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
- The minor engine version of the RDS instance is 20230330 or later. Important This extension is supported in minor engine versions that are earlier than 20230330. However, the extensions that are supported for ApsaraDB RDS for PostgreSQL instances are changed. Starting April 17, 2023, some extensions can no longer be created for RDS instances that run minor engine versions earlier than 20230330. For more information, see [Notice] Starting April 17, 2023, some extensions can no longer be created for ApsaraDB RDS for PostgreSQL instances that run earlier minor engine versions.
- If you have created this extension for your RDS instance that runs a minor engine version earlier than 20230330, the extension is not affected.
- If this is the first time you create this extension for your RDS instance or re-create the extension, you must update the minor engine version of the RDS instance to 20230330 or later. For more information, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.
- 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 on an ApsaraDB RDS for PostgreSQL instance.
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.