PHP小码农

Appearance

查询执行流程（Parser / Rewrite / Planner / Executor） ​

一条 SQL 从客户端发出到返回结果，PostgreSQL 需要经过解析（Parser）→ 重写（Rewrite）→ 计划（Planner / Optimizer）→ 执行（Executor）四个阶段。本节把每个阶段的输入、输出与核心工作讲清楚，并结合 EXPLAIN、pg_stat_statements 与日志参数，展示如何观察与诊断每个环节的耗时与产物。

前置知识

阅读本节前，建议先了解：磁盘文件结构（PGDATA / relfilenode）

基础概念 ​

PostgreSQL 采用单条 SQL 单阶段流转的处理模型：每个后端进程独立完成从解析到执行的全过程，没有共享的执行计划缓存（plan cache）供所有会话共用。

术语英文名说明
解析Parse把 SQL 文本转成语法树（parse tree），检查语法
分析/语义检查Analyze查系统目录，解析表名、列名、函数，生成查询树（Query）
重写Rewrite展开视图、规则（RULE）、行级安全策略，得到最终查询树
计划Plan / Optimize基于统计信息枚举访问路径，估算成本，选出最优计划树
执行Execute按计划树驱动算子（节点）从存储层取数、计算、返回结果
计划节点Plan Node计划树上的一个算子，如 Seq Scan、Hash Join
成本估算Cost Estimation用 seq_page_cost、random_page_cost 等参数估算 I/O 与 CPU 代价
选择率Selectivity谓词过滤掉数据的比例，统计信息是估算基础
通用计划Generic Plan预处理语句（PREPARE）在第 6 次执行后可能启用的固定计划
自定义计划Custom Plan每次执行时按实际参数值重新生成的计划

解析 ≠ 计划 ≠ 执行，成本完全不在一个量级

一次简单的主键点查，解析与计划可能只花几十微秒，执行是微秒级；但一条十几个表关联的复杂分析查询，计划阶段可能耗时数百毫秒，远超执行前几个算子的时间。「SQL 变慢」时首先要知道慢在哪一阶段，否则优化方向会完全跑偏（解析慢要查 SQL 文本与语法，计划慢要查统计信息与 join_collapse_limit，执行慢要查索引与数据量）。

四个阶段的数据结构演进 ​

text
客户端发送 SQL 文本
        │
        ▼
┌──────────────────┐
│ 1. Parser        │  原始 SQL 字符串 → 语法树（RawStmt / raw parse tree）
│    语法解析       │  只做语法检查，不查任何系统表
└──────────────────┘
        │
        ▼
┌──────────────────┐
│ 2. Analyzer      │  语法树 → 查询树（Query）
│    语义分析       │  查系统目录，把「表名」解析成 OID，检查列/函数是否存在
│                  │  同时做类型推导与隐式类型转换
└──────────────────┘
        │
        ▼
┌──────────────────┐
│ 3. Rewriter      │  查询树 → 展开后的查询树
│    规则重写       │  展开视图、RULE、行级安全策略（RLS）
└──────────────────┘
        │
        ▼
┌──────────────────┐
│ 4. Planner       │  查询树 → 计划树（PlannedStmt，含 Plan tree）
│    优化器         │  枚举访问路径与连接顺序，用统计信息估算成本，取最低者
└──────────────────┘
        │
        ▼
┌──────────────────┐
│ 5. Executor      │  计划树 → 结果集
│    执行器         │  拉取模型（pull-based）：上层节点向上要一行，向下取一行
└──────────────────┘
        │
        ▼
返回结果给客户端（Simple Query 一次性返回；Extended Query 走 Portal 分批）

核心机制 ​

阶段 1~2：解析与语义分析 ​

解析（Parser） 完全由语法决定，使用 gram.y 生成的语法分析器（Bison）。它把 SELECT * FROM t WHERE id = 1 转成一棵语法树，并识别出关键字、标识符、常量、操作符。这一步不访问任何系统表，所以 SELECT * FROM 不存在的表 能通过解析，却会在下一阶段失败。

