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,

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to