用LIMIT 1索引ORDER BY

一尘不染

用LIMIT 1索引ORDER BY

sql

我正在尝试获取表中的最新行。我有一个简单的时间戳created_at索引。当查询时ORDER BY created_at DESC LIMIT 1，它花费的时间远远超出我的预期（在36k行的计算机上大约需要50毫秒）。

EXPLAIN -ing它使用的权利要求 向后索引扫描 ，但我确认，更改索引是(created_at DESC)不会改变的查询规划的费用为一个简单的 索引扫描 。

如何优化此用例？

运行postgresql 9.2.4。

编辑：

# EXPLAIN SELECT * FROM articles ORDER BY created_at DESC LIMIT 1;
                                                  QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
Limit  (cost=0.00..0.58 rows=1 width=1752)
   ->  Index Scan Backward using index_articles_on_created_at on articles  (cost=0.00..20667.37 rows=35696 width=1752)
(2 rows)

阅读 132

2021-03-10

共1个答案

一尘不染

假设我们正在处理一个大表，则
部分索引 可能会有所帮助：

CREATE INDEX tbl_created_recently_idx ON tbl (created_at DESC)
WHERE created_at > '2013-09-15 0:0'::timestamp;

正如您已经发现的那样：下降或上升在这里几乎无关紧要。Postgres可以以几乎相同的速度向后扫描（例外适用于多列索引）。

查询以使用此索引：

SELECT * FROM tbl
WHERE  created_at > '2013-09-15 0:0'::timestamp -- matches index
ORDER  BY created_at DESC
LIMIT  1;

这里的重点是使索引更小，因此应该更容易缓存和维护。

您需要选择一个可以保证比最新时间戳更小的时间戳。
您应该不时重新创建索引以切断旧数据。
条件必须为IMMUTABLE。

因此，一次性效果会随着时间的流逝而恶化。在 具体的问题 是硬编码的条件：

WHERE created_at > '2013-09-15 0:0'::timestamp

自动化

您可以不时手动更新索引和查询。或者，您可以借助这样的功能将其自动化：

CREATE OR REPLACE FUNCTION f_min_ts()
  RETURNS timestamp LANGUAGE sql IMMUTABLE AS
$$SELECT '2013-09-15 0:0'::timestamp$$

指数：

CREATE INDEX tbl_created_recently_idx ON tbl (created_at DESC);
WHERE created_at > f_min_ts();

询问：

SELECT * FROM tbl
WHERE  created_at > f_min_ts()
ORDER  BY created_at DESC
LIMIT  1;

通过cron作业或某些基于触发器的事件自动进行娱乐。您的查询现在可以保持不变。但是您需要在更改此函数后以任何方式 重新创建所有索引
。只需拖放并创建每个。

第一的 ..

…测试您是否真的以此击中了瓶颈。

尝试是否简单地DROP index ... ; CREATE index ...完成工作。然后，您的索引可能已膨胀。您的自动真空设置可能已关闭。

或者尝试VACUUM FULL ANALYZE让您的整个表以及原始状态的索引再检查一次。

其他选项
包括常规的常规性能调整和覆盖索引，具体取决于您从表中实际获取的内容。

2021-03-10