引言

"商家后台的经营看板每天早上 9 点准时雪崩——门店店长们上班第一件事就是打开看板看昨天的销量，接口 P99 直接飙到 10 秒以上，连接池被打满，连带下单接口都跟着超时。"

慢的是一条看似平平无奇的聚合 SQL：5000 万行的订单表，按门店和日期统计商品销量 Top20。开发同事最初的反应是"加个索引吧"，加完从 10 秒变成 800ms，以为收工——结果一周后看板又慢了，而且 EXPLAIN 显示索引建了却没完全用上。后续又陆续做了查询改写、分区裁剪、物化视图预聚合四步，最终稳定在 45ms，前后 200 倍差距。

这个案例最有价值的地方不是某一条神奇 SQL，而是一条完整的优化阶梯：每一步解决一个不同层面的瓶颈，每一步都有 EXPLAIN ANALYZE 的前后对比作为证据，而且每一步都有明确的适用边界——索引解决"扫得太多"、改写解决"索引用不上"、分区解决"表太大"、物化视图解决"重复计算"。这篇文章就按这条阶梯逐级推进，SQL 基于 PostgreSQL 14，数据量 5000 万行真实量级。读完你会掌握一套可复用的优化方法论：先用 EXPLAIN ANALYZE 定位时间到底花在哪，再按代价从低到高出牌，永远不要在没看执行计划的情况下"凭经验优化"。

前置阅读：怎么发现这条 SQL、怎么用 pg_stat_statements 按总耗时抓到它，见同系列《数据库慢查询定位：从慢日志到 EXPLAIN ANALYZE——PostgreSQL 篇》；分区表和索引的完整 DDL 细节分别见《PG 分区表实战》《PG 高级索引实战》，本文只讲与本次优化直接相关的部分。

一、全景：问题、环境与五级跳路线图

1.1 慢 SQL 与表结构

经营看板的核心查询：查某个门店某天各商品的销量排行。

SELECT product_id,
       count(*)           AS order_cnt,
       sum(quantity)      AS qty,
       sum(pay_amount)    AS gmv
FROM t_order
WHERE shop_id = 1024
  AND date(create_time) = date '2026-09-15'
  AND status = 'PAID'
GROUP BY product_id
ORDER BY gmv DESC
LIMIT 20;

表结构（简化）：

CREATE TABLE t_order (
    id            bigint PRIMARY KEY,
    order_no      varchar(32)  NOT NULL,
    shop_id       bigint       NOT NULL,   -- 门店
    product_id    bigint       NOT NULL,   -- 商品
    quantity      int          NOT NULL,
    pay_amount    numeric(12,2),
    status        varchar(16)  NOT NULL,   -- INIT/PAID/CANCEL/REFUND
    create_time   timestamp    NOT NULL
);
-- 初始状态：只有主键索引，无其他索引，未分区

数据画像（优化前必须先摸清，脱离数据分布谈优化都是耍流氓）：

目标：从 5000 万行里捞出约 300 行做聚合，理想情况下只该碰这 300 行——返回 20 行却扫描 5000 万行，这就是 10 秒的全部秘密。

1.2 五级跳路线图

10.2s  基线：Seq Scan 全表扫描 5000 万行 + HashAggregate
  │
  │ 第一跳：建复合索引 (shop_id, create_time, status)
  ▼
 820ms  索引能定位 shop_id，但 date() 函数包裹让 create_time 段用不上
  │
  │ 第二跳：改写谓词，去掉索引列上的函数
  ▼
 350ms  create_time 改范围条件，三列索引完整生效（Index Only Scan）
  │
  │ 第三跳：按月 RANGE 分区 + 分区裁剪
  ▼
 180ms  只扫 9 月分区，索引体积同步缩小
  │
  │ 第四跳：物化视图预聚合（门店×天×商品）
  ▼
  45ms  查询只读汇总表，45ms 里大头是网络往返和 LIMIT 排序

出牌顺序遵循一个原则：代价从低到高——加索引不需要改代码、改写 SQL 不改变结构、分区需要改表但对应用透明、物化视图引入数据延迟和运维成本。能用便宜方案解决的，绝不上贵的。

二、第 0 跳：EXPLAIN ANALYZE 基线，时间到底花在哪

2.1 先拿到完整计划

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT product_id, count(*), sum(quantity), sum(pay_amount)
FROM t_order
WHERE shop_id = 1024
  AND date(create_time) = date '2026-09-15'
  AND status = 'PAID'
GROUP BY product_id
ORDER BY gmv DESC
LIMIT 20;

