Partial Result Cache (PTRC)

Scenarios

Given the performance gains from result set caching, TaurusDB has introduced Partial Result Cache (PTRC). Unlike traditional result set caches, PTRC caches intermediate result sets produced by specific operators in a query, enabling finer-grained caching that speeds up operator execution.

Principles

PTRC speeds up operators that repeatedly run the same scan or calculation with different input parameter values. For example:

• Nested loop joins: For each row scanned in the outer table, the inner table is scanned once based on the join condition.
• Correlated subqueries: A subquery runs once for every row returned by the outer query.

PTRC caches result sets as key-value pairs, with parameter values as keys and the corresponding calculation results as values. When an operation runs again, the system first checks the cache for the current parameter value. If a match is found, it returns the cached result right away, skipping the calculation. Otherwise, it calculates the result and saves it in the cache for future use. The following uses a correlated subquery as an example to describe how PTRC works.

Figure 1 How PTRC works

Control Mechanism and Execution Policy

According to the PTRC execution process, frequently using PTRC does not mean better. The effect depends on cache hit rates. If the hit ratio is low, PTRC will cause extra overhead from frequent cache checks and increased memory usage. To address this, TaurusDB introduced some PTRC parameters to control its behavior during both optimization and execution phases.

You are advised to retain the default values of the PTRC parameters. These parameters cannot be modified on the management console. To modify them, submit a service ticket.

The parameters listed below will be explained later:

• rds_partial_result_cache_cost_threshold
• rds_partial_result_cache_max_mem_size
• rds_partial_result_cache_min_hit_ratio
• rds_partial_result_cache_hit_ratio_frequency

Prerequisites

PTRC is only available when the kernel version of your TaurusDB instance is 2.0.63.250300 or later. For details about how to check the kernel version, see How Can I Check the Version of a TaurusDB Instance?

Configuring PTRC

You can configure PTRC using a parameter or hint.

• Using a parameter

  Parameter	Level	Description
  optimizer_switch : partial_result_cache	Global, Session	Controls whether to enable PTRC. Value: ON (default value): PTRC is enabled. OFF: PTRC is disabled.

• Using a hint

  You can use hints to control PTRC more precisely at the statement level. The hint in a statement overrides the optimizer_switch system variable. Even if the global or session switch is disabled, PTRC can be enabled for a specific query using a hint.

Examples

1. Use any of the following methods to enable PTRC:
   • Run the SET command to set the option.

     SET optimizer_switch='partial_result_cache=on';
     Query OK, 0 rows affected (0.00 sec)

     SET optimizer_switch='partial_result_cache=off';
     Query OK, 0 rows affected (0.00 sec)

   • Use hints to set the option in SQL statements.

     EXPLAIN FORMAT=TREE SELECT /*+ set_var(optimizer_switch='partial_result_cache=on') */ t1.i AS a, (SELECT t2.i FROM t t2 WHERE t1.j = t2.j ORDER BY j DESC, i DESC LIMIT 1) AS b FROM t t1;

     EXPLAIN FORMAT=TREE SELECT /*+ set_var(optimizer_switch='partial_result_cache=off') */ t1.i AS a, (SELECT t2.i FROM t t2 WHERE t1.j = t2.j ORDER BY j DESC, i DESC LIMIT 1) AS b FROM t t1;

   • Add hints to SQL statements to forcibly enable PTRC in nested loop joins.

     EXPLAIN FORMAT=TREE SELECT t1.*, (SELECT /*+ PRC_JOIN() */ count(*) FROM (t t2 JOIN t t3) LEFT JOIN (t t4 LEFT JOIN (t t5 JOIN t t6) ON t4.a>t5.a-t1.a) ON t3.a>t2.a-t1.a) FROM t t1;

     EXPLAIN FORMAT=TREE SELECT t1.*, (SELECT /*+ PRC_JOIN(t1,t2,t3) */ count(*) FROM (t t2 JOIN t t3) LEFT JOIN (t t4 LEFT JOIN (t t5 JOIN t t6) ON t4.a>t5.a-t1.a) ON t3.a>t2.a-t1.a) FROM t t1;

     The current statement contains multiple nested loop join operators. If the hint /*+ PRC_JOIN() */ is used, all nested loop join operators will be forced to use PTRC. If the hint /*+ PRC_JOIN(t1,t2,t3) */ is used, only the nested loop join operators that contain the t1, t2, and t3 tables will be forced to use PTRC.

   • Add hints to SQL statements to forcibly disable PTRC in nested loop joins.

     EXPLAIN FORMAT=TREE SELECT t1.*, (SELECT /*+ NO_PRC_JOIN() */ count(*) FROM (t t2 JOIN t t3) LEFT JOIN (t t4 LEFT JOIN (t t5 JOIN t t6) ON t4.a>t5.a-t1.a) ON t3.a>t2.a-t1.a) FROM t t1;

     EXPLAIN FORMAT=TREE SELECT t1.*, (SELECT /*+ NO_PRC_JOIN(t1,t2,t3) */ count(*) FROM (t t2 JOIN t t3) LEFT JOIN (t t4 LEFT JOIN (t t5 JOIN t t6) ON t4.a>t5.a-t1.a) ON t3.a>t2.a-t1.a) FROM t t1;

     The current statement contains multiple nested loop join operators. If the hint /*+ NO_PRC_JOIN() */ is used, PTRC will be disabled for all nested loop join operators. If the hint /*+ NO_PRC_JOIN(t1,t2,t3) */ is used, PTRC will only be disabled for the nested loop join operators that contain the t1, t2, and t3 tables.

   • Add hints to SQL statements to forcibly enable PTRC in correlated subqueries.

     EXPLAIN FORMAT=TREE SELECT t1.i AS a, (SELECT /*+PRC_SUBQUERY() */ t2.i FROM t t2 WHERE t1.j = t2.j ORDER BY j DESC, i DESC LIMIT 1) AS b FROM t t1;

   • Add hints to SQL statements to forcibly disable PTRC in correlated subqueries.

     EXPLAIN FORMAT=TREE SELECT t1.i AS a, (SELECT /*+NO_PRC_SUBQUERY() */ t2.i FROM t t2 WHERE t1.j = t2.j ORDER BY j DESC, i DESC LIMIT 1) AS b FROM t t1;

