PGCon2010 - Final Release III

PGCon 2010
The PostgreSQL Conference

Sergio Lifschitz
Day Talks - 1 - 2010-05-20
Room DMS 1160
Start time 11:30
Duration 01:00
ID 233
Event type Lecture
Track Advanced Features
Language used for presentation English

Hypothetical Indexes towards self-tuning in PostgreSQL

We propose to add hypothetical (or virtual) indexes in order to offer both what-if querying and automatic index tuning.

Hypothetical indexes are simulated index structures created solely in the database catalog. This type of index has no physical extension and, therefore, cannot be used to answer actual queries. The main benefit is to provide a means for simulating how query execution plans would change if the hypothetical indexes were actually created in the database. This feature is quite useful for database tuners and DBAs.

Index selection tools, such as Microsoft's SQL Server Index Tuning Wizard, make use of hypothetical (or virtual) indexes in the database server to evaluate candidate index configurations.

We have made some server extensions to PostgreSQL 8.* to include the notion of hypothetical indexes in the system. We have introduced three new commands: create hypothetical index, drop hypothetical index and explain hypothetical.

After implementing the server extensions for hypothetical indexes, we could use it for future automatic indexing with PostgreSQL besides simples, yet useful, what-if queries.