sql
-- 语法错误：解析阶段就被拒绝
SELECT * FORM t_user;
text
ERROR:  syntax error at or near "FORM"
LINE 1: SELECT * FORM t_user;
                 ^
sql
-- 语义错误：语法正确，但语义分析阶段找不到表
SELECT * FROM t_not_exist;
text
ERROR:  relation "t_not_exist" does not exist
LINE 1: SELECT * FROM t_not_exist;
                      ^

分析（Analyzer / parse_analyze） 负责把名字翻译成 OID：表名 → pg_class.oid，列名 → pg_attribute.attnum，函数名 → pg_proc.oid。这一步的产物是查询树（Query），是纯内存结构。

阶段主要工作典型报错信息
解析（Parse）语法检查、关键字识别syntax error at or near ...
分析（Analyze）对象存在性、列存在性、类型匹配、权限检查relation ... does not exist、column ... does not exist、permission denied for table

类型推导也发生在这里，这就是隐式类型转换的由来：

sql
-- 字面量 '1' 是 unknown 类型，会被推导为 int4 以匹配 id 列的类型
SELECT * FROM t_user WHERE id = '1';

-- 但如果两侧都是「明确类型」，就会按操作符规则决定是否转换
SELECT * FROM t_user WHERE id = 1.0;   -- numeric，id 会被隐式提升，索引可能失效

阶段 3：规则重写 ​

重写（Rewrite）阶段的唯一职责是把查询树「展开」成不含视图、规则与安全策略的等价形式。

三种典型展开：

sql
-- ① 视图展开：视图名会被替换成其定义
CREATE VIEW v_active_user AS
  SELECT id, name FROM t_user WHERE status = 'active';

-- 实际上等价于
SELECT id, name FROM t_user WHERE status = 'active';
sql
-- ② 行级安全策略（Row-Level Security）注入
-- 开启 RLS 后，每个查询都会被自动加上 USING 谓词
ALTER TABLE t_user ENABLE ROW LEVEL SECURITY;

CREATE POLICY p_self ON t_user
  USING (owner_id = current_setting('app.user_id')::int);

-- 业务写的 SELECT * FROM t_user 会被重写为：
-- SELECT * FROM t_user WHERE owner_id = current_setting('app.user_id')::int;
sql
-- ③ 规则（RULE）触发：INSERT 到视图上时，会被改写成对基表的操作
-- 这也是 CREATE RULE ... DO INSTEAD 的底层机制

重写阶段最常见的性能陷阱：视图套视图

视图本身不存数据，每次查询都要展开。若视图 A 引用视图 B，视图 B 又引用视图 C，嵌套多层后重写产物会急剧膨胀，且每个视图独立展开导致父查询的 WHERE 条件无法有效下推（除非满足简单视图的可上拉条件）。诊断方法是看 EXPLAIN 输出里的子查询层级，或对可疑查询手工改写成扁平 SQL 对比耗时。

阶段 4：计划（Planner / Optimizer） ​

计划器是整个流程中最复杂、最耗 CPU 的阶段。它的工作可以概括为三步：

（1）枚举候选路径

对每个基表，先枚举可用的访问路径（Access Path）：

访问路径触发条件EXPLAIN 显示
顺序扫描无可用索引或索引选择性差Seq Scan
索引扫描有匹配的 B-tree 索引Index Scan / Index Only Scan
位图索引扫描条件返回较多行、可批量定位页面Bitmap Heap Scan + Bitmap Index Scan
并行顺序扫描表足够大且 max_parallel_workers_per_gather > 0Gather + Parallel Seq Scan

（2）估算成本

成本由三部分构成，单位是「页」的抽象代价：

