PostgreSQL查询优化器为何生成全量连接及规避方法咨询

DevOps 小满

2026-6-21

PostgreSQL查询优化器生成全量交叉连接的问题排查与解决

问题背景

我编写了一个函数，逻辑是处理主表的选定记录，在另外两个关联表中插入数据（第二个表依赖第一个表的ID）：

从主表查询符合条件的记录详情
向第一个表插入数据并返回新ID
用这个ID向第二个表插入数据

但操作异常缓慢，改成SELECT测试后发现：当WHERE子句包含特定条件时，PostgreSQL优化器会生成全量交叉连接，导致性能暴跌。

环境与表结构

PostgreSQL版本：PostgreSQL 16.2 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 9.5.0, 64-bit

表定义：

DROP TABLE IF EXISTS c;
DROP TABLE IF EXISTS b;
DROP TABLE IF EXISTS a;
CREATE TABLE a (
  a_id UUID NOT NULL DEFAULT public.gen_random_uuid(), 
  file_id INT NOT NULL,
  ab TEXT NOT NULL,
  ac TEXT NOT NULL,
  name TEXT NOT NULL,
  PRIMARY KEY (a_id)
);
CREATE INDEX "file_id" on a using btree ("file_id");
CREATE TABLE b (
  b_id UUID NOT NULL DEFAULT public.gen_random_uuid(), 
  a_id UUID NOT NULL,
  ab TEXT NOT NULL,
  PRIMARY KEY (b_id),
  CONSTRAINT b_a_id_key FOREIGN KEY (a_id) REFERENCES a(a_id)
);
CREATE TABLE c (
  c_id UUID NOT NULL DEFAULT public.gen_random_uuid(), 
  b_id UUID NOT NULL,
  ac TEXT NOT NULL,
  PRIMARY KEY (c_id),
  CONSTRAINT c_b_id_key FOREIGN KEY (b_id) REFERENCES b(b_id)
);

表a以12条重复模式填充数据：

insert into a (file_id, ab, ac, name)
VALUES 
(14530, 'ab00000', 'ac00000', 'Huey'), 
(14531, 'ab00001', 'ac00001', 'Dewey'), 
(14532, 'ab00002', 'ac00002', 'Louie'), 
(14530, 'ab00003', 'ac00003', 'Scrooge'), 
(14531, 'ab00004', 'ac00004', 'Huey'), 
(14532, 'ab00005', 'ac00005', 'Dewey'), 
(14530, 'ab00006', 'ac00006', 'Louie'), 
(14531, 'ab00007', 'ac00007', 'Scrooge'), 
(14532, 'ab00008', 'ac00008', 'Huey'), 
(14530, 'ab00009', 'ac00009', 'Dewey'), 
(14531, 'ab00010', 'ac00010', 'Louie'), 
(14532, 'ab00011', 'ac00011', 'Scrooge'), 
(14530, 'ab00012', 'ac00012', 'Huey'), 
(14531, 'ab00013', 'ac00013', 'Dewey'), 
...

测试案例与现象

慢查询（执行时间~17秒）

EXPLAIN (ANALYZE, FORMAT JSON)
WITH upd AS 
(
    SELECT
        a_id, ab, ac
    FROM
        a
    WHERE
        file_id = 14532
    AND 
        SUBSTRING(cast (name AS TEXT),1,1)='H'
    AND 
        SUBSTRING(cast (ac AS TEXT),1,1)='a'
),
inserted_b AS 
(
    SELECT
        a_id, ab
    FROM
        upd
),
inserted_c AS
(
    SELECT
        upd.a_id, ac
    FROM
        upd
    JOIN
        inserted_b
    ON
        inserted_b.a_id = upd.a_id
)
SELECT * FROM inserted_c

执行计划显示：初始upd查询返回8333行，但优化器生成了8333×8333的全量交叉连接后再筛选，导致性能极差。

优化后快查询（执行时间~49ms）

将CTE逻辑内联，重复过滤条件：

