如何优化SQL SELECT查询以生成用户的新闻提要？

Question

我目前正在尝试构建一个特性来生成用户的新闻提要，方法是使用posts表中的以下查询。这是我们使用的SQL语句：

SELECT *
FROM "posts" AS "post"
WHERE "post"."sourceId" IN (...)
ORDER BY "post"."createdAt" DESC, "post"."timestamp" DESC
LIMIT 10;

posts表目前有大致的200K+行，而且可能会变得更大。我在数据库性能方面的技能不是最强的，但是有没有办法优化这个查询，让它尽可能快地运行呢？我假设在sourceId列上添加一个索引是不够的，而是需要一个多列索引来考虑ORDER BY列。

Answer 1

对于此查询：

SELECT p.*
FROM posts p
WHERE p.sourceId IN (...)
ORDER BY p.createdAt DESC, p.timestamp DESC
LIMIT 10;

唯一真正有帮助的索引是posts(sourceId)上的索引。

请注意，我删除了"。在定义表名和列名时，不要对它们进行转义。当你使用它们的时候，你就不需要转义它们了。

但是，查询仍然需要对所有数据进行排序。这可能会很耗时。对于Postgres来说，更复杂的查询更容易优化：

select p.*
from ((select p.*
       from posts p
       where sourceId = $si_1
       order by p.createdAt desc, p.timestamp desc
       limit 10
      ) union all
      (select p.*
       from posts p
       where sourceId = $si_2
       order by p.createdAt desc, p.timestamp desc
       limit 10
      ) union all
      . . .
    ) p
order by p.createdAt desc, p.timestamp desc;

此查询可以将posts(sourceId, createdAt desc, timestamp desc)上的索引用于内部selects。这应该是很快的。外部的order by仍然需要排序，但是数据量应该要小得多。

例如，如果一个典型的源有10,000行，而您只查找其中的3行，那么您的查询版本需要对30,000行进行排序才能获取10行。此版本使用索引获取30行，然后对它们进行排序以获得最后的10行。

这在性能上会有很大的不同。

Answer 2

您可能会发现，仅在sourceId上建立一个索引就足够了：

CREATE INDEX src_idx ON posts (sourceId);

Postgres然后必须手动对通过WHERE子句的记录进行排序。进一步在ORDER BY子句中添加列可能也会有所帮助：

CREATE INDEX idx ON posts (sourceId, createdAt DESC, timestamp DESC);

这可能会通过让Postgres一次对匹配的sourceId记录组进行排序来加快排序操作。