三个参数缺一不可：ANALYZE 给真实执行时间和行数（不只是估算），BUFFERS 给缓存命中（区分"算力慢"还是"IO 慢"），时间用 \timing 或客户端统计。基线计划（简化并加注释）：

Limit  (actual time=10182.4..10182.5 rows=20 loops=1)
  ->  Sort  (actual time=10182.3..10182.4 rows=20 loops=1)
        Sort Key: (sum(pay_amount)) DESC
        Sort Method: top-N heapsort  Memory: 28kB           -- 排序只要28k，不是瓶颈
        ->  HashAggregate  (actual time=10140.7..10178.9 rows=418 loops=1)
              Group Key: product_id                          -- 聚合输出418个商品
              Batches: 1  Memory Usage: 80kB                 -- 聚合内存也毫无压力
              Buffers: shared hit=8192 read=486201           -- ★ 读了约 3.7GB 块！
              ->  Seq Scan on t_order
                    (actual time=0.05..8721.3 rows=312 loops=1)
                    Filter: ((date(create_time) = '2026-09-15')
                             AND (shop_id = 1024)
                             AND (status = 'PAID'))
                    Rows Removed by Filter: 49,999,688       -- ★ 5000万行逐行过滤，扔掉4999万
                    Buffers: shared hit=8192 read=486201     -- ★ 几乎全部是磁盘读
Planning Time: 0.3 ms
Execution Time: 10182.8 ms

2.2 三个决定性信号

1. Seq Scan on t_order + Rows Removed by Filter: 49,999,688：全表扫描，99.999% 的工作是无用功。没有任何索引可用。
2. Buffers: shared hit=8192 read=486201：约 49 万个块（8KB 块 ≈ 3.7GB）来自磁盘读，shared_buffers 命中极低——慢主要慢在 IO，不是 CPU。这解释了为什么每天早上第一次打开尤其慢（缓存是冷的）。
3. 聚合和排序都很便宜：HashAggregate 80kB 内存、top-N heapsort 28kB，work_mem 完全不是瓶颈。所以不要一看到聚合就去调 work_mem，先看扫描。

一句话定性：这是"扫描型"慢查询，优化方向是让数据库少扫行、少读块，而不是调大内存参数。

2.3 EXPLAIN 阅读心法（只讲本例要用的）

从下往上读、从右往左看：
  下层节点产出的行喂给上层节点；先找 actual time 最大、rows 反差最大的节点

重点看四组数字：
  cost=启动代价..总代价            估算值，用于比较计划优劣（不真实执行）
  actual time=启动..总计(ms)      真实耗时
  rows 估算 vs actual rows 实际    偏差大 = 统计信息过期或谓词无法估算（如函数）
  Buffers shared hit/read/dirtied 缓存命中/磁盘读/脏块，IO 问题的直接证据

三个危险信号：
  Seq Scan + 巨大 Rows Removed  → 缺索引或谓词不可索引
  rows 估算与 actual 差一个数量级 → ANALYZE 统计信息过期
  Sort Method: external merge / HashAggregate Batches>1 → work_mem 不足落盘

三、第一跳：复合索引，10.2s → 820ms

3.1 为什么不用三个单列索引

新手常见做法是给 shop_id、create_time、status 各建一个单列索引。PG 对它们最多做 BitmapAnd 组合：扫描多个索引 → 位图与运算 → 回表。能用，但要扫三个索引再随机回表，对这种"等值+范围+聚合"的组合远不如一个复合索引高效。

复合索引的列序是核心学问。结合本查询的谓词结构：

WHERE shop_id = 1024            -- 等值
  AND create_time 范围（当天）    -- 范围（改写后，下一跳详述）
  AND status = 'PAID'           -- 等值
SELECT product_id, quantity, pay_amount   -- 聚合需要的载荷列

列序口诀：等值列在前，范围列在后；排序/分组列可以蹭索引有序性；只查不筛的载荷列放 INCLUDE。

CREATE INDEX CONCURRENTLY idx_order_shop_time_status
    ON t_order (shop_id, create_time, status)
    INCLUDE (product_id, quantity, pay_amount);

• CONCURRENTLY：在线建索引，不锁表（生产环境必加，代价是建得慢一些）。
• 前两列 shop_id（等值）+ create_time（范围）是主力；status 放第三列，范围列之后的列无法继续做索引定位条件，但作为 Filter 代价极低——第二跳改写后我们还会调整它。
• INCLUDE（PG 11+）：把聚合需要的三个载荷列放进索引叶子页但不作为索引键，实现 Index Only Scan，免回表；载荷列不参与排序，不会撑大索引树的上层结构。

