Index Recommendation
Tags: opengauss
Published: 2023-03-09 06:46:13

openGauss index recommendation has three functions, including single-query index recommendation, virtual index recommendation, and workload-level index recommendation.

**Single-query Index Recommendation**
The single-query index recommendation function supports direct operations in the database and generates recommended indexes for a single query statement entered by users based on the semantic information of the query statement and the statistics data of the database. This function supports only a single SELECT statement and does not support other types of SQL statements. This function depends on the gs_index_advise function.

Table 1 Single-query index recommendation APIs

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d7phogd4mwo68w7b7v27.png)

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. To use the tool, do as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fnmje0oj8niupzhnq91z.png)

The preceding statement indicates that an index should be created on the c_w_id column of the bmsql_customer table. To create the index, run the following command:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0a8zbbw1bx7kzpjl38u7.png)

A composite index may be recommended for some SQL statements, for example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ri7qjuhw1zk58kp7sijz.png)

The preceding statement indicates that a composite index (age, sex) should be created on table t1. To create the index, run the following command:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9auk8vw1ay4f4xbzdu3b.png)

**Virtual Index Recommendation**
The virtual index recommendation function supports direct operations in the database and 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 estimate cost of the index on the specified query statement by using the optimizer. For details about the functions and GUC parameters related to virtual indexes, see Table 2 and Table 3.

Table 2 Virtual index functions

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jb5xsi3nlf3lq6jw64ln.png)

To use the tool, do as follows:

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cwd0fcebvt1brzfifjat.png)

2. Enable the GUC parameter enable_hypo_index. This parameter determines 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:

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8gokfcb7ytylc5tb04ol.png)

To enable the GUC parameters, run the following command:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6tx9rwwzrmilou17tz5p.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/934kjch2gbdoblb3zuw7.png)

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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jtuh9ejisjizyztkrizi.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kbol901aj44dg1rglzld.png)

5. Delete the virtual index.

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w6pu41l2w9b5zx7g6qv5.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nazsvpx7uwohpao7bcem.png)

**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.

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. Run the Python script index_advisor_workload.py:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ck3e30hfjarjsa5fgggh.png)

The input parameters are as follows:

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/424rd2s9a3s5p5b5q2m7.png)

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i3rw7h2jstwd1c768jkn.png)