PolarDB:hypopg

Overview

Usage

1. Install the hypopg extension.

   1. Install the hypopg extension.

      CREATE EXTENSION hypopg;

   2. Check whether the hypopg extension is installed.

      \dx hypopg

      Sample result:

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

      Note

      • The preceding result indicates that hypopg 1.3.1 is installed.

      • You can also execute the SQL statement to query the pg_extension table to check whether the hypopg extension is installed. Example:

        SELECT * FROM pg_extension WHERE extname = 'hypopg';

        Sample result:

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

2. Configure the parameters.

   Parameter	Description
   hypopg.enabled	Specifies whether to enable the hypopg extension. Default value: on. Valid values: on off Note When the hypopg extension is disabled, hypothetical indexes are not used, but existing hypothetical indexes are not deleted.
   hypopg.use_real_oids	Specifies whether to use real object identifiers (OIDs). Default value: off. Valid values: off: does not use real OIDs. Instead, IDs are selected from the idle ID range. Such IDs are reserved by the database for use in future releases. This does not create any problems because the idle ID range is dynamically calculated when the hypopg extension is first used, and can be used on a secondary server. Note However, when this parameter is set to off, about 2500 hypothetical indexes can exist in total. When the maximum number is reached, it will take a very long time to create a new hypothetical index. In this case, you must call the hypopg_reset() function to solve this problem. For more information, see Hypothetical index operations. on: uses real OIDs. The hypopg.use_real_oids parameter is used to prevent the problem where a very long time is taken to create a new hypothetical index when the maximum number of hypothetical indexes is reached. hypopg requests real OIDs. This requires more lock resources and real OIDs cannot be used on a secondary server. However, all OIDs can be used. For more information, see Hypothetical index operations. Note Modifying this parameter state does not reset the OIDs of hypothetical indexes. Real OIDs and the non-real OIDs can coexist.

3. Delete the hypopg extension.

   DROP EXTENSION hypopg;

Examples

1. Create a table and insert some data. The table does not contain indexes. 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 the query is accelerated. Example:

   EXPLAIN SELECT val FROM hypo WHERE id = 1;

   Sample result:

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

   Note

   Sequential scan is used in simple queries because the hypo table does not contain indexes.

2. Create a hypothetical index on the table. Example:

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

   Sample result:

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

   The following table describes the parameters.

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

   Note

   • A simple B-tree index in the id column accelerates this query.

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

   • The OID is dynamically generated. It is 13925 in this example.

3. Execute the EXPLAIN statement to check whether the database uses the index. Example:

   EXPLAIN SELECT val FROM hypo WHERE id = 1;

   Sample result:

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

4. Execute the EXPLAIN statement to check whether the database uses the hypothetical index when statements are executed. Example:

   EXPLAIN ANALYZE SELECT val FROM hypo WHERE id = 1;

   Sample result:

                                               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

   Check whether the database uses the hypothetical index when statements are executed.

Hypothetical index operations

The hypopg extension also provides convenient features and views.

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

  SELECT * FROM hypopg_list_indexes ;

  Sample result:

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

• hypopg() function: lists all existing hypothetical indexes in the same format as pg_index. Example:

  SELECT * FROM hypopg() ;

  Sample result:

        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: obtains the actual CREATE INDEX statements by using the OIDs of hypothetical indexes. Example:

  SELECT index_name, hypopg_get_indexdef(indexrelid) FROM hypopg_list_indexes ;

  Sample result:

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

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

  Sample result:

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

  SELECT hypopg_drop_index(13925);

  Sample result:

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

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

  SELECT hypopg_reset();

  Sample result:

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