hypopg is a PostgreSQL extension that supports creating hypothetical indexes in memory. These indexes exist in memory and are not written to disk, so they do not occupy physical storage space nor incur actual I/O overhead.

Its core value lies in allowing database administrators (DBAs) and developers to safely and cost-effectively evaluate the potential optimization effects of new indexes on specific queries without modifying production environment data structures or affecting online service performance. By analyzing the query plan with EXPLAIN, it is possible to determine whether the optimizer would choose to use this hypothetical index, providing a basis for deciding whether to create a real index.

Precautions

Applicable scope: hypothetical indexes are visible to the optimizer only during query plan analysis with EXPLAIN, while EXPLAIN ANALYZE and actual query execution do not use them.

Session isolation: hypothetical indexes are effective only in the current database session that created them. Other concurrent sessions cannot perceive or use them.

Lifecycle: creating hypothetical indexes does not generate WAL (write-ahead log) records, so all created hypothetical indexes are automatically lost when the database service restarts or the session ends.

Supported type: only supports creating four types of hypothetical indexes: B-tree, BRIN, HASH, and BLOOM (using BLOOM index requires the bloom extension installed).

Create and delete extension

Create extension

Execute the following SQL command to create the hypopg extension and verify the installation's success by querying the pg_extension view.

Delete extension

Execute the following command to delete the extension.

Basic usage

Function enabling and configuration

Before using the hypothetical index function, enable and configure it through the GUC parameter.

Key functions and views

The following table summarizes the core functions and views provided by the hypopg extension and their usage.

Object	Function	Usage
hypopg_hidden_indexes	Record all hidden indexes	SELECT * FROM hypopg_hidden_indexes;
hypopg_list_indexes	Record all hypothetical indexes	SELECT * FROM hypopg_list_indexes;
hypopg	Retrieve hypothetical index details	SELECT * FROM hypopg();
hypopg_create_index	Create a hypothetical index	SELECT * FROM hypopg_create_index('CREATE INDEX ON test_table (id, value)');
hypopg_drop_index	Delete hypothetical indexes	-- first column of hypopg_list_indexes records indexrelid SELECT hypopg_drop_index(indexrelid);
hypopg_get_indexdef	Retrieve definition	SELECT hypopg_get_indexdef(indexrelid);
hhypopg_hidden_indexes	Retrieve and record all hidden indexes	SELECT * FROM hypopg_hidden_indexes();
hypopg_hide_index	Hide existing indexes, including hypothetical and real indexes	SELECT hypopg_hide_index('index_name'::regclass);
hypopg_relation_size	Hypothetical index size	SELECT pg_size_pretty(hypopg_relation_size(indexrelid));
hypopg_reset	Clear all hypothetical indexes	SELECT hypopg_reset();
hypopg_reset_index	Clear all hypothetical indexes	SELECT hypopg_reset_index();
hypopg_unhide_all_indexes	Cancel hiding all indexes	SELECT hypopg_unhide_all_indexes();
hypopg_unhide_index	Cancel hiding a single index	SELECT hypopg_unhide_index(index_oid);

RDS for PostgreSQL