Re: slow pgsql tables - need to vacuum?

"Craig Ringer" <craig(at)postnewspapers(dot)com(dot)au> writes:

> Douglas McNaught wrote:
>
>> You need to ANALYZE the tables after you load them, and make sure you
>> have indexes on the column you're querying (which it sounds like you
>> do, but they're not being used because the statistics for the table
>> are inaccurate).
>
> I've seen discussion here that made it sound like a REINDEX might also
> be required on tables with really high data churn (ie when VACUUM /
> VACUUM FULL are run a lot) - if you're not dropping and re-creating the
> indexes for better bulk load performance anyway, of course. Am I just
> confused, or can this sometimes be necessary?

If VACUUM FULL is run a lot, definitely. VACUUM FULL actually makes your
indexes *worse* not better.

If VACUUM is being run regularly enough that VACUUM FULL isn't necessary then
probably not. But there are usage patterns where it can still be necessary. If
you load lots of sequential records then delete all but one for a time period,
for example. That ends up leaving a page holding just one record which VACUUM
can't clean up.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!