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

NOTE:

• This function supports only a single SELECT statement and does not support other types of SQL statements.
• Column-store tables, segment-paged tables, common views, materialized views, global temporary tables, and encrypted databases are not supported.

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:

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

MogDB=# 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 t1 table. 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:

MogDB=# 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)

NOTE: Parameters of the system function gs_index_advise() are of the text type. If the parameters contain special characters such as single quotation marks ('), you can use single quotation marks (') to escape the special characters. For details, see the preceding example.