3.2 建完之后的计划：有进步，但没吃满

Limit  (actual time=818.7..819.9 rows=20 loops=1)
   ->  Sort  (actual time=818.6..819.7 rows=20 loops=1)
         ->  HashAggregate  (actual time=815.2..818.5 rows=418 loops=1)
               Buffers: shared hit=10234 read=87421
               ->  Index Scan using idx_order_shop_time_status on t_order
                     (actual time=0.4..742.6 rows=312 loops=1)
                     Index Cond: (shop_id = 1024)                    -- ★ 只用上了第一列！
                     Filter: ((date(create_time) = '2026-09-15')    -- ★ 日期和status退化成Filter
                              AND (status = 'PAID'))
                     Rows Removed by Filter: 184201                  -- 扫了18万行丢掉18.4万
                     Buffers: shared hit=10234 read=87421
 Planning Time: 0.3 ms
 Execution Time: 820.2 ms

收益分析：

• 从 Seq Scan 变成 Index Scan，磁盘读取从 49 万块（3.7GB）降到 9.7 万块（约 760MB），10.2s → 820ms，符合预期。
• 但注意 Index Cond 只有 shop_id = 1024：一个门店 3 年累积约 18 万行全部被扫出，再用 Filter 扔掉 18.4 万行。date(create_time) = ... 让 create_time 上的索引条件失效，status 又排在它后面，三列索引只用上一列。

这就是"索引建了却没完全用上"的典型现场。如果只看"耗时从 10 秒降到 800ms"就收工，随着单店数据继续增长，这个数字会按月线性恶化。

3.3 本跳原则

• 复合索引为"查询模式"设计，不是为"字段"设计：先把高频 SQL 的等值/范围/排序列列出来再定列序。
• INCLUDE 载荷列免回表，宽表收益尤其大，但别把大字段（text/jsonb）塞进去。
• 建索引后必须用 EXPLAIN 验证 Index Cond 是否符合预期，Filter 里残留本该被索引的条件 = 索引没有吃满。

四、第二跳：谓词改写，让索引列"干干净净"，820ms → 350ms

4.1 函数包裹为什么废掉索引

date(create_time) = '2026-09-15' 对索引列的值做了函数运算。B-tree 索引里存的是 create_time 原值（2026-09-15 14:23:01）的有序排列，而谓词比较的是 date() 转换后的结果——PG 无法把"函数结果等于某值"反推成"原值落在什么范围"（除非有匹配的表达式索引），于是这部分条件只能对每行现算现过滤。

这是最常见、也最冤的一类索引失效：索引明明就在那里。同类写法还有：

-- ✗ 索引列被函数/表达式包裹
WHERE to_char(create_time,'YYYY-MM-DD') = '2026-09-15'
WHERE amount * 100 > 10000
WHERE lower(shop_name) = 'abc'
WHERE coalesce(status,'INIT') = 'PAID'
WHERE id::text = '123'

-- ✓ 改成对裸列做常量区间/常量比较
WHERE create_time >= '2026-09-15' AND create_time < '2026-09-16'
WHERE amount > 100
WHERE shop_name = 'ABC'                          -- 或建 lower(shop_name) 表达式索引
WHERE status = 'PAID'                           -- 把 NULL 语义在数据层处理掉
WHERE id = 123

改写后的 SQL（注意右边界是次日零点的半开区间，不会漏掉 23:59:59.999，也不依赖边界是否包含）：

SELECT product_id, count(*), sum(quantity), sum(pay_amount)
FROM t_order
WHERE shop_id = 1024
  AND create_time >= timestamp '2026-09-15 00:00:00'
  AND create_time <  timestamp '2026-09-16 00:00:00'
  AND status = 'PAID'
GROUP BY product_id
ORDER BY gmv DESC
LIMIT 20;

4.2 顺手把索引列序也调整到最优

三列谓词现在是"等值 + 范围 + 等值"。把范围列放最后，让两个等值列都能走索引定位：

-- 更优列序：等值(shop_id) + 等值(status) + 范围(create_time)
CREATE INDEX CONCURRENTLY idx_order_shop_status_time
    ON t_order (shop_id, status, create_time)
    INCLUDE (product_id, quantity, pay_amount);

-- 确认旧索引无人使用后再删除（先观察 pg_stat_user_indexes.idx_scan）
DROP INDEX CONCURRENTLY idx_order_shop_time_status;

