Neura Market
HypoPG/hypopg logo

HypoPG/hypopg

Free

Hypothetical Indexes for PostgreSQL

FreeFree tier
Type
Open Source
Visit Website GitHub (38 stars)

About HypoPG/hypopg

HypoPG is a PostgreSQL extension that enables the creation of hypothetical (virtual) indexes. These indexes do not consume disk space or CPU resources to create, allowing developers and DBAs to test whether an index would improve query performance without actually building it. The extension is compatible with PostgreSQL 9.2 and above, and provides SQL functions such as hypopg_create_index to define hypothetical indexes using standard CREATE INDEX syntax, and hypopg_list_indexes to inspect them. Hypothetical indexes are local to the backend that creates them, so concurrent sessions remain unaffected. This makes HypoPG a valuable tool for query optimization and index planning in development and staging environments.

Key Features

Create hypothetical indexes without consuming disk or CPU resources
Use standard CREATE INDEX syntax via hypopg_create_index function
List hypothetical indexes with hypopg_list_indexes function
Per-backend isolation - concurrent sessions unaffected
Compatible with PostgreSQL 9.2 and above

Pros & Cons

Pros
  • Zero resource cost for creating virtual indexes
  • Safe to test index strategies without risk
  • Simple integration via SQL functions
  • Compatible with a wide range of PostgreSQL versions
  • Isolated per session for safe experimentation
Cons
  • Hypothetical indexes are session-local and not persistent
  • Some CREATE INDEX options (e.g., index name) are ignored
  • Requires PostgreSQL header files for installation

Best For

Test index effectiveness before creation in productionQuery performance optimization and index selectionDatabase schema planning and index design validation

FAQ

What is a hypothetical index?
A hypothetical index is a virtual index that does not physically exist. It allows you to see if PostgreSQL would use such an index in a query plan without actually creating it.
How do I create a hypothetical index?
Use the hypopg_create_index function with a regular CREATE INDEX statement as an argument, e.g., SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)');
Are hypothetical indexes visible to other sessions?
No, hypothetical indexes are contained within a single backend. Other concurrent connections running EXPLAIN will not be affected.
Does HypoPG require upgrade scripts?
HypoPG does not provide extension upgrade scripts because no data is saved in any of the objects created. To upgrade, drop and recreate the extension.