Re: VACUUM and ANALYZE Follow-Up

"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