为什么列序这样排：B-tree 复合索引按列优先级全局有序，等值条件不消耗"有序性预算"，一旦遇到范围条件，其后的列在全局上不再连续有序、无法做索引定位。所以等值列全部前置、范围列收口。如果查询还有 ORDER BY create_time，则要在"让范围列参与排序"和"让等值列定位"之间权衡，以 EXPLAIN 实测为准。

4.3 改写后的计划：Index Only Scan

先看只做谓词改写、暂不调整索引列序（沿用 (shop_id, create_time, status)）时的计划：

Limit  (actual time=348.2..349.3 rows=20 loops=1)
   ->  Sort  (actual time=348.1..349.1 rows=20 loops=1)
         ->  HashAggregate  (actual time=344.6..348.0 rows=418 loops=1)
               Buffers: shared hit=312 read=146
               ->  Index Only Scan using idx_order_shop_time_status on t_order
                     (actual time=0.4..302.1 rows=312 loops=1)
                     Index Cond: (shop_id = 1024
                                  AND create_time >= '2026-09-15'
                                  AND create_time <  '2026-09-16')   -- ★ 日期进入索引条件
                     Filter: (status = 'PAID')                       -- 范围列之后的等值列仍是Filter
                     Rows Removed by Filter: 287
                     Heap Fetches: 599                                -- ★ 回表做MVCC可见性检查
                     Buffers: shared hit=312 read=146
 Planning Time: 0.3 ms
 Execution Time: 349.6 ms

再把索引列序调整为 (shop_id, status, create_time)（等值前置、范围收口）后，三列全部进入索引条件：

Index Only Scan using idx_order_shop_status_time on t_order
   Index Cond: (shop_id = 1024 AND status = 'PAID'
                AND create_time >= '2026-09-15' AND create_time < '2026-09-16')
   Rows Removed by Filter: 0
   Heap Fetches: 599          -- 回表仍在：大表频繁更新，可见性映射尚未全部置位
   Buffers: shared hit=308 read=141
 Execution Time: 342.1 ms

关键变化与残留成本：

• 谓词进入 Index Cond 后，扫描行数从 18 万降到 599（当天该店全部状态订单），status 过滤仅扔掉 287 行。
• 虽然载荷列已在 INCLUDE 中、计划是 Index Only Scan，但 Heap Fetches: 599 暴露了残留成本：订单表频繁更新，旧页面存在死元组，可见性映射（visibility map）未全部置位，PG 仍需回表判断每行版本可见性；叠加 5000 万行大索引的冷缓存遍历，350ms 里的大头就在这里。
• IO 量已从 9.7 万块降到几百块——这为下一跳分区（索引变小、数据变新、VM 全可见）留出了收益空间。

4.4 本跳原则

• 让索引列在谓词里保持"裸列"：裸列 比较 常量 是索引友好的唯一形态。
• 无法改写时（比如确实要按 date() 查），建表达式索引：CREATE INDEX ... ON t_order (shop_id, date(create_time), status)——为特定表达式量身定制，但通用性差，优先改写。
• 复合索引列序：等值前置、范围收口；改动列序后记得删旧索引，避免写放大和优化器混淆。
• 隐式类型转换也是隐形函数：varchar_col = 123（数字）会让索引失效，参数类型必须与列一致。

五、第三跳：分区裁剪，350ms → 180ms

5.1 索引已经很好了，分区还能带来什么

350ms 里有一部分来自索引树自身的体积：5000 万行的复合索引树高 4 层、叶子页数十万个，即使只取 312 行，树的遍历和缓存竞争依然存在；而且这张表还在以每月约 150 万行增长，索引只会越来越胖。

订单数据天然按时间冷热分明（看板基本只查近几个月，3 年前的单很少碰），按月 RANGE 分区让每次查询只扫当月分区：

-- 建成分区表（生产迁移用在线方式：建新表→分批回填→切换，勿直接 ALTER）
CREATE TABLE t_order (
    id bigint NOT NULL,
    order_no varchar(32) NOT NULL,
    shop_id bigint NOT NULL,
    product_id bigint NOT NULL,
    quantity int NOT NULL,
    pay_amount numeric(12,2),
    status varchar(16) NOT NULL,
    create_time timestamp NOT NULL
) PARTITION BY RANGE (create_time);

-- 每月一个分区（自动建分区的函数+pg_cron 见《PG分区表实战》）
CREATE TABLE t_order_202609 PARTITION OF t_order
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
-- ... 其余月份分区

-- 索引在分区表上声明，PG 自动在每个分区建本地索引
CREATE INDEX CONCURRENTLY idx_order_shop_status_time
    ON t_order (shop_id, status, create_time)
    INCLUDE (product_id, quantity, pay_amount);

