带聚合子查询的分页SQL性能问题主因是“先聚合、后分页”，导致全量计算后再丢弃99%结果；应改用JOIN预聚合或键集分页，并建立覆盖索引优化。
直接结论：带聚合子查询的分页 SQL，90% 的性能问题出在“先聚合、后分页”这个顺序上——它强制数据库对全量数据执行聚合计算，再丢弃 99% 的结果。必须把分页逻辑前置到聚合之前，或用键集分页替代 OFFSET。
为什么 EXPLAIN ANALYZE 显示 “Subquery Scan” + “Limit” 耗时极高？
这是典型症状：PostgreSQL 先执行完整个子查询（含 GROUP BY、SUM 等），生成一个中间结果集，再对其应用 LIMIT/OFFSET。即使你只想要第 101–110 行，它也得算出全部 10 万行的聚合值。
- 子查询中若含
GROUP BY或窗口函数，且外层有LIMIT OFFSET，优化器几乎无法下推分页条件 -
EXPLAIN ANALYZE中看到Subquery Scan节点耗时占比 >85%，基本可确认是这个问题 - 特别注意：CTE 会加剧该问题——
WITH定义的 CTE 默认物化，相当于强制先算完再分页
用 JOIN 替代 SELECT 列中的聚合子查询（最常用且见效快）
把原本写在 SELECT 列里的标量子查询，改写为 LEFT JOIN + GROUP BY，让聚合与主表关联同步进行，避免逐行调用。
低效写法：
SELECT u.id, u.name, (SELECT SUM(o.amount) FROM orders o WHERE o.user_id = u.id AND o.status = 'paid') AS paid_total, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id AND o.created_at > '2026-01-01') AS recent_orders FROM users u ORDER BY paid_total DESC LIMIT 20 OFFSET 100;
优化后：
SELECT u.id, u.name, COALESCE(p.paid_total, 0) AS paid_total, COALESCE(r.recent_orders, 0) AS recent_orders FROM users u LEFT JOIN ( SELECT user_id, SUM(amount) AS paid_total FROM orders WHERE status = 'paid' GROUP BY user_id ) p ON p.user_id = u.id LEFT JOIN ( SELECT user_id, COUNT(*) AS recent_orders FROM orders WHERE created_at > '2026-01-01' GROUP BY user_id ) r ON r.user_id = u.id ORDER BY paid_total DESC LIMIT 20 OFFSET 100;
- 每个子查询独立聚合，且可被索引加速（如
orders(user_id, status)） - 避免了相关子查询的 N+1 执行问题
- 但注意：
OFFSET 100仍需跳过前 100 行，大数据偏移量下仍有压力
用键集分页（Keyset Pagination）彻底绕过 OFFSET
当排序字段（如 paid_total）有唯一性或高区分度时，用上一页最后一条记录的值作为游标，比 OFFSET 高效一个数量级。
- 将
LIMIT 20 OFFSET 100改为LIMIT 20 WHERE paid_total - 要求排序字段有索引，且最好加上主键防重复（如
ORDER BY paid_total DESC, u.id DESC） - 首次分页仍需完整扫描，但后续所有翻页都只查满足条件的前 20 行，不跳过任何数据
- 不支持“跳转到第 N 页”，但对无限滚动、下拉加载等场景更健壮
容易被忽略的复合索引陷阱
聚合子查询的性能，极度依赖是否命中覆盖索引。仅给 user_id 建索引远远不够。
- 对
SELECT user_id, SUM(amount) FROM orders WHERE status = 'paid' GROUP BY user_id，最优索引是：CREATE INDEX idx_orders_status_uid_amt ON orders (status, user_id) INCLUDE (amount); -
INCLUDE把amount加入索引叶子节点，避免回表；前导列status必须放最左（因 WHERE 先过滤） - 若排序字段参与聚合（如
ORDER BY MAX(created_at)），索引需包含该字段并匹配排序方向 - 别忘了定期运行
VACUUM ANALYZE orders;，否则统计信息过期会导致优化器选错索引
真正卡住的从来不是语法本身，而是聚合与分页这两个操作在执行计划里谁先谁后。只要分页还套在聚合外面，再好的索引也救不了全量计算的开销。键集分页不是“可选项”，而是处理百万级聚合分页时的事实标准。
