[HACKERS] Index Tuning Features [2]
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Index Tuning Features [2]
On Wed, 2006-10-11 at 14:30 +0200, Kai-Uwe Sattler wrote: sorry for opening a new thread but I have just subscribed to the list. Not at all, glad to hear about your implementation. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Index Tuning Features [2]
On Wed, 2006-10-11 at 14:30 +0200, Kai-Uwe Sattler wrote: We have already an implementation of an index advisor for 7.4.8. 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. I'm sure everybody would be glad to see the existing work submitted as a Work-in-Progress patch to pgsql-patches. We can then have a look at it and see what to do with it. Whatever happens your experience will be invaluable in taking this forward. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Index Tuning Features [2]
Hi, Am 11.10.2006 um 19:39 schrieb Simon Riggs: I'm sure everybody would be glad to see the existing work submitted as a Work-in-Progress patch to pgsql-patches. Would a patch against a clean 7.4.8 source tree useful for you? Otherwise, I had to spend some time to migrate the code to 8.2... Best, Kai ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
