Re: [PATCHES] [Fwd: Index Advisor]

I have looked over this patch, and it completes part of this TODO item:
       o Add SET PERFORMANCE_TIPS option to suggest INDEX, VACUUM, VACUUM         ANALYZE, and CLUSTER

Here is the foundation of it:
For an incoming EXPLAIN command, the planner generates the plan and, ifthe Index Adviser is enabled, then the query is
sentto the IndexAdviser for any suggestions it can make. The Adviser derives a set ofpotentially useful indexes (index
candidates)for this query byanalyzing the query predicates. These indexes are inserted into thesystem catalog as
virtualindexes; that is, they are not created ondisk.Then, the query is again sent to the planner, and this time the
plannermakesit's decisions taking the just-created vitual indexes into accounttoo. All index candidates used in the
finalplan represent therecommendation for the query and are inserted into the advise_indextable by the Adviser.The gain
ofthis recommendation is estimated by comparing the executioncost difference of this plan to the plan generated before
virtualindexeswere created.
 

It involves a patch to the backend, and a /contrib module to access it.

I think we have to decide if we want this, and whether it should be in
/contrib or fully integrated into the backend.  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.

---------------------------------------------------------------------------

Gurjeet Singh wrote:
> Hi All,
> 
>    Please find attached the latest version of the patch attached. It
> is based on REL8_2_STABLE.
> 
>    It includes a few bug fixes and an improvement to the size
> estimation function. It also includes a work-around to circumvent the
> problem we were facing earlier in xact.c; it now fakes itself to be a
> PL/xxx module by surrounding the BIST()/RARCST() calls inside an
> SPI_connect()/SPI_finish() block.
> 
>    Please note that the sample_*.txt files in the contrib module,
> which show a few different sample runs, may be a little out of date.
> 
> Best regards,
> 
> 
> -- 
> gurjeet[.singh]@EnterpriseDB.com
> singh.gurjeet@{ gmail | hotmail | yahoo }.com

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--  Bruce Momjian   bruce@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +