HypoPG

Introduction

HypoPG is an extension of RDS for PostgreSQL. It helps you understand whether a specific index can improve problematic queries. It allows you to rapidly create virtual indexes that have no resource cost (CPU, storage, or other resources).

For more information, see HypoPG.

Supported Versions

This extension is available to the latest minor versions of RDS for PostgreSQL 11 and later versions. You can run the following SQL statement to check whether your DB instance supports this extension:

SELECT * FROM pg_available_extension_versions WHERE name = 'hypopg';

If this extension is not supported, upgrade the minor version of your DB instance or upgrade the major version using dump and restore.

To see more extensions supported by RDS for PostgreSQL, go to Supported Extensions.

Features

HypoPG is a third-party open-source extension supported by RDS for PostgreSQL. The virtual indexes created by HypoPG do not exist in any system catalog but are stored in the private memory of the connection. Because virtual indexes do not actually exist in any physical file, HypoPG ensures that the virtual indexes can be used only by a simple EXPLAIN statement (excluding the ANALYZE option). Virtual indexes are not real indexes and therefore do not consume CPU, storage, or other resources.

HypoPG supports the following index types:

• BTREE
• BRIN
• HASH
• BLOOM (The bloom extension must be installed first.)

Extension Installation and Uninstallation

• Installing the extension

  SELECT control_extension ('create', 'hypopg');

• Uninstalling the extension

  SELECT control_extension ('drop', 'hypopg');

For more information, see Installing and Uninstalling an Extension on the RDS Console and Installing and Uninstalling an Extension Using SQL Commands.

How to Use

1. Install the HypoPG extension.

   SELECT control_extension ('create', 'hypopg');

2. Create a table and insert test data.

   CREATE TABLE t (id int, col text) ;
   INSERT INTO t select x as id,'col '||x from generate_series(1,100000) as x;

3. View the default execution plan.

   EXPLAIN SELECT * FROM t WHERE id = 1;
                          QUERY PLAN
   --------------------------------------------------------
    Seq Scan on t  (cost=0.00..1399.84 rows=344 width=36)
      Filter: (id = 1)
   (2 rows)

4. Create a virtual index.

   SELECT hypopg_create_index('CREATE INDEX ON t (id)') ;
    hypopg_create_index    
   ---------------------------
   (14737,<14737>btree_t_id)
   (1 row)

   Table 1 Parameter description

   Parameter	Description
   14737	Identifier of the virtual index.
   <14737>btree_t_id	Name of the virtual index.

5. Run EXPLAIN again to check that your DB instance uses the virtual index.

   EXPLAIN SELECT * FROM t WHERE id = 1;
                                       QUERY PLAN
   ----------------------------------------------------------------------------------
    Index Scan using "<14737>btree_t_id" on t  (cost=0.04..2.26 rows=1 width=13)
       Index Cond: (id = 1)
   (2 rows)

6. Virtual indexes are "virtual" and are not used when SQL statements are actually run. View the actual execution plan.

   EXPLAIN ANALYZE SELECT * FROM t WHERE id = 1;
                                               QUERY PLAN
   ---------------------------------------------------------------------------------------------------
    Seq Scan on t  (cost=0.00..1791.00 rows=1 width=13) (actual time=0.010..5.378 rows=1 loops=1)
      Filter: (id = 1)
      Rows Removed by Filter: 99999
    Planning Time: 0.036 ms
    Execution Time: 5.401 ms
   (5 rows)