Re: query is taking longer time after a while

Sam Mason <sam@samason.me.uk> writes:
> On Sun, Oct 04, 2009 at 01:44:30AM -0700, tomrevam wrote:
>> ->  Bitmap Index Scan on session_allocation_info_status_idx (cost=0.00..5.28 rows=1 width=0) (actual
time=1619.652..1619.652rows=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.770rows=46601 loops=1) 
>> Index Cond: ((status)::text = 'setup'::text)
>> Total runtime: 4819.990 ms

> Wow, that's quite a change in run time!  Are you sure planner stats are
> being kept up to date?

It's not the planner's fault.  Note that the parent BitmapHeapScan is
still returning the same number of rows.  This means that the increase
in number of matching index entries represents all dead rows.  IOW,
what we have here is table bloat caused by inadequate vacuuming.
I missed the start of the thread, but the questions to be asking
are about whether autovacuum is on, what its parameters are if so,
and what the manual vacuuming policy is if not.

            regards, tom lane