PostgreSQL排序异常：ORDER BY同值时OFFSET不影响末尾记录

这是PostgreSQL在排序键完全重复时的隐含行为导致的：

• 当你只按created_at DESC排序，且所有匹配记录的created_at值完全相同时，PostgreSQL没有明确的排序依据，会默认沿用数据的扫描/存储顺序（比如数据在磁盘上的物理存储位置，或者索引扫描时的顺序）。
• 那条ID为101810的记录，恰好是在扫描过程中最后被读取到的。所以不管你用OFFSET 0还是OFFSET 10，只要结果集包含它，它就会出现在结果的末尾——因为每次扫描的顺序是固定的，它始终是最后一个被取到的。

为什么用DISTINCT或按id排序就正常？ #

• SELECT DISTINCT会触发PostgreSQL额外的去重逻辑，这个过程会改变原有的扫描顺序，相当于间接引入了额外的排序规则；
• 按id排序时，id是唯一值，排序键明确且唯一，PostgreSQL会严格按照id的顺序排列，自然不会出现这种“固定末尾”的情况。

解决办法 #

如果需要稳定、可预测的排序结果，当排序键可能存在重复值时，必须追加一个唯一的附加排序键（比如主键id），确保排序规则的确定性：

SELECT * FROM order_supplier_invoices
WHERE supplier_invoice_id = 20340
ORDER BY created_at DESC, id DESC -- 追加id作为第二排序键
LIMIT 10 OFFSET 10;

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