This topic describes how to use the HypoPG extension to create hypothetical indexes for an ApsaraDB RDS for PostgreSQL instance. You can use the created hypothetical indexes to check whether indexes can increase query performance. Hypothetical indexes are not real indexes and do not consume resources such as CPU cores and disk resources.

Prerequisites

Enable or disable the HypoPG extension.

Note Hypothetical indexes are valid only in the current session.
  • Execute the following statement to enable the HypoPG extension:
    CREATE EXTENSION hypopg;
    Note Only privileged accounts are granted the permissions to execute the preceding statement.
  • Execute the following statement to disable the HypoPG extension:
    DROP EXTENSION hypopg;
    Note Only privileged accounts are granted the permissions to execute the preceding statement.

Examples

  1. Create a table and insert test data into the table.
    create extension hypopg;
    
    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 query performance of execution plans of SQL statements when no index is created on the table.
    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)
  3. Create a hypothetical index.
    SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)') ;
     indexrelid |      indexname
    ------------+----------------------
          18284 | <18284>btree_hypo_id
    (1 row)
  4. Check whether the hypothetical index increases query performance of the execution plans.
    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)
  5. Check the execution plans of the SQL statements that are executed. The hypothetical index that you created is not used in the execution plans of the SQL statements.
    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)

References

For more information about HypoPG, see Usage of HypoPG.