text
总成本 = 启动成本（startup_cost） + 运行成本（run_cost）
运行成本 ≈ 页读取成本 + CPU 元组处理成本 + 操作符自身成本

关键的成本参数：

参数默认值含义
seq_page_cost1.0顺序读一个页的代价（基准）
random_page_cost4.0随机读一个页的代价（SSD 上建议 1.1~1.5）
cpu_tuple_cost0.01处理一个元组的 CPU 代价
cpu_index_tuple_cost0.005处理一个索引条目的 CPU 代价
cpu_operator_cost0.0025执行一次操作符/函数的 CPU 代价
effective_cache_size4GB优化器对「OS 缓存 + 共享缓冲」总量的估计

（3）选择代价最低的计划

计划器会做连接顺序搜索（join order search）。表的数量决定搜索空间：

  • ≤ 12 个表（geqo_threshold 默认值）时，用动态规划穷举；
  • 超过 geqo_threshold 时，退化为遗传算法（GEQO, Genetic Query Optimizer），只求近似最优。
sql
-- 观察成本参数与优化器阈值
SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('seq_page_cost', 'random_page_cost', 'cpu_tuple_cost',
               'effective_cache_size', 'geqo_threshold', 'geqo',
               'join_collapse_limit', 'from_collapse_limit',
               'enable_seqscan', 'enable_nestloop', 'default_statistics_target')
ORDER BY name;
text
           name            | setting | unit
---------------------------+---------+------
 cpu_tuple_cost            | 0.01    |
 default_statistics_target | 100     |
 effective_cache_size      | 524288  | 8kB   -- 524288 × 8KB = 4GB
 enable_nestloop           | on      |
 enable_seqscan            | on      |
 from_collapse_limit       | 8       |
 geqo                      | on      |
 geqo_threshold            | 12      |
 join_collapse_limit       | 8       |
 random_page_cost          | 4       |
 seq_page_cost             | 1       |
(11 rows)

为什么统计信息决定计划质量

计划器不知道实际数据分布，它只能读 pg_statistic 里的直方图与 MCV（Most Common Values）。统计信息过期时，估算行数与实际行数会相差几个数量级，直接导致选错访问路径（如该走索引却走了顺序扫描）。这就是为什么 ANALYZE 如此重要，也是 autovacuum 必须正常工作的原因。

sql
-- 查看某个列的统计信息（需要 superuser 或表所有者）
-- 先建一张有倾斜分布的示例表
CREATE TABLE t_skew (id int, city text);
INSERT INTO t_skew
SELECT g, CASE WHEN g % 100 = 0 THEN 'Beijing' ELSE 'Other' END
FROM generate_series(1, 100000) AS g;
ANALYZE t_skew;

SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 't_skew';
text
 attname | n_distinct |   most_common_vals    |   most_common_freqs
---------+------------+-----------------------+------------------------
 id      |         -1 |                       |                        -- 全唯一，走直方图
 city    |          2 | {Other,Beijing}       | {0.99,0.01}            -- MCV 精确记录
(2 rows)

n_distinct = -1 表示「唯一值数量等于行数」，即该列全部唯一（如主键）；正数表示实际的唯一值个数。

阶段 5：执行器与拉取模型 ​

执行器按拉取模型（pull-based / Volcano model） 工作：从计划树根节点开始，逐层向上层返回结果。

text
Limit（根节点）
  └─ 向子节点请求 1 行 → 拿到后返回给客户端
       └─ Sort
            └─ 向子节点请求全部行 → 排序 → 逐个返回
                 └─ Seq Scan on t_user
                      └─ 逐页读 buffer → 逐行过滤 → 返回符合条件的行

每个计划节点都实现两个关键接口：ExecInit()（初始化）与 ExecProcNode()（取出下一行）。这种设计让 LIMIT 可以短路整个查询：

sql
-- 有 LIMIT 时，Seq Scan 不会扫描全表，取够 10 行就停
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM t_skew LIMIT 10;
text
                       QUERY PLAN
