如何在Postgres中为表建立索引以加速ORDER BY操作

Question

如何在PostgreSQL 11中创建索引以加速包含ORDER BY的特定查询？

我有一个查询需要从包含2M条记录的表中获取前100条记录，同时还有一些常见的过滤条件，例如：

SELECT id, first_name, last_name
FROM users
WHERE active = true AND region IN (1,2,3)
ORDER BY last_active_timestamp DESC;

没有ORDER BY子句时，它几乎瞬间返回，大约需要1秒钟。但是，有了这个子句，它会花费长达5分钟的时间，令人痛苦。

因此，我尝试创建一个部分索引，如下所示：

CREATE INDEX CONCURRENTLY my_user_index ON users (active, region, last_active_timestamp DESC NULLS LAST)
WHERE region IN (1, 2, 3) AND active = True;

但是那几乎没有效果。以上查询仍然需要数分钟的时间。这是Postgres中ORDER BY的限制，还是我可以使用不同类型的索引来加速它？

- Cerin

---

你能包含一个 EXPLAIN 吗？ - undefined

尝试仅使用last_active_timestamp列在索引上创建索引。region和active保持在where子句中。 - undefined

你尝试过一个更简单的索引吗？它只与ORDER BY子句有关，类似于CREATE INDEX CONCURRENTLY my_user_index ON users (last_active_timestamp DESC NULLS LAST)。 - undefined

你的查询中的“the first 100”在哪里？ - undefined

Answer 1

为了在查询中使用带有ORDER BY的索引，您需要对所有相关列（last_active_timestamp）建立索引，并包含一个条件来仅包括active==true和regions a,b,c。这将使数据按顺序提取出来。

此外，如果您分享您的EXPLAIN ANALYZE输出，您可能会看到一个Sort Method: external merge Disk: ####kB，表示排序溢出到磁盘而不是内存，这是由于work_mem大小不足所致。解决方案是增加work_mem至少为####kB的值，然后再试一次。

请注意，您可以根据每个会话设置work_mem，因为全局更改work_mem可能会产生负面影响，例如内存耗尽，因为每个会话都分配了postgresql.conf配置的work_mem（基本上具有乘法效应）。

如果在调整了work_mem之后查询仍然很慢（即所有排序都在内存中进行，但仍然很慢），那么你返回的数据集太大，无法快速排序。

- richyen

---

由于它们是有序的（至少标准B树是有序的），因此可以通过索引获取排序。但是使用的列避免了使用索引。 - undefined

Answer 2

尝试使用索引是正确的，但你使用了错误的索引。请尝试这个：

CREATE INDEX CONCURRENTLY my_user_index
    ON users (last_active_timestamp DESC)
    WHERE region IN (1, 2, 3)
        AND active = true;

你的索引只是在已经按照“active”和“region”排序后，才按照“last_active_timestamp”进行排序的，因此你不能仅仅使用该索引来获得排序后的输出。

为了进一步提高速度，你还可以使用“INCLUDE (id, first_name, last_name)”将选择子句中的列包含在索引中。现在，如果查询优化器选择这样做（我认为它会这样做），查询将仅在索引上运行，而不会触及表数据。

- Islingre