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 cluster 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.
  • Pushdown of multiple conditions. 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 diagnostic 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 a hint to disable filter condition pushdown for specific columns. This operation takes effect only for queries that use hints.

Syntax

  • If the minor engine version is 3.1.4 or later, use the following hint:
    /*+ filter_not_pushdown_columns=[Schema1.table1:colName1|colName2;Schema2.table2:colName1|colName2] */
  • If the minor engine version is earlier than 3.1.4, use the following hint:
    /*+ no_index_columns=[table1.colName1;colName2,table2.colName1] */
Important
  • When you disable filter condition pushdown for specific columns, you can use a hint for tables in the same database or across databases. In versions earlier than 3.1.4, when you use a hint for tables across databases, you must make sure that table names are unique across databases. Otherwise, the hint may affect additional tables. In version 3.1.4 or later, you can use a hint for tables that have the same name across databases. This is because version 3.1.4 or later allows hints to use the Schema.table format to differentiate tables.
  • For more information about how to view the minor engine version of a cluster, see How can I view the version of an AnalyticDB for MySQL cluster? To update the minor engine version of a cluster, contact technical support.

Examples

  • Example 1:
    In this example, the minor 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 of the test01 database are not pushed down. 
    /*+ filter_not_pushdown_columns=[test01.table01:id|product] */
  • Example 2:
    In this example, the minor 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 of the test01 database as well as the key column in the table03 table of the test02 database are not pushed down. 
    /*+ filter_not_pushdown_columns=[test01.table01:id|product;test02.table03:key] */
  • Example 3:
    In this example, the minor engine version is 3.1.4 or later. In the current query, filter conditions that contain the id and product columns in the table02 table as well as 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 minor engine version is 3.1.4 or later, execute the following statement:
    set adb_config filter_not_pushdown_columns=[Schema1.tableName1:colName1|colName2;Schema2.tableName2:colName1|colName2]
  • If the minor engine version is earlier than 3.1.4, execute the following statement:
    set adb_config no_index_columns=[tableName1.colName1;colName2,tableName2.colName1]
Important
  • When you disable filter condition pushdown for specific columns, you can use a hint for tables in the same database or across databases. In versions earlier than 3.1.4, when you use a hint for tables across databases, you must make sure that table names are unique across databases. Otherwise, the hint may affect additional tables. In version 3.1.4 or later, you can use a hint for tables that have the same name across databases. This is because version 3.1.4 or later allows hints to use the Schema.table format to differentiate tables.
  • For more information about how to view the minor engine version of a cluster, see How can I view the version of an AnalyticDB for MySQL cluster? To update the minor engine version of a cluster, contact technical support.

Examples

In this example, the minor 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]