Index_advisor: Index Recommendation ​

This section describes the index recommendation functions, including single-query index recommendation, virtual index recommendation, and workload_level index recommendation.

Single-query Index Recommendation ​

The single-query index recommendation function allows users to directly perform operations in the database. This function generates recommended indexes for a single query statement entered by users based on the semantic information of the query statement and the statistics of the database. This function involves the following interfaces:

Table 1 Single-query index recommendation APIs

Application Scenarios ​

Use the preceding function to obtain the recommendation index generated for the query. The recommendation result consists of the table name and column name of the index.

For example:

openGauss=> select "table", "column" from gs_index_advise('SELECT c_discount from bmsql_customer where c_w_id = 10');
     table      |  column  
----------------+----------
 bmsql_customer | c_w_id
(1 row)

The preceding information indicates that an index should be created on the c_w_id column of the bmsql_customer table. You can run the following SQL statement to create an index:

CREATE INDEX idx on bmsql_customer(c_w_id);

Some SQL statements may also be recommended to create a join index, for example:

openGauss=# select "table", "column" from gs_index_advise('select name, age, sex from t1 where age >= 18 and age < 35 and sex = ''f'';');
 table | column
-------+------------
 t1    | age, sex
(1 row)

The preceding statement indicates that a join index (age, sex) needs to be created in the t1table. You can run the following command to create a join index:

CREATE INDEX idx1 on t1(age, sex);

You can recommend specific index types for partitioned tables. For example:

openGauss=# select "table", "column", "indextype" from gs_index_advise('select name, age, sex from range_table where age = 20;');
 table | column | indextype
-------+--------+-----------
 t1    | age    | global
(1 row)

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_indexfunction 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 EXPLAINand 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 EXPLAINand 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_indexfunction 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_sizefunction 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_indexfunction 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_indexfunction to clear all created virtual indexes at a time. For example:

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

Workload-level Index Recommendation ​

For workload-level indexes, you can run scripts outside the database to use this function. This function uses the workload of multiple DML statements as the input to generate a batch of indexes that can optimize the overall workload execution performance. In addition, it provides the function of extracting service data SQL statements from logs.

Prerequisites ​

• The database is normal, and the client can be connected properly.
• The gsql tool has been installed by the current user, and the tool path has been added to the _PATH_environment variable.
• To use the service data extraction function, you need to set the GUC parameters of the node whose data is to be collected as follows:

  • log_min_duration_statement = 0

  • log_statement= 'all'

    NOTE

    After service data extraction is complete, you are advised to restore the preceding GUC parameters. Otherwise, log files may be expanded.

Procedure for Using the Service Data Extraction Script ​

1. Set the GUC parameters according to instructions in the prerequisites.

2. Run the following command to extract SQL statements based on logs:

   gs_dbmind component extract_log [l LOG_DIRECTORY] [f OUTPUT_FILE] [p LOG_LINE_PREFIX] [-d DATABASE] [-U USERNAME][--start_time] [--sql_amount] [--statement] [--json] [--max_reserved_period] [--max_template_num]

   The input parameters are as follows:

   • LOG_DIRECTORY: directory for storing pg_log.
   • OUTPUT_PATH: path for storing the output SQL statements, that is, path for storing the extracted service data.
   • LOG_LINE_PREFIX: specifies the prefix format of each log.
   • DATABASE (optional): database name. If this parameter is not specified, all databases are selected by default.
   • USERNAME (optional): username. If this parameter is not specified, all users are selected by default.
   • start_time (optional): start time for log collection. If this parameter is not specified, all files are collected by default.
   • sql_amount (optional): maximum number of SQL statements to be collected. If this parameter is not specified, all SQL statements are collected by default.
   • statement (optional): Collects the SQL statements starting with statement in pg_log log. If this parameter is not specified, the SQL statements are not collected by default.
   • json (optional): specifies that the collected log files are stored in JSON format after SQL normalization. If no format is specified, each SQL statement occupies a line.
   • max_reserved_period (optional): specifies the maximum number of days of reserving the template in incremental log collection in JSON mode. If this parameter is not specified, the template is reserved by default. The unit is day.
   • max_template_num (optional): Specifies the maximum number of templates that can be reserved in JSON mode. If this parameter is not specified, all templates are reserved by default.

   An example is provided as follows.

   gs_dbmind component extract_log $GAUSSLOG/pg_log/dn_6001 sql_log.txt '%m %c %d %p %a %x %n %e' -d postgres -U omm --start_time '2021-07-06 00:00:00' --statement

   NOTE

   If the -d/-U parameter is specified, the prefix of each log record must contain %d and %u. If transactions need to be extracted, %p must be specified. For details, see the log_line_prefix parameter. It is recommended that the value of max_template_num be less than or equal to 5000 to avoid long execution time of workload indexes.

