Skip site navigation (1) Skip section navigation (2)

Re: VACUUM and ANALYZE Follow-Up

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Mark Dexter" <MDEXTER(at)dexterchaney(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: VACUUM and ANALYZE Follow-Up
Date: 2004-11-30 20:42:36
Message-ID: 24183.1101847356@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
"Mark Dexter" <MDEXTER(at)dexterchaney(dot)com> writes:
> 1. Would it be difficult to add an option to ANALYZE to force it to
> pretend that there are a minimum number of rows (e.g., ANALYZE MINIMUM
> 1000 or something)?  This would appear to be a simple-minded way to
> solve the problem without any concerns about backward compatibility.

This strikes me as useless, not to mention not backward-compatible at all.
Where is ANALYZE supposed to come up with the data to fill pg_statistic?
Shall we add the same kluge option to VACUUM and CREATE INDEX?

> 2. Why does a newly CREATE'd table behave differently than an empty
> table after ANALYZE?  Does it make sense that it should?

This is a long-standing hack, which I am proposing undoing; see
http://archives.postgresql.org/pgsql-patches/2004-11/msg00339.php
and in particular read the comments that the patch deletes.

> 3. Has anyone ever tested whether there is a measurable performance
> gained after doing ANALYZE on empty or nearly empty tables?

As long as the table *stays* empty or nearly so, the knowledge that it
is small is good for the planner to have.  The problem we are dealing
with here boils down to the fact that a table can grow substantially
without the planner being aware of the fact.  So IMHO the correct
solution is to attack that problem head-on, not to invent weird options
to make ANALYZE lie about what it found.  CREATE TABLE shouldn't be
lying either, but at one time that seemed like a good quick-hack
workaround ...

			regards, tom lane

In response to

pgsql-general by date

Next:From: Tom LaneDate: 2004-11-30 20:52:17
Subject: Re: Dropping sequences
Previous:From: Marc G. FournierDate: 2004-11-30 20:41:10
Subject: Ignore this ...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group