多列排序会影响PostgreSQL的性能，有什么解决方法吗？

Question

我是一名有用的助手，可以为您提供文本翻译。

我正在一个大表（500k行）上运行一个非常简单的查询来分页结果。

最初我使用的是这个查询，非常快：

 select * from deck 
    order by
        deck.sas_rating desc    
    limit 10

它的解释分析显示了0.2毫秒的执行时间。很酷。

但是 sas_rating 列具有重复的整数值，当我使用偏移量翻页时，意识到我得到了重复的结果。没问题，将主键作为次要排序方式添加。但性能很差。

 select * from deck 
    order by
        deck.sas_rating desc,
        deck.id asc     
    limit 10

用 explain analyze 进行分析，需要 685 毫秒：

Limit  (cost=164593.15..164593.17 rows=10 width=1496) (actual time=685.138..685.139 rows=10 loops=1)
  ->  Sort  (cost=164593.15..165866.51 rows=509343 width=1496) (actual time=685.137..685.137 rows=10 loops=1)
        Sort Key: sas_rating DESC, id
        Sort Method: top-N heapsort  Memory: 59kB
        ->  Seq Scan on deck  (cost=0.00..153586.43 rows=509343 width=1496) (actual time=0.009..593.444 rows=509355 loops=1)
Planning time: 0.143 ms
Execution time: 685.171 ms

我的生产服务器性能较弱，情况更糟。我的搜索时间从总共125毫秒变成了35秒！

我尝试添加多列索引，但这并没有改善性能。有没有办法在使用limit + offset时防止重复结果，而不会破坏查询的性能？

- CorayThan

---

如果您只想在此列上消除重复项，似乎在Postgres中有一个distinct on：https://dba.stackexchange.com/a/24328（接近结尾处）。 - user2956272

@dyukha，这里没有重复的行。问题在于当我使用limit 10进行选择，然后再使用limit 10 offset 10进行另一次选择时，由于sas_rating包含非唯一值，因此可能会检索到一些相同的结果。请参见此SO问题以了解问题，但是没有适用于我的解决方案。 - CorayThan

我明白了。如果您尝试按 ROW_NUMBER() OVER(ORDER BY id) 排序会怎样呢？可能类似这里的内容：https://zaiste.net/row_number_in_postgresql/，但是使用 order by 而不是 where。抱歉，我不确定它是否有效，也没有地方可以测试。 - user2956272

另一种分页选项在此处描述：https://use-the-index-luke.com/no-offset - user330315

Answer 1

我相信你可能错误地添加了多列索引 - 比如你使用了 sas_rating, id 而不是 sas_rating desc, id，因为只有后者可以用于你的 order by。 sas_rating 上的索引仅适用于 sas_rating desc 排序，因为数据库可以反向遍历它。而对于多列索引来说情况并非如此 - 你必须保留列的升序/降序顺序，就像排序一样。

- Tometzky

---

你说得对。我只需要让我的多列索引使用正确的sas_rating的desc顺序即可。我甚至没有意识到索引有desc和asc之分！ - CorayThan

@Tometzky . . . 哇！非常好的观察。我认为Postgres应该足够聪明，以便将(sas_rating, id)用于此目的。但这需要实现跳过扫描。 - Gordon Linoff