5.2 分区裁剪的计划长什么样

Limit  (actual time=178.4..179.6 rows=20 loops=1)
   ->  Sort  (actual time=178.3..179.4 rows=20 loops=1)
         ->  HashAggregate  (actual time=176.1..178.2 rows=418 loops=1)
               ->  Append  (actual time=0.3..174.8 rows=312 loops=1)
                     Subplans Removed: 35                  -- ★ 36个月分区剪掉35个
                     ->  Index Only Scan using t_order_202609_shop_status_create_idx
                           on t_order_202609               -- ★ 只扫9月分区
                           Index Cond: (shop_id=1024 AND status='PAID'
                                        AND create_time >= '2026-09-15'
                                        AND create_time <  '2026-09-16')
                           Heap Fetches: 0                  -- ★ 当月新数据VM全可见，零回表
 Planning Time: 0.8 ms
 Execution Time: 180.3 ms

Subplans Removed: 35 就是分区裁剪（partition pruning）生效的铁证：优化器根据 create_time 的常量区间，在规划阶段直接排除了 35 个不相关分区。9 月分区只有约 150 万行，本地索引树更矮（高 3 层）、热缓存常驻内存；更关键的是当月数据刚写入不久、autovacuum 及时，可见性映射全部置位，第二跳残留的 Heap Fetches: 599 归零——两个因素叠加，350ms → 180ms。

5.3 让裁剪生效的两个必要条件

1. 查询条件必须用分区键的裸列——第二跳的改写在这里再次立功：date(create_time)=... 不仅废掉索引，也会让分区裁剪失效（对分区键做函数，PG 无法推断落在哪个区间）。一个改写同时解决两个问题，这也是为什么谓词改写必须排在分区之前。
2. 预编译/参数化场景用 enable_partition_pruning（默认 on）+ 常量折叠。MyBatis/JDBC 传入的是绑定参数时 PG 同样能裁剪（执行期裁剪，计划里显示 subplans removed）；但如果分区键来自函数/子查询结果，规划期无法裁剪，只能靠执行期裁剪，复杂场景要用 EXPLAIN 确认。

5.4 分区不是免费的午餐

• 分区数不是越多越好：单表几百个分区会让规划时间显著上升（本例 36 个分区 Planning Time 从 0.3ms 涨到 0.8ms，尚可；上千分区时可达几十毫秒）。按月分区对订单类业务是甜点区。
• 分区表上的唯一约束必须包含分区键（主键要改成 PRIMARY KEY (id, create_time)），跨分区全局唯一索引不存在——应用层要保证 order_no 全局唯一。
• 收益对"索引已经很快"的查询有限（本例只快了一倍），分区的更大价值在数据生命周期管理：旧数据按月 DETACH/归档、DROP 分区秒级清理，比 DELETE 5000 万行优雅得多。

5.5 本跳原则

• 时间序列大表优先考虑 RANGE 分区，分区粒度与查询窗口匹配（按天查就按周/月分，别按小时）。
• 分区裁剪与索引共用同一条铁律：分区键和索引列都不能被函数包裹。
• 分区前先评估分区数量对规划时间的影响，并设计好新分区自动创建、旧分区归档的运维机制。

六、第四跳：物化视图预聚合，180ms → 45ms

6.1 思路转换：与其每次算，不如提前算好

到 180ms 这一步，扫描已经精简到极致（312 行、几个块）。但看板的查询模式有个特点：每天上午几千个店长查的都是"昨天"那批已经定型的数据——同一批行被反复聚合几千遍。 重复计算是最后的浪费，用物化视图（Materialized View）把聚合结果存下来：

CREATE MATERIALIZED VIEW mv_order_shop_daily AS
SELECT shop_id,
       create_time::date                     AS dt,
       product_id,
       status,
       count(*)                              AS order_cnt,
       sum(quantity)                         AS qty,
       sum(pay_amount)                       AS gmv
FROM t_order
GROUP BY shop_id, create_time::date, product_id, status;

-- 刷新用的唯一索引（CONCURRENTLY 刷新的必要条件），同时也是查询索引
CREATE UNIQUE INDEX uk_mv_shop_dt_prod_status
    ON mv_order_shop_daily (shop_id, dt, product_id, status);

看板 SQL 改成查物化视图：

SELECT product_id, order_cnt, qty, gmv
FROM mv_order_shop_daily
WHERE shop_id = 1024
  AND dt = date '2026-09-15'
  AND status = 'PAID'
ORDER BY gmv DESC
LIMIT 20;

