Thread: Index Tuning Features [2]

Index Tuning Features [2]

From
Kai-Uwe Sattler
Date:
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


Re: Index Tuning Features [2]

From
Simon Riggs
Date:
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



Re: Index Tuning Features [2]

From
Simon Riggs
Date:
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



Re: Index Tuning Features [2]

From
Kai-Uwe Sattler
Date:
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