Outdated This document is no longer maintained. Refer to the latest version.

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

Function Name

Parameter

Description

hypopg_create_index

Character string of the statement for creating an index

Creates a virtual index.

hypopg_display_index

None

Displays information about all created virtual indexes.

hypopg_drop_index

OID of the index

Deletes a specified virtual index.

hypopg_reset_index

None

Clears all virtual indexes.

hypopg_estimate_size

OID of the index

Estimates the space required for creating a specified index.

This function involves the following GUC parameters:

Table 2 GUC parameters of the virtual index function

Parameter

Description

Default Value

enable_hypo_index

Whether to enable the virtual index function.

off

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.
Feedback
    openGauss 2026-11-22 20:51:03
    cancel