All Products
Search

This Product

    Tablestore:Computing pushdown

    Document Center

    Tablestore:Computing pushdown

    Last Updated:Jul 16, 2024

    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 Overview.

    Scenarios

    If a search index contains 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 the b, c, and d columns that are indexed. The SQL engine reads data by using the search index only when the b, c, and d columns are involved in SQL statements. 

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

    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. */