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

Tom Lane wrote:

>Greg Stark <gsstark@mit.edu> writes:
>  
>
>>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?
>>    
>>
>More specifically, I bet there's a huge number of completely empty
>pages, which would be read by a seqscan but not an indexscan.  VACUUM
>FULL should fix it nicely, but it's odd that autovacuum isn't keeping
>a lid on the file size.  Maybe with so few live rows, it's confused into
>thinking it doesn't need to vacuum the table often?
>
I think autovacuum is keeping a lid on the file size, but the lid is too 
loose.  The default values for autovacuum were intentionally set a 
little conservative so that it wouldn't cause a net slowdown by 
vacuuming too often.  Given that, for a 31 row table, the default 
autovacuum settings say to vacuum every 1062 updates (or deletes), 
depending on the size of the tuples that could be a lot of dead space.  
Also, the default sleep time is 5 minutes, given your logs, autovacuum 
feels the need to do something to your table every time it wakes up so I 
think you are pushing the envelope.

Are you using default values for autovacuum?   Could you prove a little 
more detail by setting pg_autovacuum  debug with -d 2

Matthew