PHP小码农

Appearance

EXPLAIN 基础用法 ​

EXPLAIN 是 PostgreSQL 性能分析的第一把钥匙。它把一条 SQL 交给查询规划器（query planner），并把规划器最终选定的执行计划（execution plan）打印出来，告诉你「这条 SQL 准备怎么跑」：用哪种扫描方式读表、走不走索引、几个表怎么连接、在哪里排序、在哪里过滤，以及每一步的估算代价与估算行数。

对于有 SQL 基础的后端开发者来说，学会读执行计划，是从「会写 SQL」跨越到「能调优 SQL」的分水岭。本节先建立 EXPLAIN 的基本使用框架，后续几节再深入解读输出细节。

前置知识

阅读本节前，建议先了解：其他缓冲区（Other Buffers），以及统计信息如何影响执行计划

基础概念 ​

先统一术语。PostgreSQL 官方文档使用的词汇与中文社区习惯的叫法略有差异，下表给出对照。

中文英文说明
查询规划器query planner / optimizer为 SQL 选择执行路径的组件
执行计划query plan / execution plan规划器选定的节点树
计划节点plan node计划树中的一个算子，如 Seq Scan
计划树plan tree节点构成的树，子节点结果向上传递
顺序扫描Seq Scan从头到尾顺序读整张表
索引扫描Index Scan通过索引定位行，再回表取数据
仅索引扫描Index Only Scan所需列都在索引里，不用回表
位图扫描Bitmap Scan先用索引构建位图，再按物理顺序回表
估算代价estimated cost抽象代价单位，不是毫秒
估算行数estimated rows规划器预估该节点输出的行数
行宽width预估每行平均字节数
实际执行ANALYZE真正执行语句并输出真实数据
缓冲区统计BUFFERS共享块命中/读盘等 I/O 统计

一个直观的 ASCII 示意图，帮助建立「计划树」的空间感：

text
                     ┌──────────────────────┐
   最顶层（最后执行）│  Sort                │  ← 输出给客户端
                     └──────────┬───────────┘
                                │
                     ┌──────────┴───────────┐
                     │  Hash Join           │  ← 连接
                     └────┬───────────┬─────┘
                          │           │
              ┌───────────┴──┐   ┌────┴─────────┐
              │ Seq Scan     │   │ Hash         │
              │ on orders    │   └────┬─────────┘
              └──────────────┘        │
                              ┌───────┴──────────┐
   最底层（最先执行）         │ Seq Scan         │
                              │ on products      │
                              └──────────────────┘

阅读计划树有三条铁律：

  1. 自下而上：缩进最深（最底层）的节点最先执行，结果向上传递给父节点。
  2. 同层从左到右：同一缩进层级的节点，按书写顺序从上到下、从左到右执行。对于 Nested Loop，左边是外层（outer），右边是内层（inner），外层每返回一行，内层就完整执行一次。
  3. 父节点代价包含子节点代价：Hash Join 的 cost 里已经包含了两个 Seq Scan 的代价，做性能归因时要减掉。

语法与用法 ​

EXPLAIN 的现代语法（PostgreSQL 9.0 起支持）如下：

sql
EXPLAIN [ ( option [, ...] ) ] statement

-- option 的完整列表
ANALYZE [ boolean ]        -- 真正执行语句，输出实际耗时与实际行数
VERBOSE [ boolean ]        -- 输出列清单、schema 限定名、触发器名等
COSTS [ boolean ]          -- 是否显示代价/行数/行宽，默认 on
SETTINGS [ boolean ]       -- 显示被非默认值覆盖的规划相关参数
GENERIC_PLAN [ boolean ]   -- 生成与参数值无关的通用计划（不可与 ANALYZE 同用）
BUFFERS [ boolean ]        -- 显示缓冲区使用量，需配合 ANALYZE
SERIALIZE { NONE | TEXT | BINARY }  -- 是否统计序列化输出数据的开销
WAL [ boolean ]            -- 显示 WAL 记录数、fpi、字节数（需配合 ANALYZE）
TIMING [ boolean ]         -- 是否统计每个节点的实际耗时，默认 on
SUMMARY [ boolean ]        -- 是否输出 Planning Time / Execution Time 汇总
MEMORY [ boolean ]         -- 显示规划阶段的内存消耗
FORMAT { TEXT | XML | JSON | YAML }  -- 输出格式，默认 TEXT

