For clarity, let's see how it works with a very simple test case:

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

This table doesn't have any index. Let's assume we want to check if an index would help a simple query. First, let's see how it behaves:

EXPLAIN SELECT val FROM hypo WHERE id = 1;
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on hypo  (cost=0.00..1791.00 rows=1 width=14)
   Filter: (id = 1)
(2 rows)

A plain sequential scan is used, since no index exists on the table. A simple btree index on the id column should help this query. Let's check with hypopg. The function hypopg_create_index() will accept any standard CREATE INDEX statement(s) (any other statement passed to this function will be ignored), and create a hypothetical index for each:

SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)') ;
 indexrelid |      indexname
------------+----------------------
      18284 | <18284>btree_hypo_id
(1 row)

The function returns two columns:

We can run the EXPLAIN again to see if Postgres Pro would use this index:

EXPLAIN SELECT val FROM hypo WHERE id = 1;
                                    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)

Yes, Postgres Pro would use such an index. Just to be sure, let's check that the hypothetical index won't be used to actually run the query:

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.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)

That's all you need to create hypothetical indexes and see if Postgres Pro would use such indexes.

hypopg() lists all hypothetical indexes that have been created with the same format as pg_index.

SELECT * FROM hypopg() ;
      indexname       | indexrelid | indrelid | innatts | indisunique | indkey | indcollation | indclass | indoption | indexprs | indpred | amid
----------------------+------------+----------+---------+-------------+--------+--------------+----------+-----------+----------+---------+------
 <18284>btree_hypo_id |      13543 |    18122 |       1 | f           | 1      | 0            | 1978     | <NULL>    | <NULL>   | <NULL>  |  403
(1 row)

hypopg_get_indexdef(oid) lists the CREATE INDEX statements that would recreate a stored hypothetical index.

SELECT index_name, hypopg_get_indexdef(indexrelid) FROM hypopg_list_indexes ;
      index_name       |             hypopg_get_indexdef
-----------------------+----------------------------------------------
 <18284>btree_hypo_id  | CREATE INDEX ON public.hypo USING btree (id)
(1 row)

hypopg_relation_size(oid) estimates how big a hypothetical index would be:

SELECT index_name, pg_size_pretty(hypopg_relation_size(indexrelid))
  FROM hypopg_list_indexes ;
      index_name       | pg_size_pretty
-----------------------+----------------
 <18284>btree_hypo_id  | 2544 kB
(1 row)

hypopg_drop_index(oid) removes the given hypothetical index.

hypopg_reset() removes all hypothetical indexes.

You can hide both existing and hypothetical indexes hypothetically. If you want to test it as described in the documentation, you should first use hypopg_reset() to clear the effects of any other hypothetical indexes.

As a simple case, let's consider two indexes:

SELECT hypopg_reset();
CREATE INDEX ON hypo(id);
CREATE INDEX ON hypo(id, val);

EXPLAIN SELECT * FROM hypo WHERE id = 1;
                                    QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using hypo_id_val_idx on hypo  (cost=0.29..8.30 rows=1 width=13)
Index Cond: (id = 1)
(2 rows)

The query plan is using the hypo_id_val_idx index now.

hypopg_hide_index(oid) allows you to hide an index in the EXPLAIN output by using its OID. It returns true if the index was successfully hidden, and false otherwise.

SELECT hypopg_hide_index('hypo_id_val_idx'::REGCLASS);
 hypopg_hide_index
-------------------
t
(1 row)

EXPLAIN SELECT * FROM hypo WHERE id = 1;
                            QUERY PLAN
-------------------------------------------------------------------------
Index Scan using hypo_id_idx on hypo  (cost=0.29..8.30 rows=1 width=13)
Index Cond: (id = 1)
(2 rows)

As an example, let's assume that the query plan is currently using the hypo_id_val_idx index. To continue testing, use the hypopg_hide_index(oid) function to hide another index.

SELECT hypopg_hide_index('hypo_id_idx'::REGCLASS);
 hypopg_hide_index
-------------------
t
(1 row)

EXPLAIN SELECT * FROM hypo WHERE id = 1;
                    QUERY PLAN
-------------------------------------------------------
Seq Scan on hypo  (cost=0.00..180.00 rows=1 width=13)
Filter: (id = 1)
(2 rows)

hypopg_unhide_index(oid) restores a previously hidden index in the EXPLAIN output by using its OID. It returns true if the index was successfully restored, and false otherwise.

SELECT hypopg_unhide_index('hypo_id_idx'::regclass);
 hypopg_unhide_index
-------------------
t
(1 row)

EXPLAIN SELECT * FROM hypo WHERE id = 1;
                            QUERY PLAN
-------------------------------------------------------------------------
Index Scan using hypo_id_idx on hypo  (cost=0.29..8.30 rows=1 width=13)
Index Cond: (id = 1)
(2 rows)

hypopg_unhide_all_index() restores all hidden indexes and returns void.

hypopg_hidden_indexes() returns a list of OIDs for all hidden indexes.

SELECT * FROM hypopg_hidden_indexes();
 indexid
---------
526604
(1 rows)