All Products
Search

This Product

    Tablestore:Computing pushdown

    Document Center

    Tablestore:Computing pushdown

    Last Updated:Feb 14, 2025

    Search indexes support features such as conditional filtering, aggregation, and sorting. After you create a search index, the system can make full use of the computing capability of the search index and push some SQL computing tasks down to the search index for execution. This eliminates the need for full table scans and improves computing efficiency.

    Note

    For more information about search indexes, see Search index.

    Scenarios

    If a search index contains the data columns involved in SQL statements, the SQL engine reads data by using the search index and pushes down the operators that are supported by the search index. For example, a table named exampletable contains columns a, b, c, and d. A search index of the table contains columns a, b, and c, and the columns are indexed. The SQL engine reads data by using the search index when only columns a, b, and c are involved in SQL statements. 

    SELECT a, b, c, d FROM exampletable; /* The search index does not contain columns a, b, c, and d. The SQL engine reads data by scanning the entire table and does not push down operators. */
SELECT a, b, c FROM exampletable;    /* The search index contains columns a, b, and c. The SQL engine reads data by using the search index and pushes down operators. */

    Usage notes

    If an expression in the WHERE clause of the SQL statement does not match the actual field type, the expression cannot be pushed down to the search index. This impairs the overall execution efficiency of the SQL statement.

    For example, the data type of fields a and b is BIGINT. If the WHERE clause is WHERE a = '123' and b = 234, a = '123' cannot be pushed down to the search index because the expression contains implicit CAST operations. Only b = 234 is pushed down to the search index. To push the complete WHERE clause to the search index, change the WHERE clause to a = 123 and b = 234.

    Prerequisites

    Operators that can be pushed down

    The following table describes the operators that can be pushed down to search indexes for execution in SQL statements.

    Type

    Operator

    Pushdown limit

    Logical operators

    AND and OR

    The NOT operator cannot be pushed down.

    Relational operators

    =, !=, <, <=, >, >=, and BETWEEN ... AND ...

    Operator pushdown is supported only for comparison between data columns and constants. Operator pushdown is not supported for comparison between data columns. 

    SELECT * FROM exampletable WHERE a > 1;  /* Operator pushdown is supported for comparison between data columns and constants. */
SELECT * FROM exampletable WHERE a > b;  /* Operator pushdown is not supported for comparison between data columns. */

    Aggregate functions

    • Basic aggregation: MIN, MAX, COUNT, AVG, SUM, and ANY_VALUE

    • Deduplication and aggregation: COUNT(DISTINCT col_name)

    • Grouping: GROUP BY col_name

    An aggregate function can aggregate all data or data in a GROUP BY group. Operator pushdown is supported only when the aggregate function supports pushdown and the function parameter is a data column. 

    SELECT COUNT(*) FROM exampletable;           /* Operator pushdown is supported for the special usage of COUNT(*). */
SELECT SUM(a) FROM exampletable;             /* Operator pushdown is supported when the function parameter is a data column. */
SELECT a, b FROM exampletable GROUP BY a, b; /* Operator pushdown is supported for grouping by data column. */
SELECT a FROM exampletable GROUP BY a+1;     /* Operator pushdown is not supported for grouping by expression. */
SELECT SUM(a+b) FROM exampletable;           /* Operator pushdown is not supported when the function parameter is an expression. */

    LIMIT

    • LIMIT row_count

    • ORDER BY col_name LIMIT row_count

    Operator pushdown is supported only when the parameter in the ORDER BY clause is a data column. 

    SELECT * FROM exampletable ORDER BY a LIMIT 1;     /* Operator pushdown is supported for sorting by data column. */
SELECT * FROM exampletable ORDER BY a, b LIMIT 1;  /* Operator pushdown is supported for sorting by data column. */
SELECT * FROM exampletable ORDER BY a+1 LIMIT 1;   /* Operator pushdown is not supported for sorting by expression. */