------------------------------------------------------------
 Limit  (cost=0.00..0.35 rows=10 width=12) (actual time=0.015..0.024 rows=10 loops=1)
   Buffers: shared hit=1
   ->  Seq Scan on t_skew  (cost=0.00..3472.00 rows=100000 width=12) (actual time=0.013..0.017 rows=10 loops=1)
         Buffers: shared hit=1
 Planning Time: 0.083 ms
 Execution Time: 0.043 ms
(5 rows)

注意 Seq Scan 的 rows=100000 是估算，而 actual ... rows=10 是实际只取了 10 行——这就是拉取模型的短路效果。同时 Planning Time 与 Execution Time 被分开报告，正好对应我们前面讲的「计划」与「执行」两阶段。

详细说明 ​

用 EXPLAIN 观察每个阶段的产物 ​

EXPLAIN 只显示计划，EXPLAIN ANALYZE 会真正执行并显示实际耗时。要观察重写结果，需要用 debug_print_rewritten；要观察计划树，用 debug_print_plan。

sql
-- 三个调试开关，把中间产物打到服务器日志（仅用于调试，生产慎用）
SET debug_print_parse = on;        -- 打印语法树
SET debug_print_rewritten = on;    -- 打印重写后的查询树
SET debug_print_plan = on;         -- 打印最终计划树

SELECT name FROM t_user WHERE id = 1;

SET debug_print_parse = off;
SET debug_print_rewritten = off;
SET debug_print_plan = off;

更轻量的做法是只观察计划树的结构与各节点成本：

sql
-- 生成三种格式的计划：text（默认）、json、yaml
EXPLAIN (FORMAT JSON, COSTS true, VERBOSE)
SELECT u.id, u.name, o.amount
FROM t_user u
JOIN t_order o ON o.user_id = u.id
WHERE u.status = 'active';
text
[
  {
    "Plan": {
      "Node Type": "Hash Join",
      "Startup Cost": 21.50,
      "Total Cost": 58.30,
      "Plan Rows": 480,
      "Plan Width": 40,
      "Hash Cond": "(o.user_id = u.id)",
      "Plans": [
        { "Node Type": "Seq Scan", "Relation Name": "t_order", "Plan Rows": 2000 },
        { "Node Type": "Hash", "Plans": [
            { "Node Type": "Seq Scan", "Relation Name": "t_user",
              "Filter": "(status = 'active'::text)", "Plan Rows": 240 }
        ]}
      ]
    }
  }
]

关键字段解读：

字段含义何时需要警惕
Node Type算子类型大表上出现 Seq Scan 且带过滤条件
Startup Cost返回第一行前的代价Sort 节点的启动成本高说明要全量排序
Total Cost返回全部行的总代价相邻计划对比时使用
Plan Rows估算输出行数与 actual rows 差异 > 10 倍即统计信息失真
Plan Width估算每行平均字节数过大说明 SELECT 列过多，可考虑覆盖索引
actual time实际耗时（毫秒）只在 ANALYZE 下出现
loops该节点执行次数loops 很大说明嵌套循环内层被反复执行
Rows Removed by Filter被过滤掉的行数远大于输出行数说明索引或条件设计有问题
sql
-- 最实用的诊断组合：ANALYZE 看实际值，BUFFERS 看 I/O 来源
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT status, count(*) FROM t_user GROUP BY status;
text
                       QUERY PLAN
---------------------------------------------------------------
 HashAggregate  (cost=31.40..33.40 rows=200 width=12) (actual time=1.245..1.247 rows=3 loops=1)
   Group Key: status
   Buffers: shared hit=42
   ->  Seq Scan on t_user  (cost=0.00..24.00 rows=1200 width=6) (actual time=0.011..0.612 rows=1200 loops=1)
         Buffers: shared hit=42
 Settings: work_mem = '4MB'
 Planning Time: 0.198 ms
 Execution Time: 1.402 ms
