Re: query is taking longer time after a while - Mailing list 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.