Try a preview of AI-powered search below!

The HypoPG PostgreSQL extension adds support for hypothetical indexes. Use hypothetical indexes to test whether adding an index improves the performance of problematic queries, without expending resources to create them.

To enable the extension:

CREATE EXTENSION hypopg;

Create a table as follows:

CREATE TABLE up_and_down (up int primary key, down int);
INSERT INTO up_and_down SELECT a AS up, 10001-a AS down FROM generate_series(1,10000) a;

The up_and_down table has no indexes, but is defined with a primary key. As a result, when using the primary key, records are retrieved directly:

EXPLAIN SELECT * FROM up_and_down WHERE up = 999;

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Index Scan using up_and_down_pkey on up_and_down  (cost=0.00..4.11 rows=1 width=8)
   Index Cond: (up = 999)

However, because it doesn't have an index, fetching a value from the down column results in a sequential scan:

EXPLAIN SELECT * FROM up_and_down WHERE down = 999;

                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on up_and_down  (cost=0.00..102.50 rows=1000 width=8)
   Filter: (down = 999)

To see what would happen if you were to create an index for the down column without actually creating the index, use HypoPG as follows:

SELECT * FROM hypopg_create_index('create index on up_and_down(down)');

 indexrelid |          indexname
------------+-----------------------------
      13283 | <13283>lsm_up_and_down_down

Explain now shows that the planner would use the index:

EXPLAIN SELECT * FROM up_and_down WHERE down = 999;

                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Index Scan using <13283>lsm_up_and_down_down on up_and_down  (cost=0.00..4.01 rows=1000 width=8)
   Index Cond: (down = 999)

As the index is not really created, if you use EXPLAIN ANALYZE, the hypothetical index is ignored:

EXPLAIN ANALYZE SELECT * FROM up_and_down WHERE down = 999;

                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Seq Scan on up_and_down  (cost=0.00..102.50 rows=1000 width=8) (actual time=35.678..35.687 rows=1 loops=1)
   Filter: (down = 999)
   Rows Removed by Filter: 9999
 Planning Time: 0.041 ms
 Execution Time: 35.735 ms
 Peak Memory Usage: 0 kB

You can query the hypothetical indexes you created using the hypopg() function:

SELECT * FROM hypopg();

          indexname          | indexrelid | indrelid | innatts | indisunique | indkey | indcollation | indclass | indoption | indexprs | indpred | amid
-----------------------------+------------+----------+---------+-------------+--------+--------------+----------+-----------+----------+---------+------
 <13283>lsm_up_and_down_down |      13283 |    16927 |       1 | f           | 2      | 0            | 9942     |           |          |         | 9900

If you create multiple hypothetical indexes, you can drop a single hypothetical index using its indexrelid as follows:

SELECT * FROM hypopg_drop_index(13283);

 hypopg_drop_index
-------------------
 t

To remove all hypothetical indexes, log out or quit your session.

\q

For more information, refer to the HypoPG documentation.