Re: CREATE INDEX speeds up query on 31 row table ...

Greg Stark wrote:

>"Marc G. Fournier" <scrappy@postgresql.org> writes:
>  
>
>>Its a *very* busy table ... and running on a 7.4.0 database ...
>>    
>>
>I wonder how many tuples are really in this table. Almost all of the 190ms is
>spent in the two sequential scans of it. Which is an awful lot of time for a
>32 tuple table. 
>
>You say it's "*very* busy" is it possible there are hundreds or thousands of
>tuples in there that are uncommitted or committed after this query starts?
>
>Alternatively, try a "vacuum full". If that helps then autovacuum is having
>trouble keeping up with the rate of creation of dead tuples. You would have to
>increase the FSM parameters.
>  
>
There are scenarios where pg_autovacuum does have trouble keeping up.  
The answer is to set more aggressive autovacuum thresholds.  
Unfortunately the version of pg_autovacuum in contrib doens't support 
table specific thresholds so you would have to set these cluster wide 
which you may or may not want to do. 

Alternatively you can keep the settings as is and have cron do a daily 
vacuum full on that table.

>Presumably autovacuum has warnings when it detects that scenario?
>
>  
>
Unfortunately not.  pg_autovacuum is pretty simple.