scenarios and methods for filter conditions without pushdown - AnalyticDB

Overview

By default, AnalyticDB for MySQL creates an index for every column when you create a table to improve data filtering efficiency. However, using an index is not always optimal and can sometimes degrade overall performance. While you could manually delete indexes, this can cause issues if an index is later required. The feature to disable filter condition pushdown in AnalyticDB for MySQL provides a better solution. It allows you to temporarily prevent filter conditions on specific columns from being pushed down at the query or cluster level, which improves overall query performance.

Consider disabling filter condition pushdown in the following scenarios:

Low-cardinality data. When a column has few unique values, filtering still returns a large amount of data, making an index ineffective.
High disk I/O pressure. If your queries or heavy data writes cause high I/O usage, using an index for data filtering can create contention for disk I/O resources and reduce its effectiveness.
Pushing down multiple conditions simultaneously, especially those with complex operations such as LIKE or string comparisons, can consume significant resources on storage nodes and affect overall performance.

Check whether filter conditions are pushed down

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

On the Execution Plan tab, click the stage that contains the TableScan operator.

Note
For information about how to navigate to the Execution Plan tab, see View diagnostic results.
Click View Stage Plans.
On the stage plan page, click the TableScan operator.
In the Properties panel on the right, look for the PushedDownFilter property. Its presence indicates that the filter condition was pushed down. For example, if the value of the PushedDownFilter property is id = BIGINT '277941', it indicates that the filter condition has been pushed down to the storage layer.
Note
- For a cluster in elastic mode, if a Filter operator appears in the execution plan of a downstream stage, its associated filter conditions were not pushed down.
- For a cluster in reserved mode, if a Filter operator appears in the current stage plan, its associated filter conditions were not pushed down.

Disable filter condition pushdown for specific columns in a query

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

Syntax

For minor engine version 3.1.4 or later, use the following hint:

/*+ filter_not_pushdown_columns=[Schema1.table1:colName1|colName2;Schema2.table2:colName1|colName2] */

For a minor engine version earlier than 3.1.4, use the following hint:
```
/*+ no_index_columns=[table1.colName1;colName2,table2.colName1] */
```

Important

You can use a hint to disable filter condition pushdown for specific columns in tables within the same database or across different databases. For a minor engine version earlier than 3.1.4, when you use a hint for tables across different databases, ensure that the table names are unique. Otherwise, the hint may affect unintended tables. In minor engine version 3.1.4 and later, hints use the Schema.table format to differentiate tables, which prevents unintended effects when using hints on tables with the same name in different databases.
To view the minor engine version of your cluster, see View cluster version information. To upgrade the minor engine version, contact technical support.

Examples

Example 1:
This example is for a minor engine version of 3.1.4 or later. In the current query, filter conditions on the id and product columns of the table01 table in the test01 database are not pushed down.
```
/*+ filter_not_pushdown_columns=[test01.table01:id|product] */
```
Example 2:
This example is for a minor engine version of 3.1.4 or later. In the current query, filter conditions on the id and product columns of the table01 table in the test01 database, and on the key column of the table03 table in the test02 database are not pushed down.
```
/*+ filter_not_pushdown_columns=[test01.table01:id|product;test02.table03:key] */
```
Example 3:
This example is for a minor engine version earlier than 3.1.4. In the current query, filter conditions on the id and product columns of the table02 table, and on the key column of 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

For minor engine version 3.1.4 or later, use the following statement:

set adb_config filter_not_pushdown_columns=[Schema1.tableName1:colName1|colName2;Schema2.tableName2:colName1|colName2]

For a minor engine version earlier than 3.1.4, use the following statement:

set adb_config no_index_columns=[tableName1.colName1;colName2,tableName2.colName1]

Important

You can use a hint to disable filter condition pushdown for specific columns in tables within the same database or across different databases. For a minor engine version earlier than 3.1.4, when you use a hint for tables across different databases, ensure that the table names are unique. Otherwise, the hint may affect unintended tables. In minor engine version 3.1.4 and later, hints use the Schema.table format to differentiate tables, which prevents unintended effects when using hints on tables with the same name in different databases.
To view the minor engine version of your cluster, see View cluster version information. To upgrade the minor engine version, contact technical support.

Examples

This example is for a minor engine version of 3.1.4 or later. For all queries in the current cluster, filter conditions on the id column of the table02 table in the test02 database are not pushed down.

set adb_config filter_not_pushdown_columns=[test02.table02:id]