2. Check the control effect.

   Run EXPLAIN ANALYZE or EXPLAIN FORMAT=TREE to check whether the optimization is applied. If the execution plan contains keyword Result cache, the optimization is applied.

   The detailed procedure is as follows:

   1. Prepare data.

      CREATE TABLE t (i INTEGER PRIMARY KEY AUTO_INCREMENT, j INTEGER, KEY(j));

      INSERT INTO t VALUES (NULL, NULL);
      INSERT INTO t SELECT NULL, NULL FROM t;
      INSERT INTO t SELECT NULL, NULL FROM t;
      INSERT INTO t SELECT NULL, NULL FROM t;
      INSERT INTO t SELECT NULL, NULL FROM t;
      INSERT INTO t SELECT NULL, NULL FROM t;
      INSERT INTO t SELECT NULL, NULL FROM t;
      INSERT INTO t SELECT NULL, NULL FROM t;
      INSERT INTO t SELECT NULL, NULL FROM t;
      INSERT INTO t SELECT NULL, NULL FROM t;
      INSERT INTO t SELECT NULL, NULL FROM t;
      INSERT INTO t SELECT NULL, NULL FROM t;
      INSERT INTO t SELECT NULL, NULL FROM t;

      ANALYZE TABLE t;

   2. Disable the feature and run the following SQL statements. The optimizer chooses the default execution plan.

      SET optimizer_switch='partial_result_cache=off';
      Query OK, 0 rows affected (0.00 sec)

      EXPLAIN FORMAT=TREE SELECT t1.i AS a, (SELECT t2.i FROM t t2 WHERE t1.j = t2.j ORDER BY j DESC, i DESC LIMIT 1) AS b FROM t t1\G
      *************************** 1. row ***************************
      EXPLAIN: -> Index scan on t1 using j  (cost=411.60 rows=4096)
      -> Select #2 (subquery in projection; dependent)
          -> Limit: 1 row(s)  (cost=410.97 rows=1)
              -> Index lookup on t2 using j (j=t1.j; iterate backwards)  (cost=410.97 rows=4096)

   3. Enable the feature and run the following SQL statements. If the execution plan contains keyword Result cache, the optimization is applied. The cache keys part shows the columns used to construct the cache key.

      SET optimizer_switch='partial_result_cache=on';
      Query OK, 0 rows affected (0.00 sec)

      EXPLAIN FORMAT=TREE SELECT t1.i AS a, (SELECT t2.i FROM t t2 WHERE t1.j = t2.j ORDER BY j DESC, i DESC LIMIT 1) AS b FROM t t1\G
      *************************** 1. row ***************************
      EXPLAIN: -> Index scan on t1 using j  (cost=411.60 rows=4096)
      -> Select #2 (subquery in projection; dependent)
          -> Result cache : cache keys(t1.j)
              -> Limit: 1 row(s)  (cost=410.97 rows=1)
                  -> Index lookup on t2 using j (j=t1.j; iterate backwards)  (cost=410.97 rows=4096)

      When you run EXPLAIN ANALYZE, Result cache not only shows the columns used as cache keys but also provides detailed statistics in the execution phase.

      EXPLAIN ANALYZE SELECT t1.i AS a, (SELECT t2.i FROM t t2 WHERE t1.j = t2.j ORDER BY j DESC, i DESC LIMIT 1) AS b FROM t t1\G
      *************************** 1. row ***************************
      EXPLAIN: -> Index scan on t1 using j  (cost=411.60 rows=4096) (actual time=0.026..0.651 rows=4096 loops=1)
      -> Select #2 (subquery in projection; dependent)
          -> Result cache:cache keys(t1.j) (Cache Hits:4095, Cache Misses:1, Cache Evictions:0, Cache Overflows:0, Memory Usage:40960 )  (actual time=0.000..0.000 rows=0 loops=4096)
              -> Limit:1 row(s)  (cost=410.97 rows=1) (actual time=0.000..0.000 rows=0 loops=4096)
                  -> Index lookup on t2 using j (j=t1.j; iterate backwards)  (cost=410.97 rows=4096) (actual time=0.000..0.000 rows=0 loops=4096)

      Table 1 Result cache metrics

      Metric	Description
      Cache Hits	Number of cache hits.
      Cache Misses	Number of cache misses.
      Cache Evictions	Number of records that are evicted using LRU.
      Cache Overflows	Number of memory overflows.
      Memory Usage	Memory used by the current query.

