Kingbase（PostgreSQL系）plan_cache_mode对谓词表达式合并的影响

by

plan_cache_mode 决定优化器在 Prepared Statement 场景下，是“针对当前参数值重新生成计划”，还是“使用一个不依赖具体参数值的通用计划”。由于优化器掌握的信息不同，因此可能产生完全不同的执行计划。

像WHERE $1::timestamptz IS NULL OR “timestamp” > $1这样的SQL条件较长见,用于分页，在 PostgreSQL 中，我们也可以使用单个预处理语句来实现同样的功能。如下

SELECT *
     FROM orders
     WHERE $1::timestamptz IS NULL OR "timestamp" > $1
     ORDER BY "timestamp" ASC
     LIMIT ${PAGE_SIZE}

注：当里这里没有考虑多个订单共享相同的时间戳，可能导致页面边界出现跳跃，那样分页方式应该有一个唯一的键。

这里如果$1为 NULL，那么null is null这个条件永远成立，就起不到过滤条件了。只是返回时间排序的前几行， 同时如果使用通用计划会导致执行计划对于某些参数值而言存在问题。看到Franck有提到该问题下面在Kingbase测试，同样适用于基于postgresql的其他库。

测试用例

create table orders (
  order_id text primary key,
  "timestamp" timestamptz not null
);

create index idx_orders_timestamp
  on orders("timestamp");

insert into orders
select
    'ORD-'||g,
    '2025-01-01'::timestamptz + g * interval '1 minute'
from generate_series(1, 5000000) as g;

analyze orders;

prepare getorders(timestamptz, int) as
select * from orders
     where $1::timestamptz is null or "timestamp" > $1
     order by "timestamp" asc limit $2
;

默认plan_cache_mode调用

kingbase=# explain (analyze, buffers, settings, costs off)
kingbase-#  execute getorders(date'2026-01-01'::timestamptz, 10);
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Limit (actual time=0.017..0.020 rows=10 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using idx_orders_timestamp on orders (actual time=0.013..0.015 rows=10 loops=1)
         Index Cond: ("timestamp" > '2026-01-01 00:00:00+08'::timestamp with time zone)
         Buffers: shared hit=4
 Planning Time: 0.421 ms
 Execution Time: 0.046 ms
(7 rows)

注意这里把$1::timestamptz is null or “timestamp” > $1 合并，并使用索引和Index Cond 条件。

切换到force_generic_plan 调用

kingbase=# set plan_cache_mode to force_generic_plan;
SET
kingbase=# explain (analyze, buffers, settings, costs off)
kingbase-#  execute getorders(date'2026-01-01'::timestamptz, 10);
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Limit (actual time=88.727..88.731 rows=10 loops=1)
   Buffers: shared hit=4815
   ->  Index Scan using idx_orders_timestamp on orders (actual time=88.724..88.726 rows=10 loops=1)
         Filter: (($1 IS NULL) OR ("timestamp" > $1))
         Rows Removed by Filter: 525600
         Buffers: shared hit=4815
 Settings: plan_cache_mode = 'force_generic_plan'
 Planning Time: 0.177 ms
 Execution Time: 88.758 ms
(9 rows)

仍然是索引扫描，但没有用于Index Cond缩小扫描范围，由于通用计划必须适用于所有可能的时间戳值$1，PostgreSQL 无法将谓词转换为Index Cond基于“timestamp”的查询。 产生了525600个无效读。

虽然默认设置plan_cache_mode = 'auto'通常效果不错，但它依赖于一种启发式方法：PostgreSQL 会使用自定义执行计划多次执行语句，然后将通用执行计划的预估成本与自定义执行计划的平均预估成本进行比较。该决策基于预估成本而非实际执行时间，这意味着即使自定义执行计划在特定工作负载下性能更佳，它有时也可能会选择通用执行计划。

优化方法，改写SQL

kingbase=# reset plan_cache_mode;
RESET
kingbase=# show plan_cache_mode
kingbase-# ;
 plan_cache_mode
-----------------
 auto
(1 row)

kingbase=# prepare getorders_better(timestamptz, int) as
kingbase-# select * from orders
kingbase-#      where timestamp > coalesce($1, '-infinity'::timestamptz)
kingbase-#      order by "timestamp" asc limit $2
kingbase-# ;
PREPARE
kingbase=# explain execute getorders_better(null::timestamptz, 10);
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.78 rows=10 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..174702.48 rows=5000003 width=19)
         Index Cond: ("timestamp" > '-infinity'::timestamp with time zone)
