PGCon2010 - Final Release III
PGCon 2010
The PostgreSQL Conference
| Speakers | |
|---|---|
|
Sergio Lifschitz |
| Schedule | |
|---|---|
| Day | Talks - 1 - 2010-05-20 |
| Room | DMS 1160 |
| Start time | 11:30 |
| Duration | 01:00 |
| Info | |
| 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.