Incompatibility After PTRC Is Enabled

When PTRC is enabled, cached rows under a single key may be returned in a different order than originally processed. For queries aggregating DOUBLE-type data, especially values near the type's limits, this change in row order can cause intermediate floating-point sums to overflow, leading to different final results.

Performance Test

We tested TPC-H Q17 across different data scales to compare performance before and after enabling PTRC. The results showed that Q17 performance was significantly improved after PTRC was enabled.

For example, at SF100, the Q17 execution plan includes a correlated subquery that uses PTRC. Execution statistics show a 96.7% cache hit rate (formula: Cache hits/(Cache hits + Cache misses)). PTRC is essentially a caching structure, so higher hit rates yield greater performance improvements.

EXPLAIN ANALYZE SELECT sum(l_extendedprice) / 7.0 AS avg_yearly FROM LINEITEM, PART WHERE p_PARTkey = l_PARTkey AND p_brand = 'Brand#14' AND p_container = 'SM PKG' AND l_quantity < ( SELECT /*+ PRC_SUBQUERY() */ 0.2 * avg(l_quantity) FROM LINEITEM WHERE l_PARTkey = p_PARTkey ) LIMIT 1;

| EXPLAIN |
| -> Limit: 1 row(s)  (actual time=13338.942..13338.942 rows=1 loops=1)
    -> Aggregate: sum(lineitem.L_EXTENDEDPRICE)  (actual time=13338.941..13338.941 rows=1 loops=1)
        -> Nested loop inner join  (cost=7602572.34 rows=5448536) (actual time=0.369..13331.440 rows=54130 loops=1)
            -> Filter: ((part.P_BRAND = 'Brand#14') and (part.P_CONTAINER = 'SM PKG'))  (cost=2029081.97 rows=197905) (actual time=0.094..8830.363 rows=20004 loops=1)
                -> Table scan on PART  (cost=2029081.97 rows=19790487) (actual time=0.023..7105.163 rows=20000000 loops=1)
            -> Filter: (lineitem.L_QUANTITY < (select #2))  (cost=25.41 rows=28) (actual time=0.198..0.225 rows=3 loops=20004)
                -> Index lookup on LINEITEM using LINEITEM_FK2 (L_PARTKEY=part.P_PARTKEY)  (cost=25.41 rows=28) (actual time=0.117..0.124 rows=30 loops=20004)
                -> Select #2 (subquery in condition; dependent)
                    -> Result cache : cache keys(PART.P_PARTKEY) (Cache Hits: 580430, Cache Misses: 20004, Cache Evictions: 0, Cache Overflows: 0, Memory Usage: 4218760 )  (actual time=0.003..0.003 rows=1 loops=600434)
                        -> Aggregate: avg(lineitem.L_QUANTITY)  (actual time=0.003..0.003 rows=0 loops=600434)
                            -> Index lookup on LINEITEM using LINEITEM_FK2 (L_PARTKEY=part.P_PARTKEY)  (cost=28.16 rows=28) (actual time=0.002..0.002 rows=1 loops=600434)
 |

Additionally, if the associated operator has a high execution cost, PTRC can deliver even more significant performance gains.

Figure 2 Performance test results