(3 rows)

kingbase=# explain execute getorders_better(date'2026-01-01'::timestamptz, 10);
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.78 rows=10 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..156591.31 rows=4481593 width=19)
         Index Cond: ("timestamp" > '2026-01-01 00:00:00+08'::timestamp with time zone)
(3 rows)

kingbase=# set plan_cache_mode to force_generic_plan;
SET
kingbase=# explain execute getorders_better(null::timestamptz, 10);
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..5824.21 rows=166667 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..58238.12 rows=1666668 width=19)
         Index Cond: ("timestamp" > COALESCE($1, '-infinity'::timestamp with time zone))
(3 rows)

kingbase=# explain execute getorders_better(date'2026-01-01'::timestamptz, 10);
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..5824.21 rows=166667 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..58238.12 rows=1666668 width=19)
         Index Cond: ("timestamp" > COALESCE($1, '-infinity'::timestamp with time zone))
(3 rows)

使用coalesce函数把2个条件合并成1个，即使使用空参数，执行计划始终使用Index Cond。 注意表时是否有-infinity值。

硬编码方式

kingbase=# explain (analyze, buffers, costs off)
kingbase-# with param as ( select
kingbase(#  date'2026-01-01'::timestamptz as p1
kingbase(# )
kingbase-# select * from orders, param
kingbase-#      where p1::timestamptz is null or "timestamp" > p1
kingbase-#      order by "timestamp" asc limit 10
kingbase-# ;
                                                                           QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------
------------------
 Limit (actual time=142.830..142.836 rows=10 loops=1)
   Buffers: shared hit=4815
   ->  Index Scan using idx_orders_timestamp on orders (actual time=142.828..142.832 rows=10 loops=1)
         Filter: ((('2026-01-01 00:00:00'::date)::timestamp with time zone IS NULL) OR ("timestamp" > ('2026-01-01 00:00:00'::date)::timestamp
 with time zone))
         Rows Removed by Filter: 525600
         Buffers: shared hit=4815
 Planning Time: 0.150 ms
 Execution Time: 142.875 ms
(8 rows)

将参数包含在 WITH 子句的公共表表达式中，使用硬编码值构建自定义查询，则可能会遇到同样的问题。由于((('2026-01-01'::date)::timestamp with time zone IS NULL)该条件可证明为假，理论上可以将 OR 表达式简化为单个谓词。然而，PostgreSQL 目前并未执行此转换。

物化CTE

如果 WITH 子句被物化而不是内联，情况会更糟，因为谓词甚至不是带有硬编码值的谓词Filter，Index Scan而是参数化的谓词Join Filter：

kingbase=#  explain (analyze, buffers, costs off)
kingbase-# with param as materialized ( select
kingbase(#  date'2026-01-01'::timestamptz as p1
kingbase(# )
kingbase-# select * from orders, param
kingbase-#      where p1::timestamptz is null or "timestamp" > p1
kingbase-#      order by "timestamp" asc limit 10
kingbase-# ;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Limit (actual time=272.672..272.681 rows=10 loops=1)
   Buffers: shared hit=4815
   CTE param
     ->  Result (actual time=0.002..0.002 rows=1 loops=1)
   ->  Nested Loop (actual time=272.671..272.677 rows=10 loops=1)
         Join Filter: ((param.p1 < orders."timestamp") OR (param.p1 IS NULL))
         Rows Removed by Join Filter: 525600
         Buffers: shared hit=4815
         ->  Index Scan using idx_orders_timestamp on orders (actual time=0.019..80.207 rows=525610 loops=1)
               Buffers: shared hit=4815
         ->  CTE Scan on param (actual time=0.000..0.000 rows=1 loops=525610)
 Planning Time: 0.175 ms
 Execution Time: 272.728 ms
(13 rows)

这种情况与使用通用执行计划执行预处理语句时的情况类似,更改计划缓存模式并无帮助.

小结

对于参数值可能导致截然不同的最优执行计划的工作负载，依赖默认设置plan_cache_mode = auto可能并非总能达到预期效果。在这种情况下，强制使用自定义执行计划plan_cache_mode = force_custom_plan可以确保优化器在每次执行时都考虑实际的参数值。这个问题并非仅限于通用计划。即使在计划时通过单行 CTE 已知值，PostgreSQL 也不会将 OR 表达式简化为可索引的谓词。

Leave a Comment