index advisor

Hi,
here is a patch implementing an index recommendation advisor for
7.4.13. It consists of extensions to the planner and the catalog as
well as a command line tool pg_advise in contrib. The index advisor
works as part of the EXPLAIN statement, i.e. by
set enable_indexadvisor=true;
explain select ...
you will find the recommended indexes in pg_indexadvisor afterwards.

Comments are welcome.
Best,
Kai

Kai-Uwe Sattler wrote:
> Hi,
> here is a patch implementing an index recommendation advisor for
> 7.4.13. It consists of extensions to the planner and the catalog as
> well as a command line tool pg_advise in contrib. The index advisor
> works as part of the EXPLAIN statement, i.e. by
> set enable_indexadvisor=true;
> explain select ...
> you will find the recommended indexes in pg_indexadvisor afterwards.
>

You know we don't put new features on stable branches, right? If you
want to submit a new feature it has to be against CVS HEAD (and we are
currently in feature freeze there too)

cheers

andrew

Moving to -hackers...

On Fri, Oct 13, 2006 at 03:07:50PM -0400, Andrew Dunstan wrote:
> Kai-Uwe Sattler wrote:
> >Hi,
> >here is a patch implementing an index recommendation advisor for
> >7.4.13. It consists of extensions to the planner and the catalog as
> >well as a command line tool pg_advise in contrib. The index advisor
> >works as part of the EXPLAIN statement, i.e. by
> >set enable_indexadvisor=true;
> >explain select ...
> >you will find the recommended indexes in pg_indexadvisor afterwards.
> >
>
>
> You know we don't put new features on stable branches, right? If you
> want to submit a new feature it has to be against CVS HEAD (and we are
> currently in feature freeze there too)

This patch was submitted so people (ie: Simon) could see what had been
done for index recommendations.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Kai-Uwe Sattler wrote:
>> here is a patch implementing an index recommendation advisor for
>> 7.4.13.

> You know we don't put new features on stable branches, right?

I think you forgot the context --- somebody asked Kai-Uwe to send what
he had. There was no intention that this would get applied as-is, and
no expectation that Kai-Uwe would do the legwork to update it to HEAD
before we decide if we're interested or not.

regards, tom lane

Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> Kai-Uwe Sattler wrote:
>>> here is a patch implementing an index recommendation advisor for
>>> 7.4.13.
>
>> You know we don't put new features on stable branches, right?
>
> I think you forgot the context --- somebody asked Kai-Uwe to send what
> he had. There was no intention that this would get applied as-is, and
> no expectation that Kai-Uwe would do the legwork to update it to HEAD
> before we decide if we're interested or not.
>

OK, my apologies.

cheers

andrew

On Fri, 2006-10-13 at 20:49 +0200, Kai-Uwe Sattler wrote:

> here is a patch implementing an index recommendation advisor for
> 7.4.13. It consists of extensions to the planner and the catalog as
> well as a command line tool pg_advise in contrib. The index advisor
> works as part of the EXPLAIN statement, i.e. by
> set enable_indexadvisor=true;
> explain select ...
> you will find the recommended indexes in pg_indexadvisor afterwards.
>
> Comments are welcome.

Thanks for submitting.

OK, I see a 12,000 line patch, developed by 3 authors using the concepts
of virtual indexes and recommendation. The patch is freshly cut, though
is against the 7.4 branch. Nothing too bad to port on a first scan.

The comments are very sparse, since I understand much of the earlier
documentation is in German. I don't read that language myself, but I'm
sure many readers do. Could you can provide what you have, or any
further docs that would help the community?

Do you have references to the Master's theses that this work derives
from? Are there any restrictive covenants on this work, licencing
restrictions, intellectual property rights etc? Does this work utilise
any known patent? We'll need to get all of that in place also before I
start looking through the code in any detail.

After that, I'll look at this to develop an architectural review myself,
not yet looking at the code/quality. We can discuss porting to 8.2 when
everybody's happy to proceed.

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