All Products
Search

This Product

    AnalyticDB:Operator-level diagnostic results

    Document Center

    AnalyticDB:Operator-level diagnostic results

    Last Updated:Aug 24, 2026

    The SQL diagnosis feature in AnalyticDB for MySQL collects statistics for SQL queries at the query, stage, and operator levels. It then uses these statistics to provide diagnoses and optimization suggestions. This topic describes how to view and analyze operator-level diagnostic results.

    Diagnostic result types

    Note For more information, see View diagnostic results.

    Low aggregation degree for an aggregation operator

    • Problem

      The aggregation degree of an aggregation operator is the ratio of the input data volume (InputDataSize) to the output data volume (OutputDataSize) in a GROUP BY operation. A smaller ratio indicates a lower aggregation degree and less effective aggregation. In AnalyticDB for MySQL, grouping and aggregation operations consist of two steps: partial aggregation (PARTIAL) and final aggregation (FINAL). If an aggregation operator has many groups, the aggregation degree is low. The partial aggregation step then fails to reduce network data transfer and instead consumes many compute resources.

    • Suggestion

      Consider skipping the partial aggregation step. Redistribute the data among nodes and then perform a single final aggregation. For more information, see Optimize grouping and aggregation queries.

    Filter conditions are not pushed down

    • Problem
      AnalyticDB for MySQL creates an index for every field in a table when storing data. These indexes accelerate data filtering during queries. However, AnalyticDB for MySQL does not push down filter conditions in the following scenarios:
      • The filter condition pushdown feature is disabled because the query statement uses the no_index_columns or filter_not_pushdown_columns hint, or the cluster uses the adb_config filter_not_pushdown_columns configuration.
      • The filter condition uses a function, such as the cast operator.
      • The relevant field in the filter condition does not have an index. This can happen if the no_index keyword was specified when the table was created, or if the index was deleted by running DROP INDEX after the table was created.
    • Suggestion
      • If the filter condition pushdown feature is disabled due to a hint in the query statement or a cluster configuration, investigate why the hint or configuration is used and determine whether you can remove it. For more information, see Do not push down filter conditions.
      • If a function is used, consider applying the function when you write the data, and then remove the function from the query.
      • If filter conditions are not pushed down because the relevant field does not have an index, investigate why the index is missing.

    Data bloat in a Join operation

    • Problem

      The data bloat rate of a Join operation is the ratio of the number of output rows to the number of input rows. The number of input rows is the sum of rows from the left and right tables. With a proper join condition, the number of output rows is typically smaller than the number of input rows. If the number of output rows is greater than the number of input rows, data bloat occurs. Data bloat consumes large amounts of compute and memory resources, which slows down the query.

    • Suggestion
      • If data bloat is caused by data characteristics, such as when many identical values exist in both tables, consider filtering out these values during the table filtering stage to exclude them from the Join operation.
      • If data bloat is caused by a suboptimal join order, manually adjust the join order. For more information, see Manually adjust the join order.

    The right table in a Join operation is too large

    • Problem
      In AnalyticDB for MySQL, the right table of a Join operation is usually the builder table, which is used to build a hash or set structure in memory. A large right table can consume excessive memory resources and affect the overall stability of the cluster. The right table might be too large for the following reasons:
      • The SQL statement contains a Left Join. The right table of a Left Join must be the builder table during execution. Therefore, if the right table in a Left Join is large, it consumes a large amount of memory resources.
      • AnalyticDB for MySQL makes an incorrect estimation of the data volume of the left and right tables. This can be caused by factors such as expired statistics.
    • Suggestion

      For optimization, we recommend that you rewrite a Left Join as a Right Join. For more information, see Rewriting a Left Join as a Right Join.

    A Cross Join operation exists

    • Problem

      A Cross Join is a Join operation that has no join condition. The number of output rows is the product of the number of rows in the left and right tables. If both tables are large, a Cross Join can severely affect the stability of the AnalyticDB for MySQL cluster.

    • Suggestion

      Add a join condition to eliminate the Cross Join.

    A scan operator reads too many fields

    • Problem

      A scan operator filters and reads detailed data from the storage layer of AnalyticDB for MySQL. If a SELECT statement includes many fields, a large amount of detailed data must be read. This consumes significant disk I/O resources and affects the overall stability of the AnalyticDB for MySQL cluster.

    • Suggestion

      Optimize the SQL statement to remove unnecessary fields from the SELECT statement.

    Data skew in a table scan

    • Problem

      AnalyticDB for MySQL uses a distributed execution architecture. For large tables, you must specify a distribution key. Data is distributed to different storage nodes based on this key when data is written. If the values of the distribution key are unevenly distributed, the data is also stored unevenly across the nodes. This causes a long tail effect during data reads, where some nodes take much longer than others. This imbalance adversely affects query performance.

    • Suggestion

      Reduce data skew in table scans by selecting a suitable distribution key. For more information about optimization methods, see Storage space diagnosis.

    Inefficient index

    • Problem

      When AnalyticDB for MySQL uses an index for data filtering, the index may not perform as expected if the selectivity of the filtered field is low. Selectivity is the ratio of the output data volume to the input data volume of the filter operator.

    • Suggestion

      Consider not pushing down the filter conditions. Instead, perform the filtering operation directly on the compute nodes. For more information, see Do not push down filter conditions.