PolarDB:Hypopg (hypothetical indexes)

Introduction

Usage

1. Install the extension.

   1. Install the hypopg extension.

      CREATE EXTENSION hypopg;

   2. Check whether the extension has been installed.

      \dx hypopg

      Expected output:

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

      Note

      • The preceding output indicates that hypopg version 1.3.1 has been installed.

      • You can also query the pg_extension table by using an SQL statement to verify whether hypopg is installed. Example:

        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)

2. Configure parameters.

   Parameter	Description
   hypopg.enabled	Default value: 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, but existing hypothetical indexes are not deleted.
   hypopg.use_real_oids	Default value: off. Valid values: off: hypopg does not use real object identifiers (OIDs). Instead, it selects identifiers from a free range. These identifiers are reserved by the database for use in future versions. Because the free identifier range is dynamically calculated the first time hypopg is used and has the advantage of being usable on standby servers, this does not cause any issues. Note The disadvantage of the default value off 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, creating a new hypothetical index takes a very long time. In this case, you can call the hypopg_reset() function to resolve this issue. For more information, see Hypothetical index operations. on: hypopg can use real object identifiers (OIDs). hypopg.use_real_oids avoids the issue of slow creation of new hypothetical indexes when the maximum number of indexes is exceeded. hypopg requests a real identifier, which requires more lock resources and cannot be used on standby servers, but allows all identifiers to be used. For more information, see Hypothetical index operations. Note Switching this parameter does not require resetting hypothetical index identifiers. Real and non-real identifiers can coexist.

3. Uninstall the extension.

   DROP EXTENSION hypopg;

Example

1. Create a table and insert some data. No indexes exist on the table. Example:

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

   Check whether an index would benefit a simple query. Example:

   EXPLAIN SELECT val FROM hypo WHERE id = 1;

   Expected output:

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

   Note

   Because no index exists on the hypo table, the query uses a sequential scan.

2. Create a hypothetical index. Example:

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

   Expected output:

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

   Parameter description:

   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 would benefit this query.

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

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

3. Run the EXPLAIN statement to check whether the database would use the index. Example:

   EXPLAIN SELECT val FROM hypo WHERE id = 1;

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

   Note

   The database uses this type of index.

4. Run the EXPLAIN statement to check whether the database uses the hypothetical index during actual execution. Example:

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

   Upon further inspection during actual execution, the database does not use the hypothetical index.

Hypothetical index operations

The hypopg extension also provides some convenient functions and views.

• hypopg_list_indexes view: lists all created hypothetical indexes. Example:

  SELECT * FROM hypopg_list_indexes;

  Expected output:

   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. Example:

  SELECT * FROM hypopg();

  Expected output:

        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: returns the actual CREATE INDEX command based on the hypothetical index identifier. Example:

  SELECT index_name, hypopg_get_indexdef(indexrelid) FROM hypopg_list_indexes;

  Expected output:

        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. Example:

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

  Expected output:

        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. Example:

  SELECT hypopg_drop_index(13925);

  Expected output:

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

• hypopg_reset() function: deletes all hypothetical indexes. Example:

  SELECT hypopg_reset();

  Expected output:

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