It seems to me to be a very valuable and interesting idea. (IBM has something similar)
Probably, for production you do not want your optimizer puzzling about possible indexes. However, it would be a very
valuabletool for the test environment. I can easily imagine a setting that allows the feature to be turned on or off
andthe suggestions could (of course) be very valuable.
Do you have a paper describing your work? I would like to read it.
> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of
> Marcos A Vaz Salles
> Sent: Wednesday, October 06, 2004 7:40 PM
> To: pgsql-hackers@postgresql.org
> Cc: Sergio Lifschitz; Maira Ferreira de Noronha; msalles@acm.org
> Subject: [HACKERS] Hypothetical Indexes
>
>
> Hello,
>
> I have just finished my M.Sc. thesis at Pontifícia
> Universidade Católica do Rio de Janeiro (PUC-Rio), Brazil,
> and we have developed a research prototype for automatic
> index selection using a software agent in PostgreSQL. In
> order to make index selection possible, we have extended the
> PostgreSQL DBMS to allow the simulation of hypothetical
> indexes. We believe these server extensions may be of value
> for addition to the PostgreSQL code base.
>
> A description of the work we have been doing is available at:
> http://www.inf.puc-rio.br/~postgresql/
There you will find a link to a tutorial based description of the hypothetical indexes feature we have implemented on
PostgreSQL7.4 beta 3.
I am aware that we shouldn't go on working on things that are not on the TODO list, but we had a research oriented
focus.At the end of the day, it turned out that some of the things we coded might be useful to the PostgreSQL community
andwe would be happy if we can contribute.
So, what do you people think? Should we work together to make this new feature available on newer PostgreSQL versions?
Thanks in advance for your attention,
Marcos Salles.
P.S: An interesting consequence of having hypothetical indexes in the system is that this eases the implementation of
indextuning tools (e.g. the Microsoft SQL Server's Index Tuning Wizard). We are planning to implement one such tool to
thePostgreSQL database (at first as a command-line utility).
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match