Re: automated index suggestor -- request for comment

On Fri, Dec 13, 2002 at 09:49:53AM -0500, Tom Lane wrote:
> Hannu Krosing <hannu@tm.ee> writes:
> > That could become the EXPLAIN SPECULATE command ?
>
> [ snicker... ]  Seriously, it wouldn't be hard to inject a slew of
> phony index definitions into the planner to see what it comes up
> with.  You just have to cons up an IndexOptInfo record, the planner
> will be none the wiser.

That's good news. The easier it is, the more likely i am to actually
get it working and available to people.

> The tricky part is deciding which indexes are even worth expending
> planner cycles on.  ("Make 'em all" doesn't seem very practical when
> you consider multi-column or functional indexes.)

Agreed. But for a first development iteration, "Make 'em all" could
certainly include the combinatorial explosion of all single- and
multi-column indices. It might be slow as a dog, but it would exist.

> The big boys approach this sort of problem with "workload analysis"
> tools, which start from a whole collection of sample queries not
> just one.  I don't think EXPLAIN applied to individual queries can
> hope to produce similarly useful results.

Again, agreed. My intent was to start with something simple which
could only deal with one query at a time, and then build a more robust
tool from that point.

That said, i wasn't planning on grafting onto the EXPLAIN syntax, but
rather creating a new SUGGEST command, which could take a query or
eventually a workload file. The other option was to decouple it from
pg proper and have an independent application to live in contrib/ or
gborg.

-johnnnnnnnnnnn