Re: [HACKERS] [Fwd: Index Advisor]

Hi Tom,

The original patch was submitted by Kai Sattler, and we (at EDB) spent a
lot of time improving it, making it as seamless and as user-friendly as
possible. As is evident from the version number of the patch (v26), it has
gone through a lot of iterations, and was available to the community for
review and discussion (and discuss they did; they asked for a few things and
those were added/improved).

<quote Bruce>
I am thinking the API needs to be simpified, perhaps by removing the system
table and having the recommendations just logged to the server logs.
</quote>

<quote Kenneth>
This means that this very useful information (in log files) would need to be
passed through an intermediary or another tool developed to allow access to
this information. I think that having this available from a table would be
very nice.
</quote>

In the initial submission, the feature was a big piece of code embedded
inside the backend. It required a system table, did not show the new plan,
actually created index physically before re planning, and could not advise
for a running application (everything had to be manually EXPLAINed).

I read through the thread titled "Index Tuning Features" that first
discussed the idea of an Index adviser for PG, and this patch also meets
quite a few requirements raised there.

Here are a few of the good things about this patch as of now:

.) Loadable plugin. Develop your own plugin to do nifty things with the plan
generated by the planner. Just as the debugger is implemented; if no
plugin... no work to do...

.) No syntax change. Run your queries as they are and get the advice in the
advise_index table (or set client_min_messages = LOG, to see the improved
plan on the screen also, if any).

.) Can recommend indexes even for the generated dynamic-queries, that are
hard to regenerate in a dry-run.

.) Can recommend indexes for SQL being executed through plpgsql (or any PL)
(again, hard to regenerate the parameterized queries by hand), and the the
advice is available in the advise_index table.

.) The adviser dumps it's advice in a table named advise_index. That can be
a user table, or a view with INSERT rule, or anything else; it should just
be an INSERTable object, accessible to the executing user (as opposed to a
system table required by the original implementation, and hence a need for
initdb).

.) No need to modify the application in any way; just set PGOPTIONS
environment variable properly before executing the appln., and run it as
usual... you have the advice generated for you.

.) No need for DBA (or the appln. writer) to feed anything to the planner in
any way; the process of recommendation is fully automated (this may change
if another plugin implimentation requires the stats in some user table).

.) Does recommend multi-column indexes. Does not make a set of each
fathomable combination of table columns to develop multi-column indexes
(hence avoiding a combinatorial explosion of time-space requirements); it
uses the columns used in the query to generate multi-column indexes.

.) The indexes are not created on disk; the index-tuple-size calculation
function does a very good job of estimating the size of the virtual index.

.) The changes to the catalog are just for the backend running under the
adviser, no one else can see those virtual indexes (as opposed to the
earlier implementation where the indexes were created on-disk, and available
to all the backends in the planning phase).

So, with one hook (no GUC variables!), we get all these cool things. I
tried very hard to eliminate that one leftover kludge, but couldn't (we have
two options here, and they are enclosed in '#if GLOBAL_CAND_LIST ... #else'
parts of the code; left upto the committers to decide which one we need!).

Another kludge that I had to add was the SPI_connect() and SPI_finish()
frame around the savepoint handling, since the RollbackToSavepoint in
xact.cassumes that only a PL/* module must be using the savepoint
infrastucture
(this was discussed on -hackers).

The interface etc. may not be beautiful, but it isn't ugly either! It is
a lot better than manually creating pg_index records and inserting them into
cache; we use index_create() API to create the index (build is deferred),
and then 'rollback to savepoint' to undo those changes when the advisor is
done. index_create() causes pg_depends entries too, so a 'RB to SP' is far
much safer than going and deleting cache records manually.

I hope you would agree that we need two passes of planner, one without
v-indexes and the other with v-indexes, for the backend to compare the
costs, and recommend indexes only if the second plan turned out to be
cheaper. If we implement the way you have suggested, then we will need one
hook at the end of get_relation_info(), one in EXPLAIN code, and yet
another, someplace after planner is finished, to do the comparison of the
two plans and recommend only those indexes that were considered to be useful
by the planner. (A total of three places to touch). And then we'll need some
extra code in the core to generate the advisory (in some form; maybe into a
user table, or as part of the EXPLAIN output; but remember, not every query
can be EXPLAINed!).

Then, we also need logic in all these places to differentiate the normal
run from the v-index enabled run, else we'll end up generating IndexOptInfo
everytime we enter get_relation_info(). And this differentiation needs to be
done in EXPLAIN code too.

Also, although the whole plan-tree is available in get_relation_info(),
but it wouldn't be the right place to scan other tables, for eg., for
generating JOIN-INDEXes or materializing some intermediate joins. (sometime
in the future we may support them!).

If we don't run the planner twice, then the developer will have to run
it manually twice, and compare the costs manually (with and without
v-indexes); virtually impossible for lage applications and introduction of
another human-error possibility.

(I just noticed that you quoted the line from the mail where I submitted
version 23 of the patch, the plugin architecture wasn't utilized; please
refer to the mail that has 'pg_post_planner_plugin-HEAD_20070116-v2.patch.gz'
and 'pg_index_adviser-HEAD_20070116-v26.patch.gz' as attachments; dated
20.Jan.07)

About the right place to call the plugin... calling it immediately after
the planner is done with normal planning phase seems to be right. At this
point planner is done and no other part of the backend yet knows about what
plan is generated; so the plugin has a chance to modify the plan in place
and do it's trickery in a completely isolated time-space. (maybe we can pass
a reference to the plan pointer, and let the plugin replace the whole plan
itself using this reference!)

I surely agree that it is time-consuming (less efficient), but it is
completely automated, with the least of human interference or application
change required; hence, on the whole, it must be a million times faster than
a human sitting down, extracting every query - prepending EXPLAIN to it -
and executing it - twice - comparing the resulting cost!!!

Best regards,
--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | yahoo }.com

17°29'34.37"N 78°30'59.76"E - Hyderabad
18°32'57.25"N 73°56'25.42"E - Pune *

On 4/6/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "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
>