[amazing.pauper]

Hi All,

I'm relatively inexperienced with PostgreSQL and I've been tasked with analyzing a large never vacuumed database . Is there a way to somehow supply the ANALYZE command with some parameters?
Specifically, I am trying to find an exact number of dead rows in certain tables, and ANALYZE only searches the first (or random?) 3000 pages of a table. Is there a way to increase this number?

Thanks in advance,

amazing.pauper

[loquin]

The PostgreSQL documentation (SQL Commands reference) covers that point pretty thoroughly.

First, Analyze does NOT look at the first 3000 pages. It chooses a random sample of the table contents. Because of this, the results cannot be exact.

To adjust the number of data points for the ANALYZE of a table, use the ALTER TABLE command to change the default_statistics_target_configuration variable upwards. I don't know whether this variable is the number of records to include, or the percentage of records to include. You'll have to investigate this.

If you're worried about the table being locked during the Vacuum Analyze, can't you adjust the Cost-Based Vacuum Delay parameters (ref the documentation, chapter 17, I believe) to make the vacuum process coexist in a busy database? This allows Vacuum to run, but pause every few milliseconds to allow access to the table for other processes. That way, Vacuum can run for as long as it takes to clean things up without seriously affecting the rest of the system.