Re: [HACKERS] [Fwd: Index Advisor]

"Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com> writes:
> Please find attached the latest version of the patch. It applies cleanly on
> REL8_2_STABLE.

The interface to the planner in this seems rather brute-force. To run
a plan involving a hypothetical index, you have to make a bunch of
catalog entries, run the planner, and then roll back the transaction
to get rid of the entries. Slow, ugly, and you still need another kluge
to keep the planner from believing the index has zero size.

It strikes me that there is a better way to do it, because 99% of the
planner does not look at the system catalog entries --- all it cares
about is the IndexOptInfo structs set up by plancat.c. So there's not
really any need to make catalog entries at all AFAICS. Rather, the
best thing would be a plugin hook at the end of get_relation_info()
that would have a chance to editorialize on the constructed IndexOptInfo
list (and maybe other properties of the RelOptInfo too). You could
remove existing index entries or insert new ones.

I'm dissatisfied with the hard-wired hook into planner() also.
That doesn't provide any extensibility nor allow the index adviser
to be implemented as a loadable plugin. I'm inclined to think it's
in the wrong place anyway; you've got thrashing around there to avoid
recursion but it's very fragile. Having to dump the results into the
postmaster log isn't a nice user API either. Perhaps what would be
better is a hook in EXPLAIN to call a plugin that can add more lines to
EXPLAIN's output, and is passed the original query and plan so that
it can re-call the planner with hypothetical indexes prepared for
insertion by the other hook.

regards, tom lane