Re: Index Tuning Features

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> > Anything that can be done to wheedle down your choices
> > before you have to run EXPLAIN ANALYZE is a bonus.
>
> Fair enough, but I prefer Peter's suggestion of attaching the
> hypothetical index definitions to EXPLAIN itself, rather than making
> bogus catalog entries. Something along the line of

While I do like avoiding the bogus catalog entries and attaching the
declarations to the explain plan. One advantage of that is that I can see
extending it to handling "IGNORING INDEX foo" as well which may be just as
important.

One disadvantage is that it doesn't let you gather any statistics related to
the new index to see what the plan would really be. "But indexes don't
influence statistics" I can hear already from the chorus. But the reason we
have indexes not affecting planning is precisely because we don't want to
require an analyze after creating an index before it's used. Which these bogus
entries would resolve.

If we had the ability to create bogus indexes it would kill two birds with one
stone. You could use that as the facility for noting which multi-column
combinations are interesting.

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.

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

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com