PolarDB:hypopg

Prerequisites

Before you begin, ensure that you have:

• 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

• Identified the queries you want to optimize and the index types to test

SHOW polar_version;

How it works

A hypothetical index is stored in the private memory of your current connection — not in any system table or physical file. Because it does not exist on disk, it works only with EXPLAIN (without the ANALYZE option). Running EXPLAIN ANALYZE executes the query against real data, so the database ignores hypothetical indexes and uses a sequential scan instead.

Supported index types:

Install the extension

1. Install hypopg:

   CREATE EXTENSION hypopg;

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

Configure parameters

Test a hypothetical index

Because hypothetical indexes exist only in the current connection's memory, run the hypopg_create_index() call and the EXPLAIN statement in the same session.

The following example walks through the full workflow on a sample table.

1. Create a table and populate it:

   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 baseline query plan. Without any index, the planner uses a sequential scan:

   EXPLAIN SELECT val FROM hypo WHERE id = 1;

   Output:

   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 on the id column:

   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 standard CREATE INDEX statement and creates a hypothetical index for each one. The OID shown (13925 in this example) is dynamically generated.

4. Run EXPLAIN again to check whether the planner would use the hypothetical index:

   EXPLAIN SELECT val FROM hypo WHERE id = 1;

   Output:

   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 planner switched from a sequential scan to an index scan, confirming the hypothetical index would help.

5. Confirm that EXPLAIN ANALYZE ignores the hypothetical index (as expected):

   EXPLAIN ANALYZE SELECT val FROM hypo WHERE id = 1;

   Output:

   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 for real, so the hypothetical index is not used.

Manage hypothetical indexes

A quick reference of all hypopg functions and views:

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();

Remove the extension

DROP EXTENSION hypopg;