Virtual Index

The virtual index function allows users to directly perform operations in the database. This function simulates the creation of a real index to avoid the time and space overhead required for creating a real index. Based on the virtual index, users can evaluate the impact of the index on the specified query statement by using the optimizer.

This function involves the following APIs:

Table 1 Virtual index function APIs

This function involves the following GUC parameters:

Table 2 GUC parameters of the virtual index function

Procedure

1. Use the **hypopg_create_index **function to create a virtual index. For example:

   openGauss=> select * from hypopg_create_index('create index on bmsql_customer(c_w_id)');
    indexrelid |              indexname              
   ------------+-------------------------------------
        329726 | <329726>btree_bmsql_customer_c_w_id
   (1 row)
   

2. Enable the GUC parameter enable_hypo_index. This parameter controls whether the database optimizer considers the created virtual index when executing the EXPLAIN statement. By executing EXPLAIN on a specific query statement, you can evaluate whether the index can improve the execution efficiency of the query statement based on the execution plan provided by the optimizer. For example:

   openGauss=> set enable_hypo_index = on;
   SET
   

   Before enabling the GUC parameter, run **EXPLAIN **and the query statement.

   openGauss=> explain SELECT c_discount from bmsql_customer where c_w_id = 10;
                                 QUERY PLAN                              
   ----------------------------------------------------------------------
    Seq Scan on bmsql_customer  (cost=0.00..52963.06 rows=31224 width=4)
      Filter: (c_w_id = 10)
   (2 rows)
   

   After enabling the GUC parameter, run **EXPLAIN **and the query statement.

   openGauss=> explain SELECT c_discount from bmsql_customer where c_w_id = 10;
                                                       QUERY PLAN                                                    
   ------------------------------------------------------------------------------------------------------------------
    [Bypass]
    Index Scan using <329726>btree_bmsql_customer_c_w_id on bmsql_customer  (cost=0.00..39678.69 rows=31224 width=4)
      Index Cond: (c_w_id = 10)
   (3 rows)
   

   By comparing the two execution plans, you can find that the index may reduce the execution cost of the specified query statement. Then, you can consider creating a real index.

3. (Optional) Use the **hypopg_display_index **function to display all created virtual indexes. For example:

   openGauss=> select * from hypopg_display_index();
                    indexname                  | indexrelid |     table      |      column      
   --------------------------------------------+------------+----------------+------------------
    <329726>btree_bmsql_customer_c_w_id        |     329726 | bmsql_customer | (c_w_id)
    <329729>btree_bmsql_customer_c_d_id_c_w_id |     329729 | bmsql_customer | (c_d_id, c_w_id)
   (2 rows)
   

4. (Optional) Use the **hypopg_estimate_size **function to estimate the space (in bytes) required for creating a virtual index. For example:

   openGauss=> select * from hypopg_estimate_size(329730);
    hypopg_estimate_size 
   ----------------------
                15687680
   (1 row)
   

5. Delete the virtual index.

   Use the **hypopg_drop_index **function to delete the virtual index of a specified OID. For example:

   openGauss=> select * from hypopg_drop_index(329726);
    hypopg_drop_index 
   -------------------
    t
   (1 row)
   

   Use the **hypopg_reset_index **function to clear all created virtual indexes at a time. For example:

   openGauss=> select * from hypopg_reset_index();
    hypopg_reset_index 
   --------------------
   
   (1 row)
   

NOTE:

• Running EXPLAIN ANALYZE does not involve the virtual index function.
• The created virtual index is at the database instance level and can be shared by sessions. After a session is closed, the virtual index still exists. However, the virtual index will be cleared after the database is restarted.
• This function does not support common views, materialized views, and column-store tables.