sql - 多列排序会破坏 postgresql 性能，有什么办法可以解决？

Question

我有一个非常简单的查询，我在一个大表(50 万行)上运行以分页结果。

最初我使用的是这个查询，它真的很快:

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

其解释分析显示执行时间为 0.2 毫秒。很酷。

但是 sas_rating 列有重复的整数值，我意识到在对结果进行分页时(对其他页面使用偏移量)我得到了重复的结果。没问题，添加主键作为次要order by。但是性能很糟糕。

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

解释分析需要 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 时防止重复结果？

Answer 1

我相信您可能添加了错误的多列索引 - 例如您使用了 sas_rating, id 而不是 sas_rating desc, id，因为只有后者可以使用为您的订单。

sas_rating 上的索引仅可用于 sas_rating desc 排序，因为数据库可以在其上倒退。多列索引不是这种情况 - 您必须像排序一样保留列的升序/降序。