Hi,
sorry for opening a new thread but I have just subscribed to the
list. We have already an implementation of an index advisor for
7.4.8. This is the result of several master theses, so it's no
production ready yet, but it works (with some limitations).
The main idea is:
1. to run the planner on the query
2. create virtual indexes (only in the data dictionary) based on some
heuristics including multi-column indexes
3. run the planner again
4. extract the used virtual indexes and store them in a new table
pg_indexadvisor together with a estimation of the gain
We use this in two ways:
- There is a proof of concept tool for determining the index
recommendations for a given workload (basically it solves the
knapsack problem)
- We have a more advanced approach where collecting index
recommendations and chosing the right set is done continuously and
automatically.
There are some papers, e.g. a demo paper at VLDB'03 where we have
presented this on top of DB2 but now it is integrated in pgsql.
It definitely requires some work to port it to 8.2 and to make it
usable for production environments.
Furthermore, there are some performance bottlenecks (creating virtual
indexes, calling the planner twice) but I think they can be solved.
So, let me know if you are interested, Kai
