Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2007

    Unhappy Unanswered: ANALYZE parameters?

    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,


  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    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.
    Last edited by loquin; 11-15-07 at 13:34.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts