Oracle转PostgreSQL：带全局行号的分页查询移植咨询

嘿，这个需求我之前在迁移Oracle到PostgreSQL时刚好碰到过！要同时实现全局唯一行号和高效分页，我们可以结合PostgreSQL的特性，用几种不同的方案来适配不同场景，避免大数据量下的性能瓶颈：

1. 物化CTE + ROW_NUMBER()（通用高效方案） #

这种方案和你Oracle的三层嵌套逻辑最接近，而且通过物化CTE让排序和行号计算只执行一次，避免重复开销：

WITH rs AS MATERIALIZED (
  SELECT *, ROW_NUMBER() OVER (ORDER BY grades) AS rn
  FROM students
)
SELECT *
FROM rs
WHERE rn > #{startIndex}
LIMIT #{pageSize};

• 这里的MATERIALIZED关键字会让PostgreSQL先把排序后的结果+行号计算出来，存到临时存储里，再做分页过滤，大数据量下比普通嵌套查询高效很多
• 行号rn的逻辑和Oracle的ROWNUM完全一致：基于ORDER BY grades的全局排序结果生成唯一序号

2. 带索引优化的直接分页（小偏移量场景） #

如果你的students表在grades字段上创建了索引，那么可以直接用窗口函数+LIMIT/OFFSET，PostgreSQL会自动利用索引优化执行计划，不需要全表扫描：

SELECT *, ROW_NUMBER() OVER (ORDER BY grades) AS rn
FROM students
ORDER BY grades
OFFSET #{startIndex}
LIMIT #{pageSize};

这种写法更简洁，但要注意：当startIndex很大（比如百万级的偏移量），LIMIT/OFFSET的性能会急剧下降，因为数据库需要跳过前面所有的行才能定位到分页起点，这时候就需要用下面的深分页优化方案。

3. 键集分页（深分页最优方案） #

针对大数据量的深分页场景，推荐用键集分页（基于排序键的定位）来替代OFFSET，同时计算全局行号：

-- 假设上一页最后一条记录的grades是85，对应的全局行号是100
WITH rs AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY grades) AS rn_in_page
  FROM students
  WHERE grades > 85 -- 根据排序是否有重复值，可调整为>=，同时加上主键避免重复
  ORDER BY grades
  LIMIT #{pageSize}
)
SELECT *, 100 + rn_in_page AS rn -- 累加之前的行号得到全局唯一序号
FROM rs;

• 这种方案的性能最优，因为它利用索引直接定位到分页的起始位置，不需要跳过大量行
• 需要额外记录上一页的最后一个排序键值（比如grades）和对应的全局行号，适合需要翻页到很后面的场景

对比Oracle原逻辑 #

你原来的Oracle三层嵌套是为了规避ROWNUM的特性（WHERE子句中ROWNUM是先过滤再分配序号），而PostgreSQL的ROW_NUMBER()窗口函数天生就是基于全局排序结果生成序号，所以不需要复杂的嵌套，通过上面的方案就能完美替代，同时保证性能。

内容的提问来源于stack exchange，提问作者97amarnathk