EXPLAIN (ANALYZE, FORMAT JSON)
WITH 
inserted_b AS 
(
    SELECT
        a_id, ab
    FROM
        a
    WHERE
        file_id = 14532
    AND 
        SUBSTRING(cast (name AS TEXT),1,1)='H'
    AND 
        SUBSTRING(cast (ac AS TEXT),1,1)='a'
),
inserted_c AS
(
    SELECT
        a.a_id, ac
    FROM
        a
    JOIN
        inserted_b
    ON
        inserted_b.a_id = a.a_id
    WHERE
        file_id = 14532
    AND 
        SUBSTRING(cast (name AS TEXT),1,1)='H'
    AND 
        SUBSTRING(cast (ac AS TEXT),1,1)='a'
)
SELECT * FROM inserted_c

其他现象

移除SUBSTRING(cast (ac AS TEXT),1,1)='a'条件后，查询恢复正常
将SUBSTRING(cast (name AS TEXT),1,1)='H'改为cast (name AS TEXT)='Huey'后，查询也会变快

问题解答

这是PostgreSQL优化器的问题吗？

是的，这属于PostgreSQL优化器在处理CTE关联时的统计信息预估偏差或逻辑推导不足。当WHERE子句包含SUBSTRING这类函数条件时，优化器可能无法准确判断CTE之间的依赖关系（比如inserted_b完全是upd的子集，且a_id是唯一主键），错误地选择全量交叉连接后再筛选的执行策略。

避免全量连接的方法

移除冗余关联逻辑
你的慢查询中inserted_b是upd的子集，inserted_c关联两者完全没必要，直接从upd中取字段即可：

EXPLAIN (ANALYZE, FORMAT JSON)
WITH upd AS 
(
    SELECT
        a_id, ab, ac
    FROM
        a
    WHERE
        file_id = 14532
    AND 
        SUBSTRING(name,1,1)='H'
    AND 
        SUBSTRING(ac,1,1)='a'
)
SELECT a_id, ac FROM upd;

注：name和ac本身就是TEXT类型，不需要额外cast

强制CTE内联
PostgreSQL 12+中默认CTE是优化栅栏，可通过NOT MATERIALIZED让优化器将CTE逻辑内联到主查询，消除不必要的连接：

EXPLAIN (ANALYZE, FORMAT JSON)
WITH upd AS NOT MATERIALIZED
(
    SELECT
        a_id, ab, ac
    FROM
        a
    WHERE
        file_id = 14532
    AND 
        SUBSTRING(name,1,1)='H'
    AND 
        SUBSTRING(ac,1,1)='a'
),
inserted_b AS NOT MATERIALIZED
(
    SELECT
        a_id, ab
    FROM
        upd
),
inserted_c AS NOT MATERIALIZED
(
    SELECT
        upd.a_id, ac
    FROM
        upd
    JOIN
        inserted_b
    ON
        inserted_b.a_id = upd.a_id
)
SELECT * FROM inserted_c;

优化过滤条件写法
用前缀匹配替代SUBSTRING，优化器更容易判断过滤后的行数，还能利用前缀索引：
- 用name LIKE 'H%'替代SUBSTRING(name,1,1)='H'
- 用ac LIKE 'a%'替代SUBSTRING(ac,1,1)='a'

创建函数索引
如果必须用SUBSTRING做过滤，创建对应的函数索引帮助优化器准确预估行数：

CREATE INDEX idx_a_name_first_char ON a (SUBSTRING(name, 1, 1)) WHERE file_id = 14532;
CREATE INDEX idx_a_ac_first_char ON a (SUBSTRING(ac, 1, 1)) WHERE file_id = 14532;

更新统计信息
执行ANALYZE a;刷新表a的统计信息，让优化器能更准确判断过滤后的结果集大小，避免错误选择执行策略。

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

PostgreSQL查询优化器为何生成全量连接及规避方法咨询

问题背景 #

环境与表结构 #

测试案例与现象 #

慢查询（执行时间~17秒） #

优化后快查询（执行时间~49ms） #

其他现象 #

问题解答 #

这是PostgreSQL优化器的问题吗？ #

避免全量连接的方法 #