Configure join reorder for the optimizer

SQL is a declarative language, which means it specifies what result to return but not how to compute it. For any given SQL statement, multiple valid query plans can produce the correct result. For example:

SELECT * FROM t0, t1, t2, t3 WHERE t0.a = t1.a AND t1.a = t2.a AND t2.a = t3.a AND t3.b = t1.b;

For the preceding SQL statement, both of the following query plans can return the correct result.Plan A and Plan B are known as equivalent query plans. The optimizer explores equivalent query plans for an SQL statement by applying transformations. For example, t1 INNER JOIN t2 and t2 INNER JOIN t1 are a pair of equivalent query plans. The optimizer can generate t2 INNER JOIN t1 from t1 INNER JOIN t2. This type of transformation is called a query transformation rule.

The query optimizer follows this workflow:

1. The optimizer receives an initial query plan that the database generates by parsing an SQL statement.

2. It then applies query transformation rules to the initial plan to generate equivalent query plans.

3. Using statistics and a cost model, the optimizer selects the plan with the lowest estimated execution cost and passes it to the execution engine as the final execution plan.

The query optimization feature relies on statistics to perform cardinality estimation and cost calculation to determine the best query plan. In IMCI, table statistics include the following:

• A histogram describes the value distribution of a column and is mainly used to estimate the selectivity of value ranges and equality predicates on a single table.

• The number of distinct values in a column is mainly used to estimate the number of groups in a Group By clause and can also help estimate the selectivity of equality predicates.

• Other constraints, such as whether the column has a unique index or a foreign key constraint.

The query optimizer calculates the cost of each operator in a query plan based on two factors:

• The total number of rows processed by the operator, which can be estimated from statistics.

• The algorithmic complexity of each operator used in the query plan.

Your PolarDB cluster must meet one of the following version requirements:

• PolarDB for MySQL 8.0.1 with revision 8.0.1.1.31 or later.

• PolarDB for MySQL 8.0.2 with revision 8.0.2.2.12 or later.

You can view your cluster version number to confirm your cluster version.

The following scenarios can cause significant errors in cardinality estimation, which may lead the optimizer to choose a suboptimal query plan. You can use HINT syntax to guide the optimizer toward a better query plan.

• A query with a predicate that uses a comparison operator on different columns of the same table, such as t1.c1>t1.c2.

• A query with a predicate that uses an operator that cannot be estimated using statistics, such as t1.c1 MOD 2=1 or t1.c2 LIKE '%ABC%'.

• A query with a predicate that contains an expression that cannot be computed during optimization, such as t1.c1+t1.c3>100.

• A query where columns involved in an operator lack the statistics needed to estimate predicate selectivity, such as SELECT a, SUM(b) FROM t1 HAVING SUM(b) > 10.

• Multiple predicates are connected by an AND operator, such as t1.c1>10 AND t1.c3<5.

• The query has too many nested layers.

• The query joins too many tables. You can adjust the number of joins the IMCI optimizer searches by modifying the loose_imci_max_enum_join_pairs parameter.

The following example uses TPC-H Q8, which is a multi-table query that includes aggregate functions.

SELECT
  o_year,
  SUM(
    CASE
      WHEN nation = 'BRAZIL' THEN volume
      ELSE 0
    END
  ) / SUM(volume) AS mkt_share
FROM
  (
    SELECT
      EXTRACT(
        year
        FROM
          o_orderdate
      ) AS o_year,
      l_extendedprice * (1 - l_discount) AS volume,
      n2.n_name AS nation
    FROM
      lineitem,
      orders,
      part,
      supplier,
      customer,
      nation n1,
      nation n2,
      region
    WHERE
      p_partkey = l_partkey
      AND s_suppkey = l_suppkey
      AND l_orderkey = o_orderkey
      AND o_custkey = c_custkey
      AND c_nationkey = n1.n_nationkey
      AND n1.n_regionkey = r_regionkey
      AND r_name = 'AMERICA'
      AND s_nationkey = n2.n_nationkey
      AND o_orderdate BETWEEN DATE '1995-01-01'
      AND DATE '1996-12-31'
      AND p_type = 'ECONOMY ANODIZED STEEL'
  ) AS all_nations
GROUP By
  o_year
ORDER BY
  o_year;

• The query plan with IMCI optimization disabled is as follows:In this plan, numerous joins generate large intermediate result sets, which increases the amount of data and the processing cost for subsequent operators, leading to longer query latency. On a 32-core machine with a TPC-H SF100 dataset, this query took 7,017 ms to complete.

• The query plan with IMCI optimization enabled is as follows:The IMCI optimizer reorders the joins, reducing the output size of most join operators to millions of rows. This effectively reduces the processing cost for subsequent operators. On the same 32-core machine with the TPC-H SF100 dataset, this query took 1,900 ms to complete, a 73% reduction in query time.