Re: Index of a table is not used (in any case)

> Of course the question "did you vacuum" (better, did you analyze) is
> annoying, just as the requirement to analyze is annoying in the first
> place, but unless someone designs a better query planner it
> will have to do. The reason why we always ask that question first is
> that people invariantly have not analyzed.

I think it is also not allways useful to ANALYZE. There are applications

that choose optimal plans with only the rudimentary statistics VACUUM
creates. And even such that use optimal plans with only the default
statistics in place.

Imho one of the biggest sources for problems is people creating new
indexes on populated tables when the rest of the db/table has badly
outdated statistics or even only default statistics in place.
In this situation the optimizer is badly misguided, because it now
sees completely inconsistent statistics to work on.
(e.g. old indexes on that table may seem way too cheap compared
to table scan)

I would thus propose a more distinguished approach of writing
the statistics gathered during "create index" to the system tables.

Something like:
if (default stats in place)
write defaults
else if (this is the only index)
write gathered statistics
else
write only normalized statistics for index
(e.g. index.reltuples = table.reltuples;
index.relpages = (index.gathered.relpages *
table.relpages / table.gathered.relpages)

Andreas