Re: [PATCHES] [Fwd: Index Advisor]

On Mon, 2007-01-08 at 11:28 -0500, Bruce Momjian wrote:
> Simon Riggs wrote:
> > On Sat, 2007-01-06 at 16:08 -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
> >
> > > 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.
> >
> > The patch to the backend is in the form of a plugin API, which does
> > nothing when there is no plugin. IMHO there is a significant amount of
> > code there and it is too early to try to get all of that into the
> > backend, especially when more tested things like Tsearch2 haven't.
> > Plugins are cool because we can update them without needing to bounce a
> > production server, which means the code can evolve faster than it would
> > do if it was directly in the backend. (You do need to reconnect to allow
> > local_preload_libraries to be re-read). Tuning out the wierd
> > recommendations will take some time/effort - I don't know there are any,
> > but then my gut tells me there very likely are some.
> >
> > The output isn't a system table, its a user space table. The reason for
> > having an output table is that we can use multiple invocations of the
> > adviser to build up a set of new indexes for a complete workload.
> > Reading things back out of the log would make that more difficult, since
> > we really want this to be automated by pgAdmin et al.
>
> The complex part of this is that the feature requires patches to the
> backend, and has a /contrib component. If it could be just in /contrib,
> I agree we would just keep it there until there is a clear direction,
> but having it in both places seems difficult. I don't think we can
> maintain a patch to the backend code in /contrib, so it would have to
> ship with our backend code. That's why I was asking about getting it
> integrated fully.

The plugin approach is exactly what happened with the debugger. The
backend has an appropriate plugin API and the debugger is a plugin.

The patch to the backend shouldn't be in contrib, definitely.

I would say its up to the installer to offer the opportunity to load the
adviser plugin, or not. I like plugins because they encourage faster
paced development, diversity and choice. e.g. Multiple java language
plugins give users choice. We could include an adviser plugin with the
main distribution, as happens with PL/pgSQL...

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com