Improve paged query performance with LIMIT OFFSET pushdown - PolarDB

Paged queries slow down as page numbers grow because the standard MySQL execution model transmits all candidate rows to the SQL layer — rows that are then immediately discarded based on the OFFSET value. PolarDB for MySQL reduces this overhead by pushing LIMIT OFFSET evaluation down to the storage engine layer, so that unnecessary rows are filtered before they are transmitted.

How it works

In standard MySQL, the LIMIT clause is evaluated at the SQL layer. The storage engine sends all candidate rows to the SQL layer, which then skips rows based on the OFFSET value and returns the final result set. For paged queries with large OFFSET values, this means the storage engine transmits a large number of rows that are immediately discarded — and the cost grows linearly as page numbers increase.

With LIMIT OFFSET pushdown enabled, PolarDB filters rows at the storage engine layer before they reach the SQL layer. This works in two main scenarios:

No WHERE conditions at the SQL layer: When predicates are fully pushed down via the detach_range_condition predicate pushdown feature, no filtering remains at the SQL layer. LIMIT OFFSET can then be pushed down directly.
Secondary index access with table lookups: When a query uses a secondary index and also needs columns from the primary table, LIMIT OFFSET pushdown skips unnecessary table lookups at the storage engine layer, avoiding retrieving rows that would be discarded anyway.

Prerequisites

Before enabling this feature, make sure your PolarDB for MySQL cluster meets the following version requirements:

Version 8.0, revision version 8.0.1.1.16 or later
Version 8.0, revision version 8.0.2.2.0 or later

To check your cluster version, see Query the engine version.

Limitations

LIMIT OFFSET pushdown only applies when the OFFSET value is greater than 512. For small OFFSET values, the number of rows skipped is small enough that the overhead of pushdown coordination outweighs the benefit over the standard execution path.

To remove this restriction, set ignore_polar_optimizer_rule to ON. For instructions, see Specify cluster and node parameters.

Parameter	Level	Default	Description
`ignore_polar_optimizer_rule`	Global and session	OFF	Controls the OFFSET threshold restriction. Set to `ON` to disable the threshold and allow pushdown for any OFFSET value.

Enable or disable LIMIT OFFSET pushdown

LIMIT OFFSET pushdown is enabled by default. Use the loose_optimizer_switch parameter to change its state. For instructions on modifying parameters, see Specify cluster and node parameters.

Parameter	Level	Variable	Default	Description
`loose_optimizer_switch`	Global and session	`limit_offset_pushdown`	ON	Enables or disables LIMIT OFFSET pushdown.
`loose_optimizer_switch`	Global and session	`detach_range_condition`	ON	Enables or disables predicate pushdown. LIMIT OFFSET pushdown depends on this feature when WHERE conditions are present.

Verify that pushdown is active

Run EXPLAIN on your query and check the Extra field. When LIMIT OFFSET pushdown is active, the output includes Using limit-offset pushdown. If this string is absent, the query did not meet the pushdown conditions — see When pushdown does not apply for the most common reasons.

The following examples use the TPC-H schema.

No predicate conditions (Q1)

A full table scan with no WHERE clause. The primary table is accessed directly. Because no filtering remains at the SQL layer, LIMIT OFFSET is pushed down.

EXPLAIN
SELECT *
FROM lineitem
LIMIT 10000000, 10\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 59440464
     filtered: 100.00
        Extra: Using limit-offset pushdown

The Extra: Using limit-offset pushdown value confirms that row filtering is happening at the storage engine layer rather than the SQL layer.

Range conditions on the primary key (Q2)

When WHERE conditions are based on the primary key, the predicate pushdown feature (detach_range_condition) removes them from the SQL layer, allowing LIMIT OFFSET to be pushed down.

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 limit-offset pushdown

Secondary index with table lookups (Q3)

When a secondary index is used and the query requires columns from the primary table, LIMIT OFFSET pushdown skips unnecessary primary table lookups for rows that fall outside the result window.

EXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 LIMIT 5000000, 10\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: range
possible_keys: i_l_partkey,i_l_suppkey_partkey
          key: i_l_suppkey_partkey
      key_len: 5
          ref: NULL
         rows: 11123302
     filtered: 100.00
        Extra: Using limit-offset pushdown

ORDER BY with an index

When an ORDER BY clause is satisfied by an index, predicates are removed at the SQL layer and LIMIT OFFSET is pushed down.

EXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 ORDER BY l_partkey LIMIT 5000000, 10\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: range
possible_keys: i_l_partkey,i_l_suppkey_partkey
          key: i_l_suppkey_partkey
      key_len: 5
          ref: NULL
         rows: 11123302
     filtered: 100.00
        Extra: Using limit-offset pushdown

When pushdown does not apply

LIMIT OFFSET pushdown requires that no filtering work remains at the SQL layer. If Extra does not show Using limit-offset pushdown, check whether any of the following conditions apply:

Condition	Reason pushdown is skipped
OFFSET value is 512 or less	Skipping a small number of rows is cheaper without pushdown overhead. Set `ignore_polar_optimizer_rule` to `ON` to override.
WHERE conditions cannot be fully pushed to the storage engine	Filtering that remains at the SQL layer prevents LIMIT OFFSET from being pushed down. This happens when the query uses a filesort or a non-indexed ORDER BY.

Performance improvements

The following figure shows query time improvements measured with a TPC-H dataset at scale factor 10, comparing Q1, Q2, and Q3 with and without LIMIT OFFSET pushdown enabled.