PolarDB:hypopg (hypothetical indexes)

Overview

How to use

1. Install the extension.

   1. Install the hypopg extension.

      CREATE EXTENSION hypopg;

   2. Check whether the extension is installed.

      \dx hypopg

      The following result is returned:

                        List of installed extensions
        Name  | Version | Schema |             Description
      --------+---------+--------+-------------------------------------
       hypopg | 1.3.1   | public | Hypothetical indexes for PostgreSQL
      (1 row)

      Note

      • The result shows that version 1.3.1 of the hypopg extension is installed.

      • You can also query the pg_extension table to verify that the hypopg extension is installed. For example:

        SELECT * FROM pg_extension WHERE extname = 'hypopg';

        The following result is returned:

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

2. Configure parameters.

   Parameter	Description
   hypopg.enabled	The default value is on. Valid values: on: Enables the hypopg extension. off: Disables the hypopg extension. Note When the hypopg extension is disabled, hypothetical indexes are not used. However, existing hypothetical indexes are not deleted.
   hypopg.use_real_oids	The default value is off. Valid values: off: hypopg does not use real object identifiers (OIDs). Instead, it selects an identifier from a free range. These identifiers are reserved by the database for future use. The free identifier range is dynamically calculated when hypopg is first used. This method has the advantage of being usable on standby servers and does not cause issues. Note The disadvantage of this setting is that you cannot have more than approximately 2,500 hypothetical indexes at the same time. If the number of existing hypothetical indexes exceeds the maximum limit, creating a new one takes a long time. To resolve this issue, call the hypopg_reset() function. For more information about how to use the function, see Hypothetical index operations. on: hypopg uses real OIDs. Setting hypopg.use_real_oids to on prevents the long creation time that occurs when the maximum number of indexes is exceeded. hypopg requests a real identifier. This requires more lock resources, cannot be used on standby servers, but allows all identifiers to be used. For more information about how to use this feature, see Hypothetical index operations. Note Switching this parameter does not require you to reset hypothetical index identifiers. Real and non-real identifiers can coexist.

3. Uninstall the extension.

   DROP EXTENSION hypopg;

Examples

1. Create a table and insert data. The table has no indexes. For example:

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

   You can check whether an index can improve a simple query. For example:

   EXPLAIN SELECT val FROM hypo WHERE id = 1;

   The following is returned:

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

   Note

   The query performs a sequential scan because the hypo table has no index.

2. Create a hypothetical index. For example:

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

   The following result is returned:

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

   The following table describes the parameters.

   Parameter	Description
   13925	The identifier of the hypothetical index.
   <13925>btree_hypo_id	The name of the generated hypothetical index.

   Note

   • A simple B-tree index on the id column is beneficial for this query.

   • The hypopg_create_index() function accepts any standard CREATE INDEX statement and creates a hypothetical index for each statement. Any other statements passed to the function are ignored.

   • The identifier is dynamically generated. In this example, it is 13925.

3. Run an EXPLAIN statement to determine whether the database uses the index. For example:

   EXPLAIN SELECT val FROM hypo WHERE id = 1;

   The following result is returned:

                                        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)

   Note

   The execution plan indicates that the database uses this index.

4. Run an EXPLAIN ANALYZE statement to determine whether the database uses the hypothetical index during actual execution. For example:

   EXPLAIN ANALYZE SELECT val FROM hypo WHERE id = 1;

   The following result is returned:

                                               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)

   Note

   During actual execution, the database does not use the hypothetical index.

Hypothetical index operations

The hypopg extension also provides some useful functions and views.

• hypopg_list_indexes view: Lists all created hypothetical indexes. For example:

  SELECT * FROM hypopg_list_indexes;

  The following result is returned:

   indexrelid |      index_name      | schema_name | table_name | am_name
  ------------+----------------------+-------------+------------+---------
        13925 | <13925>btree_hypo_id | public      | hypo       | btree
  (1 row)

• hypopg() function: Lists all created hypothetical indexes in the same format as pg_index. For example:

  SELECT * FROM hypopg();

  The following result is returned:

        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)

• hypopg_get_indexdef(oid) function: Retrieves the actual CREATE INDEX command from the hypothetical index identifier. For example:

  SELECT index_name, hypopg_get_indexdef(indexrelid) FROM hypopg_list_indexes;

  The following result is returned:

        index_name      |             hypopg_get_indexdef
  ----------------------+----------------------------------------------
   <13925>btree_hypo_id | CREATE INDEX ON public.hypo USING btree (id)
  (1 row)

• hypopg_relation_size(oid) function: Estimates the size of a hypothetical index. For example:

  SELECT index_name, pg_size_pretty(hypopg_relation_size(indexrelid))
  FROM hypopg_list_indexes;

  The following result is returned:

        index_name      | pg_size_pretty
  ----------------------+----------------
   <13925>btree_hypo_id | 2544 kB
  (1 row)

• hypopg_drop_index(oid) function: Deletes the hypothetical index with the specified identifier. For example:

  SELECT hypopg_drop_index(13925);

  The following result is returned:

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

• hypopg_reset() function: Deletes all hypothetical indexes. For example:

  SELECT hypopg_reset();

  The following result is returned:

   hypopg_reset
  --------------
  
  (1 row)