hypopg (hypothetical indexes)

Prerequisites

Before you begin, ensure that you have:

• Identified the slow queries you want to optimize

• A target index type in mind (B-tree, BRIN, hash, or bloom)

• A PolarDB for PostgreSQL (Compatible with Oracle) cluster running one of the following engine versions:

  • PolarDB for PostgreSQL (Compatible with Oracle) 2.0, revision version 2.0.14.1.0 or later

  • PolarDB for PostgreSQL (Compatible with Oracle) 1.0, revision version 1.1.28 or later

To check your revision version, run:

SHOW polar_version;

How it works

Hypothetical indexes are stored only in the private memory of the session that created them. They don't exist in any system catalog, don't create physical files, and are invisible to other connections.

Because they don't physically exist, hypothetical indexes work only with EXPLAIN (without the ANALYZE option). Running EXPLAIN ANALYZE executes the query against real data, so the planner falls back to a sequential scan—the hypothetical index is ignored.

Supported index types:

Install the extension

CREATE EXTENSION hypopg;

Verify the installation:

\dx hypopg

Expected output:

         List of installed extensions
  Name  | Version | Schema |             Description
--------+---------+--------+-------------------------------------
 hypopg | 1.3.1   | public | Hypothetical indexes for PostgreSQL
(1 row)

Alternatively, query the pg_extension catalog:

SELECT * FROM pg_extension WHERE extname = 'hypopg';

Test an index hypothesis

The core workflow: create a table, check the baseline query plan, add a hypothetical index, and verify whether the planner switches to an index scan.

Step 1: Set up a test table

CREATE TABLE hypo (id integer, val text);
INSERT INTO hypo SELECT i, 'line ' || i FROM generate_series(1, 100000) i;
VACUUM ANALYZE hypo;

Step 2: Check the baseline query plan

EXPLAIN SELECT val FROM hypo WHERE id = 1;

Without an index on id, the planner uses a sequential scan:

                        QUERY PLAN
--------------------------------------------------------
 Seq Scan on hypo  (cost=0.00..1791.00 rows=1 width=10)
   Filter: (id = 1)
(2 rows)

Step 3: Create a hypothetical index

SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)');

Output:

 indexrelid |      indexname
------------+----------------------
      13925 | <13925>btree_hypo_id
(1 row)

hypopg_create_index() accepts any valid CREATE INDEX statement and creates a hypothetical index for it. The object identifier (OID) 13925 is dynamically assigned and varies between sessions.

Step 4: Verify the planner uses the hypothetical index

EXPLAIN SELECT val FROM hypo WHERE id = 1;

The planner now chooses an index scan:

                                    QUERY PLAN
------------------------------------------------------------------------------------
 Index Scan using "<13925>btree_hypo_id" on hypo  (cost=0.04..8.06 rows=1 width=10)
   Index Cond: (id = 1)
(2 rows)

Step 5: Confirm the index is not used at execution time

EXPLAIN ANALYZE SELECT val FROM hypo WHERE id = 1;

                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------
 Seq Scan on hypo  (cost=0.00..1791.00 rows=1 width=10) (actual time=0.030..15.439 rows=1 loops=1)
   Filter: (id = 1)
   Rows Removed by Filter: 99999
 Planning Time: 0.066 ms
 Execution Time: 15.492 ms
(5 rows)

EXPLAIN ANALYZE executes the query, bypassing hypothetical indexes. This confirms the index only influences the planner's cost estimates—not actual execution.

Step 6: Create the real index

If EXPLAIN confirms the hypothetical index improves the query plan, create the actual index:

CREATE INDEX ON hypo (id);

Configure extension behavior

`hypopg.use_real_oids` details:

When set to off (default), OIDs are drawn from an idle range reserved for future database releases. This mode is compatible with secondary servers but limits the total number of hypothetical indexes to approximately 2,500. Once this limit is reached, creating new hypothetical indexes becomes very slow. Call hypopg_reset() to clear all hypothetical indexes and recover the OID space.

When set to on, the extension requests real OIDs from the database. All OIDs are available, so the 2,500-index limit does not apply. However, this mode requires more lock resources and cannot be used on a secondary server. Real OIDs and non-real OIDs can coexist—changing this parameter does not reset existing hypothetical index OIDs.

Manage hypothetical indexes

All management operations are session-scoped. Hypothetical indexes created in one connection are not visible in other connections.

List all hypothetical indexes

SELECT * FROM hypopg_list_indexes;

 indexrelid |      index_name      | schema_name | table_name | am_name
------------+----------------------+-------------+------------+---------
      13925 | <13925>btree_hypo_id | public      | hypo       | btree
(1 row)

List hypothetical indexes in `pg_index` format

SELECT * FROM hypopg();

      indexname       | indexrelid | indrelid | innatts | indisunique | indkey | indcollation | indclass | indoption | indexprs | indpred | amid
----------------------+------------+----------+---------+-------------+--------+--------------+----------+-----------+----------+---------+------
 <13925>btree_hypo_id |      13925 |    16450 |       1 | f           | 1      | 0            | 1978     |           |          |         |  403
(1 row)

Get the `CREATE INDEX` statement for a hypothetical index

SELECT index_name, hypopg_get_indexdef(indexrelid)
FROM hypopg_list_indexes;

      index_name      |             hypopg_get_indexdef
----------------------+----------------------------------------------
 <13925>btree_hypo_id | CREATE INDEX ON public.hypo USING btree (id)
(1 row)

Estimate the size of a hypothetical index

SELECT index_name, pg_size_pretty(hypopg_relation_size(indexrelid))
FROM hypopg_list_indexes;

      index_name      | pg_size_pretty
----------------------+----------------
 <13925>btree_hypo_id | 2544 kB
(1 row)

Drop a specific hypothetical index by OID

SELECT hypopg_drop_index(13925);

 hypopg_drop_index
-------------------
 t
(1 row)

Drop all hypothetical indexes

SELECT hypopg_reset();

Remove the extension

DROP EXTENSION hypopg;