Use full predicate pushdown to improve query performance - PolarDB

Full predicate pushdown eliminates the redundant row filtering that MySQL performs at the SQL layer after a range-based index scan, reducing compute overhead for large range queries on PolarDB for MySQL.

Prerequisites

Before you begin, ensure that you have:

A PolarDB for MySQL cluster running version 8.0
A revision version of 8.0.1.1.5 or later, or 8.0.2.2.0 or later

For information about how to check your cluster version, see Query the engine version.

How it works

In MySQL Community Edition, when a query uses a range condition to scan an index, the condition is evaluated twice:

Without full predicate pushdown:

The storage engine scans the index using the range condition and returns matching rows.
The SQL layer re-filters the returned rows using the same range condition.

With full predicate pushdown:

The storage engine scans the index using the range condition and returns matching rows.
The range condition is discarded. The SQL layer skips re-filtering because the storage engine has already applied it.

This duplicate filtering wastes compute resources. Full predicate pushdown removes the redundant SQL-layer filter pass.

Enable full predicate pushdown

Use the detach_range_condition variable of the loose_optimizer_switch parameter to enable or disable full predicate pushdown. For more information, see Specify cluster and node parameters.

Verify with EXPLAIN

Use the Extra field in an EXPLAIN output to confirm whether full predicate pushdown is active:

`Extra` value	Meaning
`Using where`	The SQL layer is re-filtering rows. Full predicate pushdown is disabled.
`Using index`	The SQL layer skips re-filtering. Full predicate pushdown is active.

Disabled (baseline)

The following example uses the TPC-H schema. With detach_range_condition set to off, the EXPLAIN output shows Using where, indicating that the range condition is applied again at the SQL layer (TPC-H Q2).

set @@optimizer_switch='detach_range_condition=off';
EXPLAIN SELECT * FROM lineitem WHERE l_orderkey > 10 AND l_orderkey < 60000000 LIMIT 10000000, 10\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: range
possible_keys: PRIMARY,i_l_orderkey,i_l_orderkey_quantity
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 29720232
     filtered: 100.00
        Extra: Using where

Enabled

With ignore_polar_optimizer_rule set to on, range conditions are discarded after the storage engine scan. The EXPLAIN output shows Using index, indicating that the SQL layer skips re-filtering. The following examples use TPC-H Q5 and Q6.

set @@ignore_polar_optimizer_rule=on;
EXPLAIN SELECT COUNT(*) FROM lineitem WHERE l_suppkey > 10 AND l_suppkey < 50000\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: range
possible_keys: i_l_suppkey_partkey,i_l_suppkey
          key: i_l_suppkey
      key_len: 5
          ref: NULL
         rows: 29720232
     filtered: 100.00
        Extra: Using index

set @@ignore_polar_optimizer_rule=on;
EXPLAIN SELECT COUNT(*) FROM LINEITEM WHERE l_receiptDATE > '1992-01-03' AND l_receiptDATE < '1994-12-31'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: LINEITEM
   partitions: NULL
         type: range
possible_keys: i_l_receiptdate
          key: i_l_receiptdate
      key_len: 4
          ref: NULL
         rows: 29720232
     filtered: 100.00
        Extra: Using index

Test results

The following figure shows the performance difference between enabling and disabling full predicate pushdown, measured with TPC-H queries Q5 and Q6 at a scale factor of 10.

Actual performance gains depend on your workload, data distribution, and cluster configuration. Run your own benchmarks to evaluate the impact in your environment.