Search Postgresql Archives

Re: slow pgsql tables - need to vacuum?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"Craig Ringer" <craig@xxxxxxxxxxxxxxxxxxxxx> 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!


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux