直接用 limit offset 在大数据量下变慢，因需扫描并丢弃前 offset 行，耗时线性增长且易漏/重数据；须用确定性排序（如 order by status, updated_at desc, id）和 row_number() 窗口函数配合覆盖索引实现高效稳定分页。
为什么直接用 LIMIT OFFSET 在大数据量下会变慢
因为 PostgreSQL 必须先扫描并丢弃前 OFFSET 行，哪怕你只要第 10001 条到第 10020 条，它也得把前面一万个结果全排好、再扔掉。随着页码增大，耗时线性增长，CPU 和 I/O 压力都会上升。
更隐蔽的问题是：如果数据在查询过程中被并发插入或更新，LIMIT 20 OFFSET 10000 可能漏掉记录，或者重复返回同一条——因为排序字段（比如 updated_at）相同，物理顺序不固定。
- 必须有确定性排序：仅靠
ORDER BY updated_at DESC不够，要补上唯一列（如id）作为决胜键 -
OFFSET越大，执行计划越容易退化为顺序扫描，即使有索引也可能被忽略 - 无法单次查出总行数，业务端得额外跑
COUNT(*)，增加一次全表或索引扫描
用 ROW_NUMBER() 实现稳定分页的写法要点
核心逻辑是：先编号，再过滤。但编号必须基于「已筛选、已排序」的数据集，否则序号就失去意义。
正确结构长这样：
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY status, updated_at DESC, id) AS rn
FROM tasks
WHERE status IN ('pending', 'processing') -- ✅ 过滤条件放最内层
)
SELECT id, status, updated_at
FROM ranked
WHERE rn BETWEEN 41 AND 60; -- ✅ 分页范围在外层
- 千万别把
WHERE status = ...放外层：那样ROW_NUMBER()是对全表编号，rn = 41可能对应一个completed记录 -
ORDER BY必须包含唯一列（如id），否则相同status和updated_at下，ROW_NUMBER()分配顺序非确定，翻页可能错位 - 避免多层嵌套子查询：每多一层括号，优化器越难下推
WHERE或复用索引；CTE 更清晰，且 PostgreSQL 通常能内联展开
怎么让 ROW_NUMBER() 查询跑得更快
窗口函数本身不走索引，但它的排序和过滤可以——前提是索引覆盖了 OVER 子句和 WHERE 条件里的所有列。
- 建复合索引时，顺序要匹配
ORDER BY+WHERE：例如CREATE INDEX idx_tasks_status_time_id ON tasks (status, updated_at DESC, id) - 如果查询还选了其他字段（如
description），考虑创建覆盖索引：INCLUDE (description)，避免回表 - 别在
ROW_NUMBER()外层再加ORDER BY：CTE 已按需排序，外层SELECT再排一次纯属浪费 - 深度分页（如第 1000 页）仍可能慢：此时可结合游标分页（
WHERE (status, updated_at, id) > ('pending', '2025-01-01', 9999)），比ROW_NUMBER()更轻量
COUNT(*) OVER() 要小心 total_count 出错
想在一页里同时拿到数据和总数，常用 COUNT(*) OVER()。但它默认统计的是当前窗口帧内的行数，不是整个符合条件的数据集。
错误写法（总数不准）：
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (...) AS rn,
COUNT(*) OVER () AS total_count -- ❌ 没加 WHERE，算的是全表
FROM tasks
)
SELECT * FROM ranked WHERE rn BETWEEN 41 AND 60;
正确写法（总数与分页数据严格一致）：
WITH filtered AS (
SELECT * FROM tasks WHERE status = 'active'
),
ranked AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY updated_at DESC, id) AS rn,
COUNT(*) OVER () AS total_count -- ✅ COUNT 在 filtered 后计算
FROM filtered
)
SELECT * FROM ranked WHERE rn BETWEEN 41 AND 60;
关键点在于：所有影响数据集大小的操作（WHERE、JOIN、GROUP BY）必须在 COUNT(*) OVER() 所在的子查询层级完成，否则总数和实际返回行数对不上。
