Re: VACUUM and ANALYZE Follow-Up

On Mon, Nov 29, 2004 at 11:48:37AM -0800, Mark Dexter wrote:
> 1. Provide an option with ANALYZE to force it to work as if a table had
> a minimum number of rows (e.g., ANALYZE MINIMUM 1000 would analyze
> tables as if they all had at least 1000 rows).
> 2. Provide an option during table creation to state the minimum number
> of rows to use for ANALYZE.

Ok, here's a scenario, you've executed ANALYZE on an empty table. So
the system needs to get for each column statitics on largest value,
smallest value, approximate distribution, how well does the order
correlate with the table order, average column width, etc.

Hang on, it's an empty table. Is it supposed to fabricate these
statistics out of thin air? Any made up numbers will probably be worse
than none at all.

> 3. Just change ANALYZE to assume that all tables might have a reasonable
> number of rows at some point even if they are empty now. (How much
> performance is actually gained currently when ANALYZE updates the stats
> for an empty table?)

If the table is really empty, the performance is good since it will
never use any indexes.

> In any case, it is hard to see how the present behaviour can be seen as
> desirable. It obviously causes problems at least for new Postgres
> users, and we
> all hope there will be many more of these folks in the future. Thanks
> for considering this. Mark

Seems to me that the solution is as the other poster suggested, don't
run ANALYZE on an empty table if it's not going to be empty. Run
ANALYZE when the table has data in it. If you use TRUNCATE you never
need to VACUUM that table anyway.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.