每个选项都可以写成 option（等价于 option true）或显式 option false 来关闭。布尔值的写法有 TRUE / ON / 1 三种真值写法和 FALSE / OFF / 0 三种假值写法。

最常用的一行是：

sql
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1001;

它等价于：

sql
EXPLAIN (ANALYZE, TIMING, SUMMARY, COSTS, BUFFERS) SELECT * FROM orders WHERE user_id = 1001;

注意：从 PostgreSQL 18 起，EXPLAIN ANALYZE 默认会附带 BUFFERS 信息，不需要再手写 BUFFERS（当然显式写上也无害，是合法的重复）。

PostgreSQL 9.0 之前的老语法仍然被支持，但选项顺序被强制固定：

sql
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

例如：

sql
EXPLAIN ANALYZE VERBOSE SELECT count(*) FROM orders;

老语法的限制是：选项必须严格按 ANALYZE、VERBOSE 的顺序书写，且不能使用 FORMAT、BUFFERS 等新选项。新代码应该一律使用括号选项语法。

详细说明 ​

计划树与自下而上的阅读顺序 ​

来看一个真实的两表连接计划，并逐行标注阅读顺序：

sql
EXPLAIN
SELECT c.name, o.order_no, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Hangzhou'
ORDER BY o.created_at DESC;
text
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Sort  (cost=1420.55..1420.80 rows=100 width=52)          -- ④ 最后执行：排序
   Sort Key: o.created_at DESC
   ->  Hash Join  (cost=520.00..1417.00 rows=100 width=52) -- ③ 连接
         Hash Cond: (o.customer_id = c.id)
         ->  Seq Scan on orders o  (cost=0.00..760.00 rows=20000 width=28)  -- ② 全表扫描 orders
         ->  Hash  (cost=518.75..518.75 rows=100 width=32)                 -- ② 构建哈希表
               ->  Seq Scan on customers c  (cost=0.00..518.75 rows=100 width=32)  -- ① 最先执行
                     Filter: (city = 'Hangzhou'::text)
(9 rows)

执行顺序是 ① → ② → ③ → ④：

  1. Seq Scan on customers c：扫描客户表，用 Filter 过滤出杭州的客户。
  2. Hash：把上一步的结果构造成内存哈希表，key 是 c.id。同时 Seq Scan on orders o 也已扫描完成。
  3. Hash Join：拿 orders 的每一行去探测哈希表，输出匹配的组合。Hash Cond 就是连接条件。
  4. Sort：按 o.created_at DESC 排序，输出最终结果。

缩进层级就是节点在树中的深度，-> 是 PostgreSQL 文本格式里表示「这是一个子节点」的符号。缩进越深，越先执行。

EXPLAIN 与 EXPLAIN ANALYZE 的区别 ​

这是最容易踩坑的地方，务必记牢：

对比项EXPLAINEXPLAIN ANALYZE
是否执行 SQL否，仅规划是，真正执行
输出内容估算代价、估算行数、行宽追加实际耗时、实际行数、循环次数
数据是否有副作用无有！写操作会真的改数据
适合场景快速看计划、生产环境排查需要校验估算准确性、定位慢在哪一步
开销极小可能显著慢于普通执行（有插桩开销）

EXPLAIN 只调用规划器，不触碰执行器，所以它绝对安全，可以在生产库随便跑。而 EXPLAIN ANALYZE 会真正执行语句：

sql
-- 危险示例：这条会真的删除数据！
EXPLAIN ANALYZE DELETE FROM orders WHERE created_at < '2024-01-01';

正确做法是用事务包裹后回滚：

sql
BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE created_at < '2024-01-01';
ROLLBACK;   -- 撤销，数据恢复原状

同样的规则适用于 INSERT、UPDATE、DELETE、MERGE、CREATE TABLE AS、EXECUTE 等语句。

推荐组合：EXPLAIN (ANALYZE, BUFFERS) ​

如果只能记一个组合，就记这个：

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 1001;

原因有三点：

  • ANALYZE 给出真实耗时与真实行数，才能判断估算是否准确；
  • BUFFERS 揭示 I/O 来源，区分「快是因为数据在缓存里」还是「快是因为真没读多少数据」；
  • 两者组合是排查「估算准但实际慢」和「估算错但看起来不慢」两类问题的唯一手段。

