Use the HypoPG extension to create hypothetical indexes - ApsaraDB RDS

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

Your RDS instance runs a minor engine version of 20230830 or later.
Important
This extension is supported in some minor engine versions earlier than 20230830. However, ApsaraDB RDS plans to optimize vulnerable extensions in minor engine version updates for standardized extension management and enhanced security. Therefore, you cannot create this extension for RDS instances that run a minor engine version earlier than 20230830. For more information, see [Product changes/Feature changes] Limits on extension creation for ApsaraDB RDS for PostgreSQL instances.
- If you have already created this extension for your RDS instance that runs a minor engine version earlier than 20230830, you can continue using this extension.
- If you are creating this extension for the first time or need to recreate the extension for your RDS instance, you must update the minor engine version of the RDS instance to the latest version. For more information, see Update the minor engine version.
A privileged account is used to connect to your RDS instance. You can check the type of the account that you use on the Accounts page in the ApsaraDB RDS console. If the account is a standard account, you must create a privileged account and use the privileged account to connect to your RDS instance. For more information, see Create an account.

Note

This extension is not supported by ApsaraDB RDS for PostgreSQL instances that run PostgreSQL 17.

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

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 ;

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)

Create a hypothetical index.

SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)') ;
 indexrelid |      indexname
------------+----------------------
      18284 | <18284>btree_hypo_id
(1 row)

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)

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.