Re: automated index suggestor -- request for comment

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.  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.)

Also, I don't see any reasonable way to automatically suggest partial
indexes; certainly not on the basis of individual queries.

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.

            regards, tom lane