如果嫌代价数字干扰视线（代价是抽象的，实际看指标时应聚焦时间和行数），可以同时关掉 COSTS：

sql
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM orders;

传统语法与现代语法的取舍 ​

老语法 EXPLAIN ANALYZE VERBOSE stmt; 仍然可用，但它有三个硬伤：

  • 选项顺序被强制，写错顺序会直接语法错误；
  • 无法使用 BUFFERS、FORMAT、WAL、SETTINGS 等新选项；
  • 无法显式关闭某个选项，例如无法表达 COSTS OFF。

所以养成习惯，一律写括号语法：

sql
-- 不推荐：老语法，能力受限
EXPLAIN ANALYZE VERBOSE SELECT count(*) FROM orders;

-- 推荐：新语法，表达力完整
EXPLAIN (ANALYZE, VERBOSE, BUFFERS, COSTS OFF, FORMAT TEXT)
SELECT count(*) FROM orders;

FORMAT：机器可读的输出格式 ​

默认的 TEXT 格式最适合人眼阅读；如果你想把计划喂给程序分析（例如写入监控系统、做计划回归对比），应该用 JSON：

sql
EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 1001;
text
                          QUERY PLAN
--------------------------------------------------------------
 [                                                           +
   {                                                         +
     "Plan": {                                               +
       "Node Type": "Index Scan",                            +
       "Parallel Aware": false,                              +
       "Async Capable": false,                               +
       "Scan Direction": "Forward",                          +
       "Index Name": "idx_orders_user_id",                   +
       "Relation Name": "orders",                            +
       "Alias": "o",                                         +
       "Startup Cost": 0.29,                                 +
       "Total Cost": 24.83,                                  +
       "Plan Rows": 12,                                      +
       "Plan Width": 28,                                     +
       "Actual Startup Time": 0.031,                         +
       "Actual Total Time": 0.052,                           +
       "Actual Rows": 12,                                    +
       "Actual Loops": 1,                                    +
       "Index Cond": "(user_id = 1001)",                     +
       ...
     }                                                       +
   }                                                         +
 ]
(1 row)

JSON 与 YAML 的关键字段名与 TEXT 一一对应（Node Type↔节点名，Total Cost↔cost 右半边，Plan Rows↔rows），只是换了载体。PHP 里可以这样解析：

text
-- psql 的 JSON 输出带 + 续行符时不利于程序解析，建议改用
\pset format unaligned
\pset tuples_only on
EXPLAIN (FORMAT JSON) SELECT 1;

SETTINGS：看看是哪个参数影响了计划 ​

当你不确定「计划为什么变成这样」时，SETTINGS 会列出所有被非默认值覆盖、且影响规划的参数：

sql
EXPLAIN (SETTINGS, COSTS OFF)
SELECT * FROM orders WHERE user_id = 1001;
text
                          QUERY PLAN
--------------------------------------------------------------
 Index Scan using idx_orders_user_id on orders
   Index Cond: (user_id = 1001)
 Settings: random_page_cost = '1.1', effective_cache_size = '12GB'
(3 rows)

这在排查「本地测试快、线上慢」时非常有用——多半是两边的 random_page_cost 或 effective_cache_size 不一样。

auto_explain：自动记录慢查询的计划 ​

EXPLAIN 需要你手动敲。生产环境里，大多数慢查询是「事后才发现」的，这时可以用 auto_explain 扩展，把超过阈值的语句的执行计划自动写进日志：

sql
-- 方式一：会话级临时启用（需要 superuser）
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '1s';   -- 超过 1 秒就记录
SET auto_explain.log_analyze = on;          -- 记录实际执行数据
SET auto_explain.log_buffers = on;          -- 记录缓冲区
SET auto_explain.log_format = 'json';       -- 结构化输出，便于解析
SET auto_explain.log_nested_statements = on;

更稳妥的方式是写进 postgresql.conf：

ini
# postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_verbose = off
auto_explain.log_format = 'json'
auto_explain.log_level = 'LOG'

注意 auto_explain.log_analyze = on 会带来可观的插桩开销（类似 EXPLAIN ANALYZE 的开销），生产启用时建议把阈值设成只抓真正的问题查询。