(7 rows)

Buffers: shared hit=42 表示全部从共享缓冲区命中，没有发生物理读；若出现 read= 则表示从磁盘读取。

预处理语句与计划缓存 ​

使用扩展查询协议（Extended Query Protocol）时，PREPARE 会让 PostgreSQL 缓存计划。这是与「每次重新解析+计划」不同的路径：

sql
-- 1. 准备语句：此时完成解析、分析、重写
PREPARE find_user (int) AS
  SELECT id, name FROM t_user WHERE id = $1;

-- 2. 前 5 次执行：使用「自定义计划」（按实际参数值重新计划，通常更优）
EXECUTE find_user(1);

-- 3. 第 6 次起：如果通用计划（按平均选择性估算）的总成本不高于自定义计划，
--    就固定使用通用计划，避免每次重复计划的 CPU 开销
EXECUTE find_user(1);

-- 4. 查看缓存中的预处理语句
SELECT name, statement, parameter_types, generic_plans, custom_plans
FROM pg_prepared_statements;
text
   name    |                 statement                  | parameter_types | generic_plans | custom_plans
-----------+--------------------------------------------+-----------------+---------------+--------------
 find_user | PREPARE find_user (int) AS ...             | {integer}       |             0 |            5
(1 row)

预处理语句的「双刃剑」

计划缓存节省了每次计划的 CPU，但通用计划用的是平均选择性。如果参数值分布极不均匀（例如查「近 1 天订单」和「近 3 年订单」用同一条 SQL），通用计划可能对其中一类参数极差。这是「PREPARE 后偶发慢查询」的经典原因。缓解办法：拆分成两条 SQL、加 plan_cache_mode = force_custom_plan，或在应用层改用逐次拼参数（代价是失去计划缓存）。

何时会跳过某些阶段 ​

并非所有 SQL 都走完整的四阶段：

场景跳过的阶段说明
SELECT 1 等无表查询跳过重写与计划直接由简单表达式求值返回
事务控制语句（BEGIN/COMMIT）跳过计划与执行由事务管理器直接处理
SET 参数、SHOW跳过计划不产生计划树
缓存了通用计划的 EXECUTE复用计划解析、分析、重写、计划都跳过（若为通用计划）
COPY 批量导入部分跳过不走常规执行器，直接调存储层
逻辑复制的应用进程不经过解析直接按 WAL 记录修改数据
sql
-- 对比：有表查询才有完整的计划与执行时间
SELECT 1;
text
 ?column?
----------
        1
(1 row)
sql
EXPLAIN (ANALYZE, TIMING off) SELECT 1;
text
                    QUERY PLAN
--------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=4) (actual rows=1 loops=1)
 Planning Time: 0.034 ms
 Execution Time: 0.006 ms
(3 rows)

各阶段的耗时诊断手段 ​

阶段诊断工具观察点
解析 + 分析 + 重写log_min_duration_statement、pg_stat_statements无法单独拆分，合并在「总耗时」里
计划EXPLAIN 的 Planning Time、auto_explain计划耗时 > 执行耗时时优先怀疑统计信息
执行EXPLAIN (ANALYZE, BUFFERS)逐节点看 actual time 与 Buffers
整体pg_stat_statementstotal_exec_time、mean_exec_time、rows
等待pg_stat_activity.wait_event定位是在等锁、等 I/O 还是等 WAL
sql
-- pg_stat_statements：按总耗时排序找出最消耗的 SQL（需预先安装扩展）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT queryid,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       round(100.0 * shared_blks_hit
             / nullif(shared_blks_hit + shared_blks_read, 0), 2) AS hit_pct,
       left(query, 60) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;
text
     queryid     | calls | total_ms | mean_ms | hit_pct |       query_preview
