Re: automated index suggestor -- request for comment

On Fri, 2002-12-13 at 03:22, johnnnnnn wrote:
> The manual is pretty sparse on advice regarding indices. Plenty of
> good feature documentation, but not much about when and where an index
> is appropriate (except a suggestion that multi-column indices should
> be avoided).
>
> Of course, the ultimate arbiter of which indices are used is the
> planner/optimizer. If i could somehow convince the optimizer to
> consider indices that don't yet exist, it could tell me which would
> give the greatest benefit should i add them.

the generated index names should be self-explaining or else we would
have to change EXPLAIN output code as well, just to tell what the actual
index definition was.

That could become the EXPLAIN SPECULATE command ?

> So, i'm writing for two reasons. First, i want to gauge interest in
> this tool. Is this something that people would find useful?

Sure it would be helpful.

> Second, i am looking to solicit some advice. Is this project even
> feasible?

As tom recently wrote on this list, no statistics is _gathered_ base on
existence of indexes, so pretending that they are there should be
limited just to planner changes plus a way to tell the planner to do it.

> If so, where would be the best place to start? My assumption
> has been that i would need to hack into the current code for
> determining index paths, and spoof it somehow, but is that possible
> without actually creating the indices?

Either with or without real indexes, it's all just code ;)

In worst case you could generate the entries in pg_class table without
building the actual index and then drop or rollback when the explain is
ready.

Of course you could just determine all possibly useful indexes and
generate then anyhow an then drop them if they were not used ;)

--
Hannu Krosing <hannu(at)tm(dot)ee>