不应只看 cost 大小 ​

新手最常见的误区是「这个节点 cost 大所以它慢」。这是错的，原因有三：

  1. cost 是估算值，不是实测值。它只是规划器用来比较不同方案的相对刻度（默认以顺序读一个数据页 = 1.0 为基准），与真实毫秒没有固定换算关系。
  2. cost 包含子节点。一个 Hash Join cost 是 1417，可能其中 1200 都来自两个 Seq Scan，连接本身只花了 217。
  3. 估算本身可能错。rows=1 的节点实际输出了 100000 行，此时 cost 估算完全失真，光看 cost 会得出错误结论。

正确的做法是：先看 EXPLAIN ANALYZE 的 actual time，再看 rows 与 actual rows 的差距，最后才回头看 cost。

sql
-- 不要这样得出结论：Seq Scan cost 760 最高，所以它是瓶颈
-- 而应该这样看：
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT * FROM orders WHERE amount > 9999;
text
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Seq Scan on orders  (actual time=0.015..182.400 rows=3 loops=1)
   Filter: (amount > 9999)
   Rows Removed by Filter: 199997
   Buffers: shared hit=64 read=1204
 Planning Time: 0.180 ms
 Execution Time: 182.610 ms
(6 rows)

这里才有真信息：扫了 20 万行只留下 3 行（Rows Removed by Filter: 199997，过滤效率极低），读了 1204 个块。结论应该是「加 amount 索引」。

EXPLAIN 的权限模型 ​

EXPLAIN 对权限的要求常被误解：

  • 不需要表的 SELECT 权限。只要你能执行这条语句（或至少能通过解析），就能对它 EXPLAIN。
  • 但对于 EXPLAIN ANALYZE，因为会真正执行语句，所以需要与正常执行相同的权限（例如 SELECT、DELETE 权限）。

换句话说，EXPLAIN（不带 ANALYZE）是一种「低权限也有用的诊断手段」，但它也可能通过计划泄露结构信息（例如表名、索引名），所以在多租户环境中要留意。

sql
-- 创建一个只能 EXPLAIN 不能查询的角色（演示权限边界）
CREATE ROLE analyst LOGIN PASSWORD 'secret';
GRANT USAGE ON SCHEMA public TO analyst;
-- analyst 可以：EXPLAIN SELECT * FROM orders;
-- analyst 不能：EXPLAIN ANALYZE SELECT * FROM orders;  -- 缺少 SELECT 权限

小表与大表的差异 ​

EXPLAIN 的效果高度依赖数据量。在只有几行、几十行的小表上，规划器必然选择 Seq Scan，无论你建了多少索引。这是因为：

  • 小表通常只占 1 个数据页，顺序读完的成本极低；
  • 走索引至少要访问索引页 + 堆页，启动代价（startup cost）反而更大；
  • 规划器的最小化目标是总代价，不是「用没用索引」。
sql
-- 在 5 行的小表上，即使 id 有主键索引，也几乎必然是 Seq Scan
EXPLAIN SELECT * FROM tiny_table WHERE id = 3;
text
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on tiny_table  (cost=0.00..1.05 rows=1 width=68)
   Filter: (id = 3)
(2 rows)

所以：

  • 不要在小表上验证索引是否生效，结论没有意义；
  • 不要在生产库上直接验证，可能造出脏数据；
  • 应构造足够数据量的测试表（至少几万行，最好与生产同量级）。

构造测试数据 ​

验证执行计划需要有一定规模的数据。下面这段 SQL 可以在几秒内造出 20 万行订单数据和 2000 行客户数据：