结果集完全相同，而 mv 全表只有"门店 × 天 × 商品 × 状态"量级（约几千万行表聚合后几百万行），查询走唯一索引的等值前缀：

Limit  (actual time=43.1..44.8 rows=20 loops=1)
   ->  Index Scan Backward using uk_mv_shop_dt_prod_status
         on mv_order_shop_daily  (actual time=0.04..0.2 rows=20 loops=1)
         Index Cond: (shop_id = 1024 AND dt = '2026-09-15' AND status = 'PAID')
 Planning Time: 0.2 ms
 Execution Time: 45.1 ms

45ms 里大部分是网络往返和客户端处理，数据库侧不到 1ms。五级跳到此完成：10182ms → 45ms，约 226 倍。

6.2 刷新策略：数据新鲜度的三种选择

物化视图的代价是数据不是实时的，必须设计刷新机制。按业务对时效的要求选型：

本案例用 pg_cron 每天凌晨并发刷新（当天数据还在变，实际调度为每小时刷近 7 天分区）：

-- 需要 pg_cron 扩展；并发刷新不阻塞看板查询
SELECT cron.schedule('refresh-mv-order-daily', '7 */1 * * *',
  $$ REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_shop_daily $$);

并发刷新的两个注意点：① 必须有上文的唯一索引；② 它通过"计算新版本 + upsert 差异"实现，刷新期间 IO 和 WAL 开销比普通刷新大，不要在业务高峰跑。

对分钟级时效有要求的场景，物化视图就不合适了，更常用的是手写增量汇总表：每 15 分钟把这段时间的订单聚合后 upsert 进日汇总表，思路与 mv 一致但增量计算、成本可控。

6.3 本跳原则

• 物化视图/汇总表消灭的是"重复计算"，适用于高频、固定模式、可接受延迟的聚合分析，是报表/看板类查询的终点站。
• 刷新即成本：全量刷新锁表、CONCURRENTLY 耗 IO、增量汇总表要自己维护一致性——选型先回答"业务能接受几分钟延迟"。
• 不要给低频的即席分析上物化视图：为每个查询建一个 mv，存储和刷新运维会失控。这类需求更适合列存（如 ClickHouse / PG 列存扩展）。

七、优化过程中的两个"免费午餐"

五级跳之外，调优时还顺手处理了两个不改变结构、成本极低的问题，它们也经常是别人的主瓶颈：

7.1 统计信息过期：rows 估算严重偏差

优化初期曾在测试库看到另一个离谱计划：索引扫描估算 rows=58000，actual rows=312，偏差近 200 倍，优化器因此放弃索引选了全表。原因是造数后没跑 ANALYZE：

ANALYZE t_order;                          -- 全表分析
ANALYZE t_order (shop_id, status);        -- 数据倾斜严重的列单独多采点
ALTER TABLE t_order ALTER COLUMN shop_id SET STATISTICS 1000;  -- 提高采样桶数(默认100)

判断方法：EXPLAIN ANALYZE 中任何节点的估算 rows 与 actual rows 差一个数量级以上，先 ANALYZE 再说话。autovacuum 正常工作的库一般不用手动处理，但大批量导入/更新后要手动触发。

7.2 work_mem：聚合/排序落盘

另一类看板 SQL（不 LIMIT 的全量导出）曾出现：

Sort Method: external merge  Disk: 8240kB          -- 排序落盘
HashAggregate  Batches: 24  Memory Usage: 4096kB    -- 聚合分批落盘

这是 work_mem（默认 4MB）不足的典型证据，调大即可：

-- 会话级先验证（注意是"每个排序/哈希节点"一份，不是总内存，不能全局乱调）
SET work_mem = '64MB';
-- 验证有效后，在连接池配置或数据库级按机器内存/并发数谨慎设置

本例的 Top20 查询始终是 heapsort 28kB，所以前四跳完全不需要动 work_mem——再次强调：BUFFERS 说 IO 是瓶颈时调内存参数没用，看到 Disk/Batches 才轮到 work_mem。

八、完整优化效果与决策原则

8.1 五级跳总账

8.2 决策树：一条慢聚合查询怎么出牌

