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 interfaces:

Table 1 Virtual index function interfaces

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:

   mogdb=> 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:

   mogdb=> set enable_hypo_index = on;
   SET

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

   mogdb=> 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.

   mogdb=> 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:

   mogdb=> 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:

   mogdb=> 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:

   mogdb=> 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:

   mogdb=> 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.