sql
-- 准备一个独立的测试库，避免污染生产
CREATE TABLE customers (
    id         bigserial PRIMARY KEY,
    name       text        NOT NULL,
    city       text        NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    id          bigserial PRIMARY KEY,
    customer_id bigint      NOT NULL REFERENCES customers(id),
    order_no    text        NOT NULL,
    amount      numeric(12,2) NOT NULL,
    status      text        NOT NULL DEFAULT 'pending',
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- 插入 2000 个客户
INSERT INTO customers (name, city)
SELECT 'customer_' || g,
       (ARRAY['Hangzhou','Beijing','Shanghai','Shenzhen','Chengdu'])[1 + (g % 5)]
FROM generate_series(1, 2000) AS g;

-- 插入 200000 条订单
INSERT INTO orders (customer_id, order_no, amount, status, created_at)
SELECT 1 + (random() * 1999)::int,
       'ORD-' || lpad(g::text, 9, '0'),
       round((random() * 10000)::numeric, 2),
       (ARRAY['pending','paid','shipped','closed'])[1 + (g % 4)],
       now() - (random() * interval '365 days')
FROM generate_series(1, 200000) AS g;

-- 更新统计信息，否则规划器对数据一无所知
ANALYZE customers;
ANALYZE orders;

ANALYZE 这一步不能省。没有统计信息时，规划器会退化成用默认假设（比如假设任何表都只有 1000 页），计划可能荒唐得离谱。

实战示例 ​

场景一：判断一条慢 SQL 是「没走索引」还是「走了索引也慢」 ​

业务反馈：按下单用户查询订单列表很慢。

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, order_no, amount, created_at
FROM orders
WHERE customer_id = 1500
ORDER BY created_at DESC
LIMIT 20;
text
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1834.72..1856.10 rows=20 width=44) (actual time=61.204..63.815 rows=20 loops=1)
   Buffers: shared hit=8 read=1216
   ->  Sort  (cost=1834.72..1835.22 rows=100 width=44) (actual time=61.204..61.207 rows=20 loops=1)
         Sort Key: created_at DESC
         Sort Method: top-N heapsort  Memory: 26kB
         Buffers: shared hit=8 read=1216
         ->  Seq Scan on orders  (cost=0.00..1832.00 rows=100 width=44) (actual time=0.021..55.830 rows=97 loops=1)
               Filter: (customer_id = 1500)
               Rows Removed by Filter: 199903
               Buffers: shared hit=8 read=1216
 Planning Time: 0.245 ms
 Execution Time: 63.980 ms

诊断结论：

  • Seq Scan 扫了 20 万行只留 97 行，Rows Removed by Filter: 199903——过滤效率极低，属于典型的「缺索引」；
  • read=1216 表示大部分数据页不在缓存，读了磁盘；
  • 修复方式：在 (customer_id, created_at DESC) 上建复合索引。
sql
CREATE INDEX CONCURRENTLY idx_orders_customer_created
    ON orders (customer_id, created_at DESC);

ANALYZE orders;

再跑一次计划，变成了 Index Scan，耗时从 64ms 降到 0.1ms 级别。

场景二：用 SETTINGS 定位「本地快线上慢」 ​

线上有个查询走了 Seq Scan，但本地测试库明明是同样的 SQL、同样的索引，走的却是 Index Scan。

sql
EXPLAIN (SETTINGS, COSTS OFF)
SELECT * FROM orders WHERE customer_id < 50 AND status = 'paid';
text
                             QUERY PLAN
---------------------------------------------------------------------
 Seq Scan on orders
   Filter: ((customer_id < 50) AND (status = 'paid'::text))
 Settings: random_page_cost = '4', effective_cache_size = '4GB'
(3 rows)

而本地库的 SETTINGS 显示 random_page_cost = '1.1'、effective_cache_size = '48GB'。差异在于：本地是 SSD 且内存大，运维把 random_page_cost 调低了，规划器就更愿意走索引；线上还是默认值，规划器认为随机 I/O 很贵，宁愿用顺序扫描。

结论：执行计划的差异，往往是成本参数差异导致的，不要只盯着数据量。

注意事项 ​

  • EXPLAIN ANALYZE 会真正执行语句，写操作必须包在事务里回滚。

    sql
    -- 错误！直接执行会真的清空数据
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'closed';

-- 正确做法
BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'closed';
ROLLBACK;

  • 不要在小表上判断索引是否生效。

    sql
    -- 不推荐：tiny_table 只有 5 行，无论怎样都是 Seq Scan
CREATE INDEX idx_tiny ON tiny_table (id);
EXPLAIN SELECT * FROM tiny_table WHERE id = 3;   -- 必然 Seq Scan

  • 不要只看 cost 数字做优化决策。 cost 是相对估算值，且父节点包含子节点代价。要看 actual time、rows vs actual rows。

  • EXPLAIN ANALYZE 有插桩开销，测得的时间可能比真实执行慢。

    sql
    -- 对每行耗时极短的节点，计时开销占比可能很高
