Sergio Lifschitz

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.

We have discussed hypothetical indexes as a hacker talk during the brazilian PgCon2009 in Campinas, and discussions with Bruce Momjian have encouraged us to, once again (we've submitted our ideas for the very first time about five years ago), bring our ideas and coding to the international PGCon.