-----------------+-------+----------+---------+---------+----------------------------
 -8123456789012345 | 15230 | 84213.55 |    5.53 |   99.87 | SELECT u.id, u.name FROM ...
  1234567890123456 |  8412 | 20114.02 |    2.39 |   98.42 | UPDATE t_order SET status = ...
(2 rows)
sql
-- 诊断执行期的等待：这条视图能直接看到 SQL 卡在哪
SELECT pid,
       state,
       wait_event_type,
       wait_event,
       now() - query_start AS query_time,
       left(query, 50)   AS query_preview
FROM pg_stat_activity
WHERE state <> 'idle' AND backend_type = 'client backend'
ORDER BY query_time DESC;
text
  pid  |        state        | wait_event_type |  wait_event  | query_time  |  query_preview
-------+---------------------+-----------------+--------------+-------------+------------------
 18823 | active              | Lock            | transactionid| 00:02:31.12 | UPDATE t_order SET ...
  9021 | active              | IO              | DataFileRead | 00:00:04.88 | SELECT * FROM t_skew ...
(2 rows)

wait_event_type = Lock 且 wait_event = transactionid 表示在等另一个事务释放行锁；IO / DataFileRead 表示确实在读磁盘，属正常等待。

实战示例 ​

示例 1：同一条查询的三种计划形态 ​

目标：通过改变数据分布与索引，观察计划器如何在 Seq Scan、Index Scan、Bitmap Heap Scan 之间取舍。

sql
-- 1. 准备数据：10 万行，status 只有 3 个取值
CREATE TABLE t_order_demo (
  id      bigserial PRIMARY KEY,
  user_id int,
  status  text,
  amount  numeric(10,2)
);
INSERT INTO t_order_demo (user_id, status, amount)
SELECT (random() * 1000)::int,
       (ARRAY['paid','pending','cancelled'])[1 + (random() * 2)::int],
       (random() * 10000)::numeric(10,2)
FROM generate_series(1, 100000);
ANALYZE t_order_demo;
sql
-- 2. 场景 A：无索引 + 高选择性谓词 → 顺序扫描
EXPLAIN (ANALYZE, BUFFERS, TIMING off)
SELECT * FROM t_order_demo WHERE status = 'paid';
text
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on t_order_demo  (cost=0.00..2084.00 rows=33600 width=27) (actual rows=33421 loops=1)
   Filter: (status = 'paid'::text)
   Rows Removed by Filter: 66579
   Buffers: shared hit=1042
sql
-- 3. 建索引后重试：优化器会改走位图索引扫描（返回行数占比太高，不适合普通索引扫描）
CREATE INDEX idx_order_status ON t_order_demo (status);
ANALYZE t_order_demo;

EXPLAIN (ANALYZE, BUFFERS, TIMING off)
SELECT * FROM t_order_demo WHERE status = 'paid';
text
                             QUERY PLAN
---------------------------------------------------------------------
 Bitmap Heap Scan on t_order_demo  (cost=570.00..1560.00 rows=33600 width=27) (actual rows=33421 loops=1)
   Recheck Cond: (status = 'paid'::text)
   Heap Blocks: exact=1039
   ->  Bitmap Index Scan on idx_order_status  (cost=0.00..561.60 rows=33600 width=0) (actual rows=33421 loops=1)
         Index Cond: (status = 'paid'::text)
sql
-- 4. 换成高选择性谓词：这次优化器会选择普通索引扫描
EXPLAIN (ANALYZE, BUFFERS, TIMING off)
SELECT * FROM t_order_demo WHERE user_id = 42;
text
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_order_user on t_order_demo  (cost=0.42..36.10 rows=98 width=27) (actual rows=94 loops=1)
   Index Cond: (user_id = 42)
   Buffers: shared hit=12

从 Buffers: shared hit=1042 降到 hit=12，物理读的差异就是计划器选择路径的核心依据。

示例 2：统计信息失真导致选错计划 ​

