Scenarios and methods for disabling filter condition pushdown - AnalyticDB

AnalyticDB for MySQL indexes all columns by default to accelerate data filtering. In some cases, index-based filtering hurts rather than helps performance. This document explains when to disable filter condition pushdown, how to apply it at the query or cluster level, and how to verify the result.

Disabling pushdown is a targeted workaround, not a substitute for proper index design. If you consistently hit the scenarios below, consider reviewing your table schema and index strategy as a longer-term fix.

When to disable pushdown

Disable filter condition pushdown when any of the following is true:

Low cardinality columns — A column has few unique values, so filtering returns a large fraction of the data. Indexes provide little benefit in this case.
High disk I/O — Your workload already saturates disk I/O from queries or data writes. Index-based filtering competes for the same I/O resources and degrades overall throughput.
Multiple complex pushdown conditions — Several conditions are pushed down simultaneously and involve operations like LIKE or string comparison. This consumes excessive resources on storage nodes.

Disable pushdown for a single query

Use a SQL hint to disable filter condition pushdown for specific columns in one query. The hint applies only to that query and has no effect on the cluster.

Syntax

The syntax depends on your cluster's minor engine version.

Version 3.1.4 or later:

/*+ filter_not_pushdown_columns=[<schema1>.<table1>:<col1>|<col2>;<schema2>.<table2>:<col1>] */

Earlier than version 3.1.4:

/*+ no_index_columns=[<table1>.<col1>;<col2>,<table2>.<col1>] */

Separator reference:

Separator	Meaning	Example
`:`	Separates the table name from the column list	`table01:id\|product`
`\|`	Separates columns within the same table	`id\|product`
`;`	Separates multiple table entries	`test01.table01:id;test02.table03:key`

Important

In version 3.1.4 and later, use the schema.table format to reference tables across databases. This lets the optimizer distinguish tables that share the same name in different databases. In earlier versions, table names must be unique across all databases when using cross-database hints; otherwise, the hint may unintentionally affect additional tables. To check your cluster's minor engine version, see How can I view the version of an AnalyticDB for MySQL cluster?. To upgrade the version, contact technical support.

Examples

Disable pushdown for two columns in one table (v3.1.4+):

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

Disables pushdown for the id and product columns in test01.table01.

Disable pushdown across two databases (v3.1.4+):

/*+ filter_not_pushdown_columns=[test01.table01:id|product;test02.table03:key] */

Disables pushdown for id and product in test01.table01, and key in test02.table03.

Earlier than v3.1.4:

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

Disables pushdown for id and product in table02, and key in table03.

Verify the hint is applied

After running a query with the hint, confirm that filter condition pushdown is disabled by checking the execution plan. See Check pushdown status below.

Disable pushdown for all queries in a cluster

Execute a set adb_config statement to disable filter condition pushdown for specific columns across all queries in the cluster. This setting persists until you change it, and applies cluster-wide regardless of individual query hints.

Syntax

Version 3.1.4 or later:

set adb_config filter_not_pushdown_columns=[<schema1>.<table1>:<col1>|<col2>;<schema2>.<table2>:<col1>]

Earlier than version 3.1.4:

set adb_config no_index_columns=[<table1>.<col1>;<col2>,<table2>.<col1>]

The separator conventions are the same as for query-level hints. See the separator reference table above.

Important

The same cross-database naming rules apply. In version 3.1.4 and later, use the schema.table format. In earlier versions, make sure table names are unique across all databases. To check your cluster's minor engine version, see How can I view the version of an AnalyticDB for MySQL cluster?. To upgrade the version, contact technical support.

Example

Disable pushdown for the id column in test02.table02 (v3.1.4+):

set adb_config filter_not_pushdown_columns=[test02.table02:id]

This affects all queries in the cluster that reference the id column in test02.table02.

Verify the configuration is applied

After running the statement, confirm the setting is effective by running a representative query and checking its execution plan. See Check pushdown status below.

Check pushdown status

Use the execution plan to determine whether filter conditions are being pushed down.

On the Execution Plan tab, click the stage that contains the TableScan operator.
For instructions on navigating 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 section on the right, look for the PushedDownFilter property.
- Present — the filter condition is pushed down.
- Absent — the filter condition is not pushed down.

Alternative: check via the Filter operator

You can also identify pushdown status directly in the execution plan by looking for the Filter operator:

Elastic mode clusters — If the Filter operator appears in the execution plan of a downstream stage, the associated filter conditions are not pushed down.
Reserved mode clusters — If the Filter operator appears in the current stage plan, the associated filter conditions are not pushed down.