时间戳

首页 > 解决方案 > 如何在 Postgres 中索引表以加快 ORDER BY

postgresql - 如何在 Postgres 中索引表以加快 ORDER BY

问题描述

如何在 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;

但这几乎没有效果。上面的查询仍然需要几分钟。这只是ORDER BYPostgres 的一个限制，还是我可以使用不同类型的索引来加速它？

标签： postgresqlpostgresql-11

解决方案

尝试索引是正确的，但您使用了错误的索引。在这里试试这个：

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

您的索引仅last_active_timestamp在已经被排序之后才排序active，region因此您不能只使用索引来获得排序输出。

为了进一步加快速度，您还可以使用INCLUDE (id, first_name, last_name). 现在您的查询可以（如果计划者选择这样做并且我认为它会）仅在索引上运行，而根本不接触表数据。

推荐阅读