目标：人为制造「统计信息过期」，观察估算行数与实际行数的巨大偏差。

sql
-- 1. 建表并分析，此时统计信息准确
CREATE TABLE t_stale (id int, flag boolean);
INSERT INTO t_stale SELECT g, false FROM generate_series(1, 1000) AS g;
ANALYZE t_stale;

EXPLAIN (ANALYZE, TIMING off)
SELECT * FROM t_stale WHERE flag = true;
text
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on t_stale  (cost=0.00..15.00 rows=1 width=5) (actual rows=0 loops=1)
   Filter: (flag = true)
   Rows Removed by Filter: 1000
 Planning Time: 0.120 ms
sql
-- 2. 大量插入 true，但不重新 ANALYZE
INSERT INTO t_stale SELECT g, true FROM generate_series(1001, 50000) AS g;

-- 3. 再查：估算仍是 rows=1，实际是 49000 行 —— 偏差近 5 万倍
EXPLAIN (ANALYZE, TIMING off)
SELECT * FROM t_stale WHERE flag = true;
text
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on t_stale  (cost=0.00..629.00 rows=1 width=5) (actual rows=49000 loops=1)
   Filter: (flag = true)
   Rows Removed by Filter: 1000
 Planning Time: 0.098 ms
sql
-- 4. 重新 ANALYZE，统计信息恢复准确
ANALYZE t_stale;

EXPLAIN (ANALYZE, TIMING off)
SELECT * FROM t_stale WHERE flag = true;
text
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on t_stale  (cost=0.00..629.00 rows=49000 width=5) (actual rows=49000 loops=1)
   Filter: (flag = true)
   Rows Removed by Filter: 1000

关键信号：rows=1 vs actual rows=49000。 只要有 10 倍以上的偏差，就应怀疑统计信息过期，或该列的 default_statistics_target 不够（对倾斜列可单独提高）。

sql
-- 针对特定列提高统计精度（会延长 ANALYZE 时间）
ALTER TABLE t_order_demo ALTER COLUMN user_id SET STATISTICS 500;
ANALYZE t_order_demo;

示例 3：用 auto_explain 捕获「线上慢查询的真实计划」 ​

EXPLAIN 需要手工执行；线上慢查询发生时不方便复现。auto_explain 扩展能把超过阈值的查询的真实执行计划自动写入日志。

sql
-- 1. 加载扩展（需重启或写入 shared_preload_libraries 后重启）
CREATE EXTENSION IF NOT EXISTS auto_explain;
ini
# postgresql.conf
shared_preload_libraries = 'auto_explain'

# 记录执行超过 1 秒的查询计划
auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = on          # 打印实际执行统计（有额外开销）
auto_explain.log_buffers = on          # 打印缓冲区使用
auto_explain.log_verbose = on
auto_explain.log_nested_statements = on
auto_explain.sample_rate = 1.0         # 采样率，高负载时可降到 0.1
text
# 日志输出示例（每条慢查询对应一段）
LOG:  duration: 2451.883 ms  plan:
  Query Text: SELECT user_id, sum(amount) FROM t_order_demo GROUP BY user_id
  HashAggregate  (cost=2084.00..2184.00 rows=1000 width=36) (actual time=1203.4..1204.1 rows=1001 loops=1)
    Group Key: user_id
    Buffers: shared hit=1042
    ->  Seq Scan on t_order_demo  (cost=0.00..2084.00 rows=100000 width=14) (actual time=0.02..812.5 rows=100000 loops=1)

结合 auto_explain.log_analyze，即使无法复现线上场景，也能拿到当时真实的节点耗时分布。

