I have another idea for making the hooks a bit more cleaner; I will try that and run it through you guys later today.
Please find attached the latest version of the patch. It applies cleanly on REL8_2_STABLE.
The following restrictions are applied before generating an index candidate (this is not mentioned in the README):
.) It should be a base relation; We do not generate advisory for a view or Set Returning Function or something else. .) It should not be a system table; like pg_class etc. .) It should not be a temporary table. (May be we can allow these; opinions please!!) .) We do not recommend indexes on system columns; like oid, ctid etc. .) The relation should have at least two pages. .) The relation should have at least two tuples.
The last two restrictions put the onus on the user to keep the table ANALYZEd or VACUUMed.
I have moved the calls to index_adviser() from two other places to the end of planner(). IMO, that is the best place to place a call to the Adviser; instead of duplicating code in every caller of planner(). index_adviser() makes sure that it doesn't get called recursively.
This change however costs us the loss of ability to append suggested plan to the existing plan, if being called by the EXPLAIN command. Instead, it now uses the newly written function explain_getPlanString() in explain.c to get the string representation of the plan, and then emits it as elog( LOG,...).
The only kludge left now is the code enclosed in '#if GLOBAL_CAND_LIST' in plancat.c. We need to decide whether we need the 'if' part or the 'else' part! I already see a strong objection to the 'else' option, since it is very close to the core of the optimizer! Opinions needed.
After adding the CREATE TABLE advise_index(...) script to src/test/regress/sql/create_table.sql and enabling the GUC in the conf file, 'make installcheck' runs fine, with a few acceptable diffs. Moreover, post the 'make' run, we can see there are a few advises for the tables involved in the test run. Four of them are ob serial columns of hash-index-testing tables, so they don't make much point; but the rest three of them are on big tables, and one of them is a multi-column-index suggestion.
Now that there's just one call to the Index Adviser (from planner()) we can now move forward in making it a plugin.
