如何避免包含ORDER_BY条件的SQL查询中的排序操作？

Question

我正在优化一个PostgreSQL查询，它涉及三个表和一个ORDER_BY条件。PostgreSQL似乎更喜欢安排排序操作来执行ORDER_BY条件，而不是使用索引。

原始SQL如下所示：

SELECT activity.provider, activity.provider_id, activity.crawled_at, activity.raw, activity.account_provider_id, activity.created_at
FROM activity 
JOIN friendship ON (activity.provider, activity.account_provider_id) = (friendship.provider, friendship.friend_provider_id) 
JOIN token ON (token.provider, token.account_provider_id) = (friendship.provider, friendship.provider_id) 
WHERE token.uid = 1 
ORDER BY created_at desc NULLS LAST
LIMIT 10 OFFSET 10000;

我已经在provider, account_provider_id, created_by表上创建了一个索引activity。

CREATE INDEX activity_provider_account_provider_id_created_at_idx
  ON activity
  USING btree
  (provider COLLATE pg_catalog."default", account_provider_id COLLATE pg_catalog."default", created_at DESC NULLS LAST);

但是，PostgreSQL返回的查询计划需要昂贵的排序操作。

这个查询需要超过10秒的时间才能完成。我也尝试过set enable_sort = false，但没有成功。我应该如何优化这个查询？

编辑:我添加了执行时间图。排序操作是查询中最昂贵的部分。如果没有ORDER_BY，查询返回的速度相当快。

Answer 1

单凭订单本身并不那么昂贵。这里的问题是，它与偏移量一起使用。因此postgresql对所有数据进行排序，然后才能接受所需的10行数据。

您可能正在使用ORDER +偏移量实现分页。这是一个已知的postgresql限制，需要postgresql方式来处理。

Answer 2

原来我在解释这个问题时犯了一个错误。排序操作不是此查询中最昂贵的部分，如第二个图所示。最昂贵的部分是第二个嵌套循环，它负责实现和加入activity表。数据库引擎需要从activity表中获取所有列。可以通过推迟加载额外列来优化查询：

SELECT * FROM activity WHERE (activity.provider, activity.account_provider_id, activity.created_at) IN (
  SELECT activity.provider, activity.account_provider_id, activity.created_at
  FROM activity 
  JOIN friendship ON (activity.provider, activity.account_provider_id) = (friendship.provider, friendship.friend_provider_id) 
  JOIN token ON (token.provider, token.account_provider_id) = (friendship.provider, friendship.provider_id) 
  WHERE token.uid = 2
  ORDER BY created_at desc NULLS LAST
  LIMIT 10 OFFSET 100
)

这可以将查询时间缩短到~100 to。

无论是来自@FuzzyTree的评论还是来自罗马Konoval的回答，在提出问题的意义上都是正确的。