Re: query is taking longer time after a while

From: tomrevam <tomer(at)fabrix(dot)tv>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: query is taking longer time after a while
Date: 2009-10-04 08:44:30
Message-ID: 25736068.post@talk.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2009-10-04 11:35:54 Re: Where can I get the number of plans that considered by Planner?
Previous Message V S P 2009-10-04 02:14:53 Re: How useful is the money datatype?