From: | "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Greg Stark <gsstark(at)mit(dot)edu>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: CREATE INDEX speeds up query on 31 row table ... |
Date: | 2004-09-30 20:42:10 |
Message-ID: | 415C6FA2.6040305@zeut.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-09-30 20:43:17 | Re: Bug in Beta3 with parser? |
Previous Message | Tom Lane | 2004-09-30 20:41:46 | Re: More pgindent bizarreness |