Postgres中ROW_NUMBER()无排序时结果不稳定的原因及稳定化方案

核心本质：无明确排序时，PostgreSQL的行顺序不保证稳定 #

你写的ROW_NUMBER() OVER ()没有指定ORDER BY子句，PostgreSQL在这种情况下，不会保证返回行的顺序是固定的——它会根据执行计划的最优路径返回数据，而这个路径的行顺序可能每次都发生变化。

带主键表结果稳定的原因 #

从执行计划能看到，带主键的表用了Index Only Scan，这个索引是基于主键构建的，索引本身是有序结构，扫描时会严格按照主键的顺序返回数据。哪怕你没写ORDER BY，索引扫描的天然顺序让ROW_NUMBER()生成的序号对应的行是固定的，所以前10000行的avg结果一致。

无主键表结果不稳定的原因 #

无主键表用的是Seq Scan（全表扫描），PostgreSQL的全表扫描没有固定的行输出顺序：

• 数据在磁盘上的存储顺序可能因为插入、删除、VACUUM等操作发生变化；
• 如果开启了并行扫描，不同工作进程的调度顺序会影响行的返回顺序；
• 缓存命中情况的差异也可能导致行顺序波动。
  这些因素都会让每次扫描返回的行顺序不一样，ROW_NUMBER()生成的序号对应不同的行，最终avg结果自然每次都变。

要得到稳定的查询结果，必须在ROW_NUMBER()的OVER子句里加上唯一且稳定的排序条件，比如：

1. 如果表有主键，直接用主键排序：

SELECT * 
FROM 
    (SELECT 
         *, 
         ROW_NUMBER() OVER (ORDER BY 主键列) AS n 
     FROM 
         {table_name}) t 
WHERE 
    n < 10000 

2. 如果没有主键，找一个业务上唯一的列（比如唯一索引列）来排序；如果连唯一列都没有，退而求其次可以用ctid（注意：ctid是PostgreSQL内部的行标识符，当表发生VACUUM FULL、行移动时会变化，仅临时使用可以，不建议长期依赖）：

SELECT * 
FROM 
    (SELECT 
         *, 
         ROW_NUMBER() OVER (ORDER BY ctid) AS n 
     FROM 
         {table_name}) t 
WHERE 
    n < 10000 

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