PolarDB:Partial Result Cache (PTRC)

How it works

PTRC is "partial" in two ways:

• It caches intermediate result sets of specific operators, not the entire query result.

• Even for eligible operators, it does not cache all result sets — the cache scope is bounded by memory limits.

PTRC stores results in key-value pairs, where the key is the correlated parameter of the operator and the value is the cached intermediate result. Each time the operator runs, the system checks whether the key exists in the cache:

• Cache miss: the operator runs and the result is added to the cache.

• Cache hit: the cached result is returned directly, skipping execution.

Example: TPC-H Q17

TPC-H Q17 illustrates how PTRC works in practice.

select
        sum(l_extendedprice) / 7.0 as avg_yearly
from
        lineitem,
        part
where
        p_partkey = l_partkey
        and p_brand = 'Brand#34'
        and p_container = 'MED BOX'
        and l_quantity < (
                select
                        0.2 * avg(l_quantity)
                from
                        lineitem
                where
                        l_partkey = p_partkey
        );

The correlated subquery runs once for each row produced by the join of part and lineitem. The correlated parameter is p_partkey — the driving table column used in the join condition. Because the join result contains many rows with the same p_partkey value, PTRC caches the subquery result for each distinct p_partkey and reuses it for subsequent rows with the same value. The key is p_partkey; the value is true or false.

To confirm that PTRC is active, run EXPLAIN on the query. An operator named Partial result cache appears before the subquery in the execution plan.

Prerequisites

Before enabling or tuning PTRC, confirm that your cluster meets the following requirements:

• A PolarDB for MySQL cluster running MySQL 8.0

• Revision version 8.0.2.2.9 or later

To check your cluster version, see Engine versions.

Parameters

PTRC is enabled by default and controlled through six parameters. Together, they define three aspects of PTRC behavior: whether to engage, when to engage, and what to do when memory fills up.

How the optimizer decides whether to use PTRC

PTRC consumes resources — it performs a cache lookup on every operator execution and holds cached results in memory. The query optimizer uses partial_result_cache_cost_threshold and partial_result_cache_low_hit_rate to decide whether the benefit justifies the cost.

Step 1: Cost check

If the overall query cost is below partial_result_cache_cost_threshold, the optimizer skips PTRC entirely. For short queries, the cache lookup overhead can outweigh any performance gain.

Step 2: Hit rate estimation

If the query cost exceeds the threshold, the optimizer identifies all eligible operators and estimates the hit rate for each:

hit_rate = (fanout - ndv) / fanout

• fanout: the total number of times the operator executes.

• ndv: the number of distinct values of the correlated parameter (the cache key).

If the estimated hit rate is below partial_result_cache_low_hit_rate, PTRC is not used for that operator.

When estimation is unavailable

The optimizer relies on indexes or histograms to estimate ndv. If the correlated parameter column has neither, ndv cannot be accurately estimated. In that case, the optimizer enables PTRC without a hit rate estimate and delegates the decision to the dynamic feedback mechanism at runtime.

Dynamic feedback mechanism

At runtime, PTRC monitors its own hit rate and memory usage and adjusts behavior accordingly.

Hit rate is recalculated every partial_result_cache_check_frequency cache misses (default: every 200 misses). It is also recalculated when memory usage reaches partial_result_cache_max_mem_size.

Based on the current hit rate, one of three actions is taken:

When the memory limit is reached, the feedback mechanism is triggered for all operators that have PTRC enabled in the query.

Performance considerations

Two factors determine how much PTRC improves performance:

• Operator cost: the higher the cost of the cached operator, the larger the gain. For cheap operators, the improvement is minimal.

• Cache hit rate: the higher the hit rate, the larger the gain.

In TPC-H Q17, the cache hit rate is 96%. The following figures show the query and its execution plan with PTRC enabled, along with the performance improvement.

PTRC is designed for queries with complex operators that depend on correlated parameters. Operators for which PTRC is suitable include correlated subqueries and nested loop joins (inner joins, outer joins, semi joins, and anti joins).