3. Change the GUC parameter values set in 1 to the values before the setting.

Procedure for Using the Index Recommendation Script ​

1. Prepare a file that contains multiple DML statements as the input workload. Each statement in the file occupies a line. You can obtain historical service statements from the offline logs of the database.

2. To enable this function, run the following command:

   gs_dbmind component index_advisor [p PORT] [d DATABASE] [f FILE] [--h HOST] [-U USERNAME] [-W PASSWORD][--schema SCHEMA]
   [--max_index_num MAX_INDEX_NUM][--max_index_storage MAX_INDEX_STORAGE] [--multi_iter_mode] [--multi_node]  [--json] [--driver] [--show_detail]

   The input parameters are as follows:

   • PORT: port number of the connected database.
   • DATABASE: name of the connected database.
   • FILE: file path that contains the workload statement.
   • HOST (optional): ID of the host that connects to the database.
   • USERNAME (optional): username for connecting to the database.
   • PASSWORD (optional): password for connecting to the database.
   • SCHEMA: schema name.
   • MAX_INDEX_NUM (optional): maximum number of recommended indexes.
   • MAX_INDEX_STORAGE (optional): maximum size of the index set space.
   • multi_node (optional): specifies whether the current instance is a distributed database instance.
   • multi_iter_mode (optional): algorithm mode. You can switch the algorithm mode by setting this parameter.
   • json (optional): Specifies the file path format of the workload statement as JSON after SQL normalization. By default, each SQL statement occupies one line.
   • driver (optional): Specifies whether to use the Python driver to connect to the database. By default, gsql is used for the connection.
   • show_detail (optional): Specifies whether to display the detailed optimization information about the current recommended index set.

   Example:

   gs_dbmind component index_advisor 6001 postgres tpcc_log.txt --schema public --max_index_num 10 --multi_iter_mode

   The recommendation result is a batch of indexes, which are displayed on the screen in the format of multiple create index statements. The following is an example of the result.

   create index ind0 on public.bmsql_stock(s_i_id,s_w_id);
   create index ind1 on public.bmsql_customer(c_w_id,c_id,c_d_id);
   create index ind2 on public.bmsql_order_line(ol_w_id,ol_o_id,ol_d_id);
   create index ind3 on public.bmsql_item(i_id);
   create index ind4 on public.bmsql_oorder(o_w_id,o_id,o_d_id);
   create index ind5 on public.bmsql_new_order(no_w_id,no_d_id,no_o_id);
   create index ind6 on public.bmsql_customer(c_w_id,c_d_id,c_last,c_first);
   create index ind7 on public.bmsql_new_order(no_w_id);
   create index ind8 on public.bmsql_oorder(o_w_id,o_c_id,o_d_id);
   create index ind9 on public.bmsql_district(d_w_id);

   NOTE

   The value of the multi_node parameter must be specified based on the current database architecture. Otherwise, the recommendation result is incomplete, or even no recommendation result is generated.