Re: query is taking longer time after a while
От | tomrevam |
---|---|
Тема | Re: query is taking longer time after a while |
Дата | |
Msg-id | 25736068.post@talk.nabble.com обсуждение исходный текст |
Ответ на | Re: query is taking longer time after a while (Andy Colson <andy@squeakycode.net>) |
Ответы |
Re: query is taking longer time after a while
(Sam Mason <sam@samason.me.uk>)
Re: query is taking longer time after a while (Sam Mason <sam@samason.me.uk>) |
Список | pgsql-general |
Andy Colson-2 wrote: > > Can you post an explain analyze'es'es for (1) when its quick and (2) > when its slow? > Here are results: 1. Upon startup: QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on session_allocation_info (cost=99.42..11369.76 rows=3110 width=21) (actual time=1.107..2.144 rows=677 loops=1) Recheck Cond: (((status)::text = 'active'::text) OR ((status)::text = 'setup'::text)) Filter: ((initiator)::text = 'ISA'::text) -> BitmapOr (cost=99.42..99.42 rows=3111 width=0) (actual time=0.426..0.426 rows=0 loops=1) -> Bitmap Index Scan on session_allocation_info_status_idx (cost=0.00..48.93 rows=1555 width=0) (actual time=0.244..0.244 rows=1557 loops=1) Index Cond: ((status)::text = 'active'::text) -> Bitmap Index Scan on session_allocation_info_status_idx (cost=0.00..48.93 rows=1555 width=0) (actual time=0.181..0.181 rows=1609 loops=1) Index Cond: ((status)::text = 'setup'::text) Total runtime: 2.193 ms (9 rows) Time: 2.602 ms 2. After a few hours: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Bitmap Heap Scan on session_allocation_info (cost=285.11..31149.80 rows=9317 width=21) (actual time=160.329..161.025 rows=677 loops=1) Recheck Cond: (((status)::text = 'active'::text) OR ((status)::text = 'setup'::text)) Filter: ((initiator)::text = 'ISA'::text) -> BitmapOr (cost=285.11..285.11 rows=9322 width=0) (actual time=47.171..47.171 rows=0 loops=1) -> Bitmap Index Scan on session_allocation_info_status_idx (cost=0.00..140.23 rows=4661 width=0) (actual time=42.066..42.066 rows=28168 loops=1) Index Cond: ((status)::text = 'active'::text) -> Bitmap Index Scan on session_allocation_info_status_idx (cost=0.00..140.23 rows=4661 width=0) (actual time=5.103..5.103 rows=20945 loops=1) Index Cond: ((status)::text = 'setup'::text) Total runtime: 161.079 ms (9 rows) Time: 162.009 ms 3. After a few days: QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on session_allocation_info (cost=10.55..14.57 rows=1 width=21) (actual time=4817.076..4819.918 rows=677 loops=1) Recheck Cond: (((status)::text = 'active'::text) OR ((status)::text = 'setup'::text)) Filter: ((initiator)::text = 'ISA'::text) -> BitmapOr (cost=10.55..10.55 rows=1 width=0) (actual time=2426.423..2426.423 rows=0 loops=1) -> Bitmap Index Scan on session_allocation_info_status_idx (cost=0.00..5.28 rows=1 width=0) (actual time=1619.652..1619.652 rows=51025 loops=1) Index Cond: ((status)::text = 'active'::text) -> Bitmap Index Scan on session_allocation_info_status_idx (cost=0.00..5.28 rows=1 width=0) (actual time=806.770..806.770 rows=46601 loops=1) Index Cond: ((status)::text = 'setup'::text) Total runtime: 4819.990 ms -- View this message in context: http://www.nabble.com/query-is-taking-longer-time-after-a-while-tp25661219p25736068.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
В списке pgsql-general по дате отправления:
Следующее
От: Martijn van OosterhoutДата:
Сообщение: Re: Where can I get the number of plans that considered by Planner?