EXPLAIN (ANALYZE, BUFFERS) 先定性
│
├─ Seq Scan + Rows Removed 巨大？
│   ├─ 谓词列有函数/隐式转换？ ──→ 先改写为裸列条件（最便宜，先做）
│   ├─ 建复合索引：等值前置、范围收口、载荷 INCLUDE
│   └─ rows 估算偏差大？ ──→ ANALYZE / 提高 STATISTICS
│
├─ 计划合理但涉及多个时间段、表持续膨胀？
│   └─ 时间分区 + 确认 Subplans Removed（同时获得归档能力）
│
├─ Sort external merge / HashAggregate Batches>1？
│   └─ 会话级验证 work_mem，谨慎固化
│
├─ 查询本身只碰几百行但仍嫌慢，且被高频重复执行？
│   └─ 物化视图 / 增量汇总表（接受延迟换性能）
│
└─ 即席分析、查询模式不固定、数据量持续暴涨？
    └─ 不要堆索引和 mv，考虑列存/数仓分流

三条贯穿始终的原则：

1. 让数据库少干活，而不是让它干得更快：10 秒里 8.7 秒是扫 5000 万行，消除扫描比提升扫描速度有效得多。
2. 每层手段解决一类问题，按成本出牌：索引 ≠ 万能，分区 ≠ 加速银弹，物化视图 ≠ 实时；顺序反了会花大代价解决小问题。
3. 所有优化以 EXPLAIN ANALYZE 的证据闭环：改一次看一次计划，确认瓶颈节点真的消失、没有引入新节点（如分区后规划时间暴涨），再进入下一步。

九、常见问题

9.1 加了复合索引为什么优化器还是选全表扫描？

按概率排序：① 表很小或统计信息认为走索引回表成本更高（小表 Seq Scan 本来就更快，别强制）；② 统计信息过期，rows 估算失真——ANALYZE 后再看；③ 谓词对索引列用了函数/类型转换；④ 用了前导列之外的条件（复合索引最左前缀）；⑤ 选择性太差（如 status='PAID' 命中 90% 行，回表随机 IO 比顺序扫还贵）。可以用 SET enable_seqscan=off 在会话里验证"索引路径本身快不快"，但生产永远不要用这个参数强制，它只用于诊断。

9.2 INCLUDE 和把列直接放进复合索引键有什么区别？

索引键列参与 B-tree 的排序和查找，键列越多，树的每层越大、插入维护成本越高，且范围列之后的键列无法用于定位；INCLUDE 的载荷列只存在于叶子页、不影响树结构，专门为覆盖索引（Index Only Scan）服务。判断标准：参与 WHERE/JOIN/ORDER BY 的列放键里，只在 SELECT 列表里出现的载荷列放 INCLUDE。

9.3 为什么我的分区表没有发生分区裁剪？

逐项检查：① WHERE 里是不是对分区键用了函数（date(create_time)=... 不裁剪，范围条件裁剪）；② 条件值类型是否与分区键一致；③ 用了 PreparedStatement 时 PG 支持执行期裁剪，但部分旧版本/复杂子查询只能规划期裁剪——用 EXPLAIN 看有没有 Subplans Removed；④ 分区是否真的覆盖了数据（DEFAULT 分区会吃掉不匹配的数据，且永远无法被裁剪）。

9.4 物化视图和直接建汇总表、加 Redis 缓存怎么选？

物化视图由数据库维护、支持 CONCURRENTLY 刷新和查询重写生态，适合固定聚合模式、DBA 可控；手写汇总表（配合定时增量 upsert）灵活、可做准实时，但一致性和调度要自己负责；Redis 缓存把结果放到应用侧，适合结果集小、键值模型简单的 Top 榜，且要处理缓存与数据库的一致性和穿透。本案例选物化视图是因为聚合维度固定、T+1/小时级延迟业务完全接受。三者都无法替代对原始明细查询的索引优化。

9.5 索引是不是越多越好？宽表建十几个索引有什么代价？

读爽写疼：每个索引都要在 INSERT/UPDATE/DELETE 时同步维护（额外 WAL、真空成本、热块竞争），占用磁盘和 shared_buffers，批量写入吞吐可能下降一半以上。订单表这种写多读也多的表，索引应按"高频查询模式"收敛，定期用 pg_stat_user_indexes 查 idx_scan=0 的僵尸索引清理，用 pg_stat_statements 验证新索引是否真的被命中。

9.6 优化完上线要注意什么？

① 索引用 CREATE INDEX CONCURRENTLY，不要在业务高峰建（即使用了 CONCURRENTLY，大索引也吃 IO）；② 分区表迁移走"建新表→分批回填→追平增量→事务内切换 rename"的在线方案；③ 物化视图首次刷新全表扫描，在低峰执行；④ 所有改动先在等体量数据的预发库用真实参数验证计划，测试库数据量小，计划可能完全不同；⑤ 上线后观察 pg_stat_statements 中该 SQL 的 mean_time 和 Buffer 命中，确认收益而非只看单次执行。

