Re: [PATCHES] [Fwd: Index Advisor]

Kenneth Marshall wrote:
> One problem with only putting this information in the system logs
> is that when we provide database services to a member of our
> community we do not actually give them an account of the DB server
> or log server. This means that this very useful information 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. My two cents.

Well, you can still run EXPLAIN manually and see the suggestions. I am
not sure even how a system table is going to work in a shared
environment for this usage. Perhaps we need to allow a table name to be
passed using the EXPLAIN, or now that I think of it, EXPLAIN output is
actually is just a single-column text table, and perhaps we would just
need to give people a way of saving that off.

The really nifty use seemed to be setting the GUC to ON and running and
application, and capturing all the suggestions. Perhaps we need to be
able to pass a single-text-column table as the GUC value and use that
for capturing the output suggestions. But again, if you are doing it
for an application and setting it for all logins, don't you probably
have access to the server logs.

Anyway, this is a new direction for us, but I think a useful one, and I
find the implementation used here creative.

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

>
> Ken
>
> On Sat, Jan 06, 2007 at 04:08:24PM -0500, Bruce Momjian wrote:
> >
> > 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, if
> > the Index Adviser is enabled, then the query is sent to the Index
> > Adviser for any suggestions it can make. The Adviser derives a set of
> > potentially useful indexes (index candidates) for this query by
> > analyzing the query predicates. These indexes are inserted into the
> > system catalog as virtual indexes; that is, they are not created on
> > disk.
> >
> > Then, the query is again sent to the planner, and this time the planner
> > makes it's decisions taking the just-created vitual indexes into account
> > too. All index candidates used in the final plan represent the
> > recommendation for the query and are inserted into the advise_index
> > table by the Adviser.
> >
> > The gain of this recommendation is estimated by comparing the execution
> > cost difference of this plan to the plan generated before virtual
> > indexes were 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[(dot)singh](at)EnterpriseDB(dot)com
> > > singh(dot)gurjeet(at){ gmail | hotmail | yahoo }.com
> >
> > [ Attachment, skipping... ]
> >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 5: don't forget to increase your free space map settings
> >
> > --
> > Bruce Momjian bruce(at)momjian(dot)us
> > EnterpriseDB http://www.enterprisedb.com
> >
> > + If your life is a hard drive, Christ can be your backup. +
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> > choose an index scan if your joining column's datatypes do not
> > match
> >

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +