This topic describes how to create hypothetical indexes by using the HypoPG plug-in of ApsaraDB RDS for PostgreSQL. You can use hypothetical indexes to check whether indexes can help increase query performance. Hypothetical indexes are not real indexes and do not consume resources such as CPU and disk resources.

Prerequisites

Enable or disable HypoPG

Note Hypothetical indexes are valid only in the current session.
  • Execute the following statement to enable HypoPG:
    CREATE EXTENSION hypopg;
    Note Only privileged accounts are granted the permissions to execute the preceding statement.
  • Execute the following statement to disable HypoPG:
    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. Enable HypoPG and create a hypothetical index on the table.
    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.