注意事项 ​

  • EXPLAIN ANALYZE 会真正执行 SQL：对 INSERT/UPDATE/DELETE 使用它会修改数据。要查看写操作的执行计划而不落盘，请把语句包在事务里并 ROLLBACK。
  • Planning Time 也可能很大：连接表多、join_collapse_limit 大、统计目标高的查询，计划耗时可观；此时优化方向是减少连接表数或降低搜索阈值。
  • rows 与 actual rows 差异是首要诊断信号：10 倍以内可接受，100 倍以上必须处理统计信息或提高 default_statistics_target。
  • enable_seqscan = off 不是优化手段：它只是把顺序扫描的代价人为抬高，用于验证「索引是否可用」，不应写入生产配置。
  • 视图不是性能瓶颈的免罪牌：视图展开后等价于手写 SQL，性能问题要在底层表与索引上解决。
  • RLS 与 RULE 会改变执行计划：开启行级安全策略后，所有访问该表的查询都会被注入谓词，索引设计需同步调整。
  • 通用计划可能劣化：PREPARE/参数化查询跑顺后突然变慢，优先检查是否为通用计划。
  • work_mem 不足会导致计划改变：排序/哈希被迫落盘后成本模型会偏向其他路径，EXPLAIN 里出现 Sort Method: external merge Disk: xxxkB 即为证据。
  • LIMIT 会显著改变计划选择：加 LIMIT 后计划器知道只需取少量行，可能主动从 Seq Scan 切到 Index Scan；因此不带 LIMIT 的 EXPLAIN 不能代表带 LIMIT 的真实情况。
  • 解析与分析的耗时无法单独测量：它们与计划时间一起被计入 Planning Time 的相邻区间，只能通过 log_min_duration_statement 与 pg_stat_statements 做整体判断。
sql
-- 不推荐：在生产中使用 EXPLAIN ANALYZE 直接跑写操作
-- EXPLAIN ANALYZE DELETE FROM t_order_demo WHERE status = 'pending';   -- 不推荐，会真删
sql
-- 推荐：写操作的计划用事务包裹并回滚
BEGIN;
EXPLAIN ANALYZE DELETE FROM t_order_demo WHERE status = 'pending';
ROLLBACK;

最佳实践 ​

  • 优化顺序固定为：解析/计划 → 执行 → 等待：先用 pg_stat_statements 找到高耗 SQL，再用 EXPLAIN (ANALYZE, BUFFERS) 定位节点，最后用 pg_stat_activity.wait_event 排除锁与 I/O 等待。
  • ANALYZE 跟着批量写入走：大批量导入后手工执行一次 ANALYZE，不要等 autovacuum 的延迟。
  • 对倾斜列提高 default_statistics_target：全局保持 100，仅对少数热点列用 ALTER COLUMN ... SET STATISTICS 500。
  • SSD 上把 random_page_cost 降到 1.1~1.5：默认值 4.0 是按机械盘标定的，SSD 上会导致优化器过度偏爱顺序扫描。
  • effective_cache_size 设为物理内存的 50%~75%：它直接影响索引扫描的成本估算，设得贴合实际能显著改善计划质量。
  • 保持 EXPLAIN (ANALYZE, BUFFERS) 成为肌肉记忆：BUFFERS 能区分「内存命中」与「真实磁盘读」，是判断是否需要加内存/加索引最直接的证据。
  • 生产环境按需开启 auto_explain：阈值设为业务可接受的慢查询线（如 1s），sample_rate 在高负载下调低，避免日志淹没。
  • 参数化查询用扩展协议而非字符串拼接：既避免 SQL 注入，也能复用计划；但要监控通用计划劣化。
  • 定期审查 pg_stat_user_tables 的 seq_scan 与 idx_scan：大表上高频 seq_scan 是缺失索引的强信号。
sql
-- 找出「被大量顺序扫描」的大表：通常是缺索引
SELECT relname,
       seq_scan,
       idx_scan,
       n_live_tup,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE seq_scan > 1000
  AND n_live_tup > 100000
ORDER BY seq_scan DESC
LIMIT 10;

下一节

继续学习：Postmaster 主进程

参考链接 ​