PostgreSQL 查询优化：通过结构重写突破性能瓶颈
除了调整索引、内存参数或 JOIN 顺序外，还有更深层的优化手段。有时候执行计划看起来并无大碍，每个算子都在正常工作，但 SQL 的原始形态却提出了低效的问题，导致产生巨大的中间结果集，或者迫使规划器走上一条本可避免的低效路径。
这些改写操作不改变查询的最终返回值，仅改变 PostgreSQL 计算数据的方式。识别常见模式后，大部分改写是机械性的——如果原形式匹配 X，则改为 Y——往往能带来数量级的性能提升，且几乎没有副作用。
偏移量分页迁移至键集分页
这是本文中最具影响力的改写。OFFSET N LIMIT M 是大多数 ORM 和 REST API 框架的默认分页方式。然而一旦用户深度翻页，它就变成了性能陷阱。若要获取 50 万行数据中的第 1000 页（每页 20 条），PostgreSQL 必须读取并丢弃前 19,980 行。
在拥有 50 万行的 order_info 表上测试"第 4800 页，每页 20 条"：
SELECT id, customer_id, dt
FROM order_info
ORDER BY dt DESC
LIMIT 20 OFFSET 48000;
传统 OFFSET 执行计划概览：
Limit (cost=... rows=20 width=...) (actual time=1900.7..1900.7 rows=20 loops=1)
Buffers: shared hit=481693 read=1775
-> Index Scan Backward using idx_order_info_dt
Index Cond: true
Execution Time: 1900.750 ms
耗时 1.9 秒仅为了返回 20 行数据。扫描逆向遍历了索引，访问了大量堆元组进行可见性检查，并丢弃了其中绝大多数。对于如此微小的结果集，这相当于消耗了几 GB 的页面流量。
解决方案是采用键集分页（Keyset Pagination）：不再使用 OFFSET，而是记录上一页最后一条记录的游标值，要求获取小于该值的行：
-- 传递上一页最后一条记录的 (dt, id) 组合
SELECT id, customer_id, dt
FROM order_info
WHERE dt < '2024-03-01'::timestamptz
OR (dt = '2024-03-01'::timestamptz AND id < 100234)
ORDER BY dt DESC, id DESC
LIMIT 20;
优化后效果：
Limit (actual time=0.979..1.014 rows=20 loops=1)
Buffers: shared hit=22 read=1
-> Index Scan Backward using idx_order_info_dt
Index Cond: (dt < '2024-03-01'::timestamptz)
Execution Time: 1.032 ms
耗时仅为 1 毫秒，命中缓冲区极少。Index Cond 使得规划器能从游标位置开始扫描，无需跳过无关数据。无论翻到第一页还是第十页，成本几乎一致。
关键点提示：
- 复合游标保证唯一性：仅按时间排序可能存在多条同名时间的数据，生产环境建议加上主键作为辅助排序：
ORDER BY dt DESC, id DESC。 - 索引需匹配排序：确保索引顺序与
ORDER BY一致，否则可能导致回退到内存排序，抵消键集优势。 - 放弃随机跳转语义：键集分页适用于流式加载，若业务强依赖"跳转到第 N 页"，则仍需考虑 Offset 方案或重构数据结构。
相关标量子查询转为聚合关联
位于 SELECT 列表中的标量子查询对外部每一行都执行一次（计划中显示为 SubPlan N）。外部集合越大，开销呈 O(n²) 爆炸式增长。优化思路是将子查询预聚合，再通过 LEFT JOIN 合并：
-- 改写前：每次循环用户都要查询订单子句
SELECT u.user_id,
u.email,
(SELECT count(*) FROM order_info o
WHERE o.user_id = u.user_id AND o.status = 'pending') AS pending_count
FROM t_users u
WHERE u.is_active = true;
-- 改写后：单次聚合，然后左连接
SELECT u.user_id, u.email, COALESCE(p.pending_count, 0) AS pending_count
FROM t_users u
LEFT JOIN (
SELECT user_id, count(*) AS pending_count
FROM order_info
WHERE status = 'pending'
GROUP BY user_id
) p ON p.user_id = u.user_id
WHERE u.is_active = true;
改写后的版本先对订单表进行一次全量聚合，再与用户表连接。当外层行数巨大时，改写通常能提升 20 到 100 倍速度。
针对"每个外层对象取前 N 个关联行"的场景（非统计计数），应使用 LATERAL JOIN 配合 LIMIT N。
将 NOT IN 替换为 NOT EXISTS
这是 SQL 中最隐蔽的陷阱之一。只要子查询中包含单个 NULL 值，NOT IN 就会返回空结果集。逻辑上 x NOT IN (a, b, NULL) 等价于 x <> a AND x <> b AND x <> NULL，而 x <> NULL 的结果未知，导致整行被排除。
-- 风险写法：若内层查询有任意 email 为 NULL，结果为空
SELECT * FROM users
WHERE email NOT IN (SELECT email FROM unsubscribed_list);
-- 安全写法：支持三值逻辑
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM unsubscribed_list s WHERE s.email = u.email);
NOT EXISTS 基于存在性判断，不受 NULL 干扰。两者生成的执行计划也不同，NOT EXISTS 常转换为 Anti Semi Join，效率更高。除非确认子查询列绝对非空，否则在生产代码中统一使用 NOT EXISTS。
DISTINCT 与 GROUP BY 的选择
SELECT DISTINCT 用于去重，相同列的 GROUP BY 也能达到同样目的。若无聚合函数需求，两者功能等价。但 GROUP BY 更具灵活性，能与 HAVING、窗口函数配合得更好。
需要特别注意 DISTINCT ON 的使用场景："每个用户的最新订单"常被错误写成：
-- 潜在问题：无 ORDER BY 指定哪一行被保留
SELECT DISTINCT ON (customer_id) customer_id, order_id, dt
FROM order_info;
正确的做法是配合 ORDER BY 明确规则，通常希望保留最大值或最小值：
SELECT DISTINCT ON (customer_id) customer_id, order_id, dt
FROM order_info
ORDER BY customer_id, dt DESC;
此写法配合 (customer_id, dt DESC) 索引可执行索引扫描，直接产出每组一行，无需额外排序。
大数据量的批量删除与更新
大规模 DELETE 或 UPDATE 会锁定大量行，生成相应比例的 WAL，甚至引发自动真空风暴。千万级数据的单次删除可能锁住写入通道数分钟。应将任务分片执行：
-- 危险操作：试图一次性删除所有过期日志
-- DELETE FROM logs WHERE created_at < now() - interval '90 days';
-- 安全方案：循环提交，释放锁
DO $$
DECLARE
row_deleted int;
BEGIN
LOOP
DELETE FROM logs
WHERE log_id IN (
SELECT log_id FROM logs
WHERE created_at < now() - interval '90 days'
LIMIT 5000
);
GET DIAGNOSTICS row_deleted = ROW_COUNT;
EXIT WHEN row_deleted = 0;
COMMIT; -- 提交事务释放锁
END LOOP;
END $$;
使用 LIMIT + IN (SELECT ...) 是因为标准 PG 语法不支持 DELETE ... LIMIT。每片提交一次，允许 autovacuum 在批次间处理脏页。更新操作同理，根据并发负载设置批量大小。
Upsert 写法：INSERT ... ON CONFLICT
旧代码常采用读后再写的模式，存在竞态条件：
-- 伪代码示例
SELECT 1 FROM t_users WHERE email = $1;
-- 应用层逻辑：若不存在则插入
-- INSERT INTO t_users (email, ...) VALUES ($1, ...);
两次网络往返，两个会话可能同时读到"不存在"，导致重复插入冲突。PostgreSQL 的标准用法是利用 ON CONFLICT：
INSERT INTO t_users (email, username, status)
VALUES ($1, $2, 'active')
ON CONFLICT (email) DO UPDATE
SET username = EXCLUDED.username,
status = 'active'
RETURNING user_id;
单次往返，原子操作，无竞态。EXCLUDED 引用即将插入但因冲突而未插入的行。若只需忽略重复，可使用 DO NOTHING。冲突目标必须有唯一索引。
显式列出所需字段
虽非逻辑重写，但 SELECT * 在宽表场景下代价巨大。它强制拉取所有列，增加 IO 开销，使索引覆盖扫描退化为普通扫描（需回表），加剧排序内存消耗。此外，上游表结构变更可能导致下游代码隐式断裂。生产环境应始终显式命名所需列，仅对调试或导出工具例外。
HAVING 与 WHERE 的过滤时机
HAVING 在聚合后过滤，WHERE 在聚合前过滤。若谓词可在聚合前判定，务必前置，以减少参与聚合的数据量。
-- 低效：聚合所有订单，再筛选用户 ID
SELECT user_id, count(*) as cnt
FROM order_info
GROUP BY user_id
HAVING user_id IN (SELECT id FROM active_users);
-- 高效：先筛选活跃用户，再聚合
SELECT user_id, count(*) as cnt
FROM order_info
WHERE user_id IN (SELECT id FROM active_users)
GROUP BY user_id;
仅在涉及聚合结果的过滤（如 count(*) > 5）时才放 HAVING。规划器有时会尝试推演，但显式写在 WHERE 中更稳妥。
组合重写案例
现实查询往往混合多种反模式。例如"获取总消费最高的前 100 名用户各自的最新 20 单"：
-- 初级写法：全量计算排名，再用窗口函数
WITH top_users AS (
SELECT user_id
FROM order_info
GROUP BY user_id
ORDER BY sum(amount_cents) DESC
LIMIT 100
)
SELECT * FROM (
SELECT o.*, row_number() OVER(PARTITION BY user_id ORDER BY dt DESC) AS rn
FROM order_info o
WHERE user_id IN (SELECT user_id FROM top_users)
) t
WHERE rn <= 20;
窗口函数会对这 100 人的所有订单打分，工作量巨大。
优化方案结合 CTE 与 LATERAL：
WITH top_users AS (
SELECT user_id, sum(amount_cents) AS total_spend
FROM order_info
GROUP BY user_id
ORDER BY 2 DESC
LIMIT 100
)
SELECT t.user_id, t.total_spend, recent.*
FROM top_users t,
LATERAL (
SELECT order_id, amount_cents, dt
FROM order_info
WHERE user_id = t.user_id
ORDER BY dt DESC
LIMIT 20
) recent;
每个 Top 用户触发一次子查询，最多返回 2000 行，远低于全量计算。PG 15+ 虽能优化部分窗口函数场景，但 LATERAL 语义更清晰且稳定。
验证改写的必要性
任何重写都伴随改变边缘行为的微小风险。部署前需执行以下检查：
- 结果比对：新旧查询在同一数据集运行，核对行数及样本数据一致性。
- 执行计划分析：使用
EXPLAIN ANALYZE验证预期的成本下降，若未改善则排查边界情况。 - 压测验证：离线基准测试无法完全模拟缓存与并发效应，上线前后对比真实环境的响应时间。
索引之后、扩容之前，优先尝试查询重构。对于构建本身错误的查询，建议参考专门的反模式文档进一步诊断。
