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

Tom Lane wrote:

>Greg Stark <gsstark(at)mit(dot)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