PostgreSQL基于常量表行值传参查询慢200倍如何优化

安全阿岳

2026-9-3

问题描述

现有统计新增代币铸造量的查询，存在严重性能不一致问题：

当start_time公共表表达式（CTE）中硬编码时间常量时，查询仅需0.2秒即可完成
当start_time从processed.last_update配置表动态读取token_supply对应的最后更新时间时，查询耗时长达40秒，性能相差200倍

查询语句如下：

-- 快速版本：硬编码时间
with start_time as (
  select '2022-06-02T17:45:43Z':: timestamp with time zone as time
)

-- 慢版本：动态读取时间
-- with start_time as (
--   select last_update as time
--   from processed.last_update
--   where table_name = 'token_supply'
-- )

select  ident as ma_id
    ,   sum(quantity) as quantity
    ,   sum(quantity) filter (where quantity > 0) as quantity_minted
from public.ma_tx_mint
where exists (
  select id
  from public.tx
  where tx.id = ma_tx_mint.tx_id
    and exists (
      select id
      from public.block
      cross join start_time
      where block.id = tx.block_id
        and block.time >= start_time.time
    )
)
group by ident

业务需求为查询指定时间之后新增的记录，需要实现从其他表动态获取时间阈值的同时，保持和硬编码场景一致的执行逻辑与查询性能。

环境信息

数据库版本：PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit

相关表结构

create table public.ma_tx_mint (
    id bigint
  , quantity numeric
  , tx_id bigint
  , ident bigint
  , primary key(id)
);

create table public.tx (
    id bigint
  , block_id bigint
  , primary key(id)
);

create table public.block (
    id bigint
  , time timestamp with time zone
  , primary key(id)
);

create table processed.last_update (
    table_name varchar
  , last_update timestamp with time zone
  , primary key(table_name)
);

问题根因

PostgreSQL 13版本中，默认情况下如果CTE仅被外层引用一次，优化器会尝试将CTE内联到主查询中生成执行计划。当CTE返回的是常量值时，优化器可以在计划生成阶段直接拿到具体的时间阈值，结合表统计信息准确估算block.time >= 阈值过滤后的返回行数，选择「按时间过滤block→关联匹配tx→关联匹配ma_tx_mint→聚合计算」的高效索引路径。
当CTE是从表中动态读值时，优化器无法在计划生成阶段提前拿到具体的时间阈值，只能基于通用选择率估算行数，最终错误选择了以ma_tx_mint为驱动表的全表扫描+嵌套循环关联路径，导致性能骤降。

解决方案

按优先级从高到低可选择以下方案：

方案1：给CTE增加MATERIALIZED提示（改动最小，适配PG12及以上版本）
强制数据库先执行CTE查询拿到具体的时间值，再基于这个确定的常量生成外层查询的执行计划，和硬编码场景的执行逻辑完全一致，修改方式仅需调整CTE定义：
```
with start_time as MATERIALIZED (
  select last_update as time
  from processed.last_update
  where table_name = 'token_supply'
)
-- 后续主查询逻辑保持不变
```

方案2：先查询时间值再传入主查询
可以在业务代码或者PL/pgSQL存储过程中，先单独执行查询拿到last_update的具体值，再把这个值作为参数传入主查询，避免优化器在未知参数值的情况下生成执行计划。PL/pgSQL示例：

create or replace function query_ma_mint_delta()
returns table (ma_id bigint, quantity numeric, quantity_minted numeric) as $$
declare
  v_start_time timestamptz;
begin
  -- 先拿到确定的时间阈值
  select last_update into v_start_time
  from processed.last_update
  where table_name = 'token_supply';
  
  return query
  select  ident as ma_id
      ,   sum(quantity) as quantity
      ,   sum(quantity) filter (where quantity > 0) as quantity_minted
  from public.ma_tx_mint
  where exists (
    select id
    from public.tx
    where tx.id = ma_tx_mint.tx_id
      and exists (
        select id
        from public.block
        where block.id = tx.block_id
          and block.time >= v_start_time
      )
  )
  group by ident;
end;
$$ language plpgsql stable;

方案3：补充缺失的二级索引，从根本上稳定执行计划
现有表仅创建了主键索引，过滤字段、关联字段均无二级索引，硬编码场景的高性能依赖优化器选对驱动顺序，一旦统计信息波动或者阈值变化很容易出现性能跳水。补充以下索引后，无论执行计划如何选择都不会出现百倍性能差：
```
-- 支持block表按时间快速过滤
create index if not exists idx_block_time on public.block(time);
-- 支持tx表按block_id快速关联
create index if not exists idx_tx_block_id on public.tx(block_id);
-- 支持ma_tx_mint表按tx_id快速关联
create index if not exists idx_ma_tx_mint_tx_id on public.ma_tx_mint(tx_id);
```

内容的提问来源于stack exchange，提问作者adjuric

PostgreSQL基于常量表行值传参查询慢200倍如何优化

环境信息 #

相关表结构 #

环境信息

相关表结构