PolarDB:hypopg (hypothetical indexes)

How it works

Hypothetical indexes are stored in the private memory of your connection, not in any system tables. Because they have no physical presence on disk, they are visible only to plain EXPLAIN statements — not to EXPLAIN ANALYZE, which executes the query against real data.

This design gives you an instant answer to "would the planner use this index?" without paying the cost of building it.

Supported index types:

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for PostgreSQL cluster running one of the following minor engine versions:

  • PostgreSQL 16: 2.0.16.9.8.0 or later

  • PostgreSQL 14: 2.0.14.5.1.0 or later

  • PostgreSQL 11: 2.0.11.9.28.0 or later

• Identified the slow queries to optimize and the index types to try

To check your minor engine version, view it in the console or run SHOW polardb_version;. To upgrade, see Upgrade the minor engine version.

Install the extension

CREATE EXTENSION hypopg;

To 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';

Expected output:

extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
--------+----------+--------------+----------------+------------+-----------+--------------
 hypopg |       10 |         2200 | t              | 1.3.1      |           |
(1 row)

Test an index without building it

This example walks through the complete workflow: identify a slow query, create a hypothetical index, confirm the planner would use it, then decide whether to build the real index.

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;

2. Check the current query plan.

EXPLAIN SELECT val FROM hypo WHERE id = 1;

Without an index, the planner falls back to a sequential scan:

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

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)

The indexrelid (here 13925) is dynamically assigned. The indexname reflects the index type and columns.

hypopg_create_index() accepts any standard CREATE INDEX statement. Any other statements passed to it are ignored.

4. Verify the planner would use the index.

EXPLAIN SELECT val FROM hypo WHERE id = 1;

The query plan now shows 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)

The cost drops from 1791.00 to 8.06. The planner would use this index.

5. Confirm the index is not used during actual execution.

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 real query and ignores hypothetical indexes. This is expected behavior.

Manage hypothetical indexes

The following functions and views are available for managing 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)

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)

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)

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)

SELECT hypopg_drop_index(13925);

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

SELECT hypopg_reset();

hypopg_reset
--------------

(1 row)

Configure the extension

Uninstall the extension

DROP EXTENSION hypopg;