HomeMogDBMogDB StackUqbar
v3.0

Documentation:v3.0

Supported Versions:

Other Versions:

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)

img 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.
Copyright © 2011-2024 www.enmotech.com All rights reserved.