This topic describes how and where to use filter conditions without pushdown.

Overview

By default, AnalyticDB for MySQL creates indexes for all columns when you create a table, which improves the data filtering efficiency. However, in some scenarios, indexes used to filter data may not result in high filtering efficiency or may even degrade the overall performance. In this case, we recommend that you do not use indexes to filter data. You can manually delete indexes for some columns. However, this may cause an issue where no indexes are available when they are required. AnalyticDB for MySQL supports filter conditions without pushdown to temporarily block the pushdown capabilities of filter conditions for some columns at the query or instance level. This improves the overall query efficiency.

We recommend that you do not use indexes to filter data in the following scenarios:
  • A small number of unique values. This means a large amount of data is returned after data filtering. In this case, indexes may not be suitable for data filtering.
  • High disk I/O usage. If large amounts of I/O resources are occupied due to your query characteristics or data writes, indexes used to filter data may cause competition for disk I/O resources and decrease the filtering efficiency.
  • Multiple conditions are pushed down. If multiple conditions are pushed down and these conditions contain complex operations such as LIKE and string comparison, large amounts of resources on storage nodes are consumed. This affects the overall performance.

Check whether filter conditions are pushed down

You can check whether filter conditions are pushed down on the execution page.

  1. On the Execution Plan tab, click the stage that contains the TableScan operator.
    Note For more information about how to go to the Execution Plan tab, see View diagnosis results.
  2. Click View Stage Plans.
  3. On the stage plan page, click the TableScan operator.
  4. In the right-side Properties section, check whether the PushedDownFilter property is displayed. If the filter condition is pushed down, the property is displayed. Otherwise, the property is not displayed. sql-exe-plan-attribute
    Note You can also check whether filter conditions are pushed down by using execution plans.
    • For clusters in elastic mode, you can check whether the Filter operator is displayed in the execution plan of a downstream stage. If the Filter operator is displayed, the filter conditions related to the operator are not pushed down.
    • For clusters in reserved mode, you can check whether the Filter operator is displayed in the current stage plan. If the Filter operator is displayed, the filter conditions related to the operator are not pushed down.

Disable filter condition pushdown for specific columns in a query

In a specific query, you can use hints to disable filter condition pushdown for some columns. This operation takes effect only for queries that use hints.

Syntax:

If the engine version is 3.1.4 or later, use the following hint:
/*+ filter_not_pushdown_columns=[${database}.${tableName}:${col1Name}|${col2Name}] */
If the engine version is earlier than 3.1.4, use the following hint:
/*+ no_index_columns=[${tableName}.${col1Name};${col2Name},${tableName1}.${col1Name}] */
Note For more information about how to query engine versions, see How can I view the version of an AnalyticDB for MySQL cluster?

Example 1:

In this example, the engine version is 3.1.4 or later. In the current query, filter conditions that contain the id and product columns in the table01 table are not pushed down.

/*+ filter_not_pushdown_columns=[test01.table01:id|product] */

Example 2:

In this example, the engine version is earlier than 3.1.4. In the current query, filter conditions that contain the id and product columns in the table02 table and the key column in the table03 table are not pushed down.

/*+ no_index_columns=[table02.id;product,table03.key] */

Disable filter condition pushdown for specific columns in a cluster

You can execute the following statements to disable filter condition pushdown for specific columns in all queries of the current cluster.

Syntax:

If the engine version is 3.1.4 or later, execute the following statement:
set adb_config filter_not_pushdown_columns=[${database}.${tableName}:${col1Name}|${col2Name}]
If the engine version is earlier than 3.1.4, execute the following statement:
set adb_config no_index_columns=[${tableName}.${col1Name};${col2Name},${tableName1}.${col1Name}]
Note For more information about how to query engine versions, see How can I view the version of an AnalyticDB for MySQL cluster?

Example:

In this example, the engine version is 3.1.4 or later. In all queries of the current cluster, filter conditions that contain the id column in the table02 table of the test02 database are not pushed down.

set adb_config filter_not_pushdown_columns=[test02.table02:id]