Use HypoPG to create hypothetical indexes for query performance testing - ApsaraDB RDS

HypoPG lets you test whether an index would improve query performance—without actually building it. Because hypothetical indexes have no resource cost (no CPU or disk usage), you can quickly evaluate dozens of index candidates on a slow query and then only create the ones that help.

Prerequisites

Before you begin, ensure that you have:

An ApsaraDB RDS for PostgreSQL instance running minor engine version 20230830 or later. For PostgreSQL 17, the minor engine version must be 20241030 or later.
A privileged account to connect to the instance. Check the account type on the Accounts page in the ApsaraDB RDS console. If you only have a standard account, create a privileged account first.

Important

This extension is supported in some minor engine versions earlier than 20230830. However, ApsaraDB RDS plans to standardize extension management and improve security in future minor engine version updates. As a result, you cannot create the HypoPG extension on instances running a minor engine version earlier than 20230830. If you already created the extension on an older version, you can continue using it. To create or recreate the extension, first update the minor engine version to the latest version. For more information, see [Product changes] Limits on extension creation for ApsaraDB RDS for PostgreSQL instances.

How it works

Hypothetical indexes are visible to the PostgreSQL query planner but are never used when a query actually runs. This means:

EXPLAIN (without ANALYZE) shows whether the planner would choose the index and estimates the performance gain.
EXPLAIN ANALYZE executes the query and ignores the hypothetical index, showing actual performance without it.

Hypothetical indexes exist only for the duration of your current session. They are automatically discarded when the session ends.

Enable the HypoPG extension

Run the following statement as a privileged account:

CREATE EXTENSION hypopg;

To remove the extension:

DROP EXTENSION hypopg;

Evaluate an index with a hypothetical index

The following example walks through the full workflow: create a hypothetical index, measure the estimated improvement, and then decide whether to create the real index.

Step 1. Create a test table and load sample data.

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 execution plan without any index. The planner performs a sequential scan.

EXPLAIN SELECT val FROM hypo WHERE id = 1;

Expected output:

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

Step 3. Create a hypothetical index on the id column.

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

Expected output:

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

Step 4. Run EXPLAIN again. The planner now chooses an index scan, and the estimated cost drops significantly.

EXPLAIN SELECT val FROM hypo WHERE id = 1;

Expected output:

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

Step 5. Confirm that EXPLAIN ANALYZE (which runs the query) ignores the hypothetical index.

EXPLAIN ANALYZE SELECT val FROM hypo WHERE id = 1;

Expected output:

                                                    QUERY PLAN
---------------------------------------------------------------------------------------------------
 Seq Scan on hypo  (cost=0.00..1791.00 rows=1 width=10) (actual time=0.046..46.390 rows=1 loops=1)
   Filter: (id = 1)
   Rows Removed by Filter: 99999
 Planning time: 0.160 ms
 Execution time: 46.460 ms
(5 rows)

The execution plan reverts to a sequential scan because the hypothetical index does not exist as a real index.

Step 6. If the hypothetical index shows a clear improvement, create the real index.

CREATE INDEX ON hypo (id);

What's next

Usage of HypoPG — full reference for all HypoPG functions and options.