Re: Index Tuning Features

Greg Stark <gsstark@mit.edu> writes:
> You would create your proposed index, then run ANALYZE and EXPLAIN to your
> heart's content. When you have it set up just so then you REINDEX your index
> and you're set.

And when you realize you don't want it after all ... you need an exclusive
lock on the table to drop it.  (Yes, you would, see relcache load.)
The advantage of keeping this idea all inside EXPLAIN is that there's
guaranteed to be no interference with anything else.

> We already have these "bogus" indexes incidentally, we just create the index
> with indisvalid=f.

Au contraire, that is something completely different.  indisvalid=f is
really the exact opposite: it's not there to the planner and it is there
to the executor.

As for the statistics business: really, we use the presence of an index
as a hint to gather certain kinds of stats about its underlying table.
If we had (ahem) statistical hints then we could gather appropriate data
with or without a real associated index.  That sort of feature would
have additional uses, ie, being able to estimate selectivities more
accurately for expressions that might not have anything to do with any
of the indexes on a table.
        regards, tom lane