十、总结

五级跳速查卡

【基线定性】EXPLAIN (ANALYZE, BUFFERS)
  Seq Scan + Rows Removed 49,999,688 + 3.7GB 磁盘读 = 扫描型慢查询
  聚合 Sort 28kB / HashAgg 80kB 均正常 → 别动 work_mem

【第一跳 10.2s→820ms】复合索引
  CREATE INDEX CONCURRENTLY ... ON t_order(shop_id, create_time, status)
    INCLUDE(product_id, quantity, pay_amount)
  发现 Index Cond 只有 shop_id：函数包裹让后续列失效

【第二跳 820ms→350ms】谓词改写 + 索引列序
  date(create_time)=x  →  create_time >= 当天 AND < 次日（半开区间）
  列序改为 (shop_id, status, create_time)：等值前置、范围收口
  计划变 Index Only Scan，Heap Fetches=0，Buffers 10 块

【第三跳 350ms→180ms】RANGE 月分区
  PARTITION BY RANGE(create_time) + 每分区本地索引
  计划 Append + Subplans Removed: 35 = 裁剪生效
  附带能力：旧分区 DROP/归档秒级完成

【第四跳 180ms→45ms】物化视图
  MATERIALIZED VIEW 门店×天×商品×状态 预聚合 + 唯一索引
  pg_cron 定时 REFRESH ... CONCURRENTLY（不阻塞读）
  适用：高频固定聚合 + 可接受分钟/T+1 延迟

【免费午餐】
  rows 估算失准 → ANALYZE；倾斜列 SET STATISTICS 1000
  external merge / Batches>1 → 才调 work_mem

给团队的建议

一句话

慢查询优化没有银弹，只有一套"用证据定位、按成本出牌"的方法论：这条 5000 万行订单表上的聚合 SQL，EXPLAIN (ANALYZE, BUFFERS) 一眼定性——Seq Scan 扔掉 4999 万行、3.7GB 磁盘读，是典型的扫描型慢查询，聚合排序只用几十 kB 内存，调 work_mem 纯属南辕北辙；第一跳用 (shop_id, create_time, status) INCLUDE 载荷列的复合索引把 10.2 秒打到 820ms，但 EXPLAIN 里 Index Cond 只有 shop_id 暴露了关键问题——date() 函数包裹让索引列变成 Filter；第二跳把谓词改成 create_time 半开区间、索引列序调整为"等值前置、范围收口"，三列全部进入 Index Cond 并实现 Heap Fetches=0 的 Index Only Scan，820ms 到 350ms；第三跳按月 RANGE 分区让计划出现 Subplans Removed: 35 的裁剪效果，350ms 到 180ms，同时获得旧数据秒级归档的生命周期能力；第四跳针对看板"几千人重复聚合同一批定型数据"的特点，用门店×天×商品维度的物化视图预聚合加 CONCURRENTLY 定时刷新，把重复计算彻底消灭，180ms 到 45ms。五级跳背后是同一种思维方式：索引解决谓词不可定位、改写解决索引列不可见、分区解决扫描范围不可剪、物化视图解决计算结果可复用，再加上 ANALYZE 治统计失准、work_mem 只在落盘证据出现时出手——永远先看执行计划里时间和块到底花在哪个节点，让每一步改动都由 Rows Removed、Index Cond、Buffers、Subplans Removed 这几个硬指标验收，性能优化就从"凭感觉调参"变成了可复现的工程实践。

互动话题：你优化过的最慢的一条 SQL 是多少秒到多少毫秒？最后起决定作用的是索引、改写、分区还是物化视图？有没有遇到过"索引建了但因为函数包裹/隐式转换完全没生效"的坑？评论区贴出你的 EXPLAIN 关键片段，一起诊断。

参考资料

• PostgreSQL 官方文档：Using EXPLAIN（执行计划解读）
• PostgreSQL 官方文档：Indexes — CREATE INDEX / INCLUDE / 表达式索引
• PostgreSQL 官方文档：Table Partitioning（声明式分区与裁剪）
• PostgreSQL 官方文档：Materialized Views（REFRESH CONCURRENTLY）
• PostgreSQL 官方文档：Server Statistics（pg_stat_statements / pg_stat_user_indexes）
• pg_cron 扩展（定时刷新/分区维护）
• 同系列：数据库慢查询定位——从慢日志到 EXPLAIN ANALYZE（PostgreSQL 篇）
• 同系列：PG 分区表实战——千万级数据的查询优化
• 同系列：PG 高级索引实战——GIN/GiST/BRIN/Btree