-- 只关心行数时，关掉 TIMING 可以显著减少开销
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT * FROM orders;

  • 不要在没跑 ANALYZE 的表上做计划分析。 统计信息缺失会让估算完全失真。

    sql
    -- 错误！批量导入后直接 EXPLAIN，估算行数可能差几个数量级
INSERT INTO orders SELECT ...;
EXPLAIN ANALYZE SELECT * FROM orders WHERE amount > 5000;

-- 正确：先收集统计信息
ANALYZE orders;

  • 不要为了让计划「好看」而长期依赖 enable_* 开关。

    sql
    -- 不推荐：永久关闭 seqscan 来「逼」规划器走索引
ALTER SYSTEM SET enable_seqscan = off;   -- 会拖垮其他查询

    可以在会话里临时试探，但真正的修复应该是补索引或修正统计信息。

  • EXPLAIN 不会告诉你锁等待、触发器开销、序列化开销。 这些发生在计划节点之外。

    sql
    -- 计划里看不到锁等待，但实际可能卡在这里
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders FOR UPDATE;

  • 注意 LIMIT 会让上层节点的实际耗时与估算完全不同。 Limit 节点提前停止，Sort 节点的 actual rows 只有 20，但它内部的估算仍是假设跑完。

  • GENERIC_PLAN 不能与 ANALYZE 同时使用。 需要看通用计划时，参数要用 $1 占位符并显式标注类型。

    sql
    EXPLAIN (GENERIC_PLAN) SELECT * FROM orders WHERE customer_id = $1::bigint;

最佳实践 ​

  • 默认使用 EXPLAIN (ANALYZE, BUFFERS)。 这是信息量与开销的最佳平衡点，能同时看到真实耗时、真实行数和 I/O 来源。

    sql
    EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

  • 代价数字干扰时用 COSTS OFF。 聚焦时间与行数两个维度。

    sql
    EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT ...;

  • 只关心行数时关掉 TIMING。 可显著降低插桩开销，尤其是节点执行次数极多（loops 很大）的计划。

    sql
    EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) SELECT ...;

  • 程序解析用 JSON 格式。 TEXT 格式的缩进和续行对程序不友好。

    sql
    EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS) SELECT ...;

  • 生产排查慢查询用 auto_explain，而不是事后 EXPLAIN ANALYZE。 后者可能因为「问题已过、数据已变」而重现不出来。

    ini
    shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = on
auto_explain.log_buffers = on

  • 每次大批量数据变更后手动 ANALYZE。 不要等 autovacuum 慢慢跟上。

    sql
    -- 只更新统计信息，不回收空间，开销小、见效快
ANALYZE orders;

  • 在独立测试库上构造与生产同量级的数据。 小表上的计划结论没有参考价值。

    sql
    -- 用 generate_series + random() 快速造数据
INSERT INTO orders SELECT ... FROM generate_series(1, 1000000);
ANALYZE orders;

  • 养成「先看 actual rows 与估算 rows 的比值」的习惯。 比值超过 10 倍是明确的危险信号，说明统计信息不足或条件相关性太强。

    sql
    -- 计划里 rows=100 而 actual rows=50000，说明估算偏差 500 倍
-- 需要 ANALYZE、提高统计目标，或建立扩展统计信息
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;

  • 用 SETTINGS 排查环境差异。 本地与线上计划不一致时，先比对 random_page_cost、effective_cache_size、work_mem 等参数。

    sql
    EXPLAIN (SETTINGS, COSTS OFF) SELECT ...;

  • EXPLAIN 是只读的、低权限可用的诊断手段，可以放心在生产使用。 但要区分清楚它与 EXPLAIN ANALYZE 的差别：前者只看计划，后者真跑。

    sql
    -- 生产环境安全：只规划，不执行
EXPLAIN SELECT * FROM orders WHERE customer_id = 1;

  • 把 EXPLAIN 的结果与 pg_stat_statements 结合。 前者告诉你「单次执行怎么跑的」，后者告诉你「哪些语句累计最耗时」。

    sql
    SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

下一节

继续学习：EXPLAIN ANALYZE 实际执行

参考链接 ​