PostgreSQL查询使用limit 1非常缓慢

Question

当我添加limit 1时，我的查询变得非常缓慢。

我有一个名为object_values的表，其中包含对象的时间戳数值：

 timestamp |  objectID |  value
--------------------------------
 2014-01-27|       234 | ksghdf

每个对象我想要获得最新的值:

SELECT * FROM object_values WHERE (objectID = 53708) ORDER BY timestamp DESC LIMIT 1;

（我取消了超过10分钟的查询）

如果给定objectID的值没有结果，这个查询非常缓慢（如果有结果则很快）。 如果我移除限制，它会立即告诉我没有结果：

SELECT * FROM object_values WHERE (objectID = 53708) ORDER BY timestamp DESC;  
...  
Time: 0.463 ms

一份解释告诉我，没有限制条件的查询使用了索引，而带有limit 1的查询则没有使用索引：

较慢的查询：

explain SELECT * FROM object_values WHERE (objectID = 53708) ORDER BY timestamp DESC limit 1;  
QUERY PLAN`
----------------------------------------------------------------------------------------------------------------------------
Limit  (cost=0.00..2350.44 rows=1 width=126)
->  Index Scan Backward using object_values_timestamp on object_values  (cost=0.00..3995743.59 rows=1700 width=126)
     Filter: (objectID = 53708)`

快速查询：

explain SELECT * FROM object_values WHERE (objectID = 53708) ORDER BY timestamp DESC;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Sort  (cost=6540.86..6545.11 rows=1700 width=126)
   Sort Key: timestamp
   ->  Index Scan using object_values_objectID on working_hours_t  (cost=0.00..6449.65 rows=1700 width=126)
         Index Cond: (objectID = 53708)

timestampobjectID
vacuum analyze表格包含44,884,559行和66,762个不同的objectIDs。 我在两个字段上分别建立了索引：和。 我对表进行了操作，并重新索引了该表。

另外，当我将限制设置为3或更高时，慢查询变得快了：

explain SELECT * FROM object_values WHERE (objectID = 53708) ORDER BY timestamp DESC limit 3;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Limit  (cost=6471.62..6471.63 rows=3 width=126)
   ->  Sort  (cost=6471.62..6475.87 rows=1700 width=126)
         Sort Key: timestamp
         ->  Index Scan using object_values_objectID on object_values  (cost=0.00..6449.65 rows=1700 width=126)
               Index Cond: (objectID = 53708)

总的来说，我认为这与规划者对执行成本做出错误假设有关，因此选择了更慢的执行计划。

这是真正的原因吗？是否有解决方法？

- pat

---

这个问题是否已经在 pg-bugs 上提出，并在最新版本的 Postgres 中得到解决？ - Sidharth Samant

@ShiwanginiShishulkar - 我在问。 - Sidharth Samant

Answer 1

你可以通过在查询中添加一个不必要的ORDER BY子句来避免这个问题。

SELECT * FROM object_values WHERE (objectID = 53708) ORDER BY timestamp, objectID DESC limit 1;

- Brendan Nee

---

哈！太棒了！完全解决了它！ - BrianC

这个答案实际上是有效的，不像之前的回答和所有评论。 - mianos

好的。是否能够得到为什么这样的解释？ - Boro

关于这个错误的讨论在 PostgreSQL 邮件列表上：https://www.postgresql.org/message-id/flat/CA%2BU5nMLbXfUT9cWDHJ3tpxjC3bTWqizBKqTwDgzebCB5bAGCgg%40mail.gmail.com - John Bachir

根据我的经验，对于Postgres 13来说，不必要的ORDER BY技巧已经不再适用。相反，技巧变成了重写查询（使用CTE或子查询），以便将LIMIT移动，就像这个例子一样。 - Fabien Snauwaert

Answer 2

你遇到的问题与行相关性统计数据的缺失有关，如果你使用的是最新版本的Postgres，请考虑将其报告给pg-bugs以供参考。

对于你的计划，我建议的解释是：

• limit 1让Postgres只查找一行数据，并且在这个过程中假设你的object_id是足够常见的，以至于它会在索引扫描中相对快速地出现。

  根据你提供的统计数据，它可能认为平均需要读取大约70行数据才能找到符合条件的一行数据；它只是没有意识到object_id和timestamp之间的相关性，实际上会导致它读取表的大部分数据。

• 相比之下，limit 3让Postgres意识到object_id不常见，因此它会认真考虑（并最终）使用top-n排序来处理预计有1700行数据的object_id，因为这样做可能更便宜。

  例如，它可能知道这些行的分布情况是它们都集中在磁盘的同一个区域。

• 没有limit子句意味着它仍然会获取所有的1700行数据，所以它直接使用object_id的索引。

顺便说一下，解决方案：

在(object_id, timestamp)或(object_id, timestamp desc)上添加索引。

- Denis de Bernardy

---

针对“limit 1”这种情况，你是想说全表扫描吗？而不是索引扫描。 - harmic

@harmic：OP在那里使用了索引扫描...不一定是整个表，但肯定比PG预想的要多很多。 - Denis de Bernardy

@Denis：感谢您的回复，我已经想到了解释可能是这样的。 组合索引确实解决了它，您的回复使我认识到了很多关于索引、排序和组合索引的知识。感谢您。由于问题是基于统计数据的，可能只在表填充时才会出现？！ - pat

@pat：问题是由于表中的相关性引起的。Postgres几乎不会收集有关它们的统计数据或数据。因此，任何Postgres提出的计划都将假定数据完全不相关。例如，它不会知道自动递增ID可能与自动填充的date_created字段非常强烈地相关。 :-) - Denis de Bernardy

我认为Denis的意思是，随着向表中添加行，两者都会增加。如果它是一个“created_on”时间戳，而不是“updated_on”，那么这意味着它们严格相关--更大的ID将始终与更大的时间戳配对。如果在更新时更改，仍然至少有一个“默认”相关性，但随着时间的推移（因为行被更新），它可能会降低。 - Joshua

Answer 3

我在更新频繁的表上遇到了类似的症状，针对我的情况需要的是：

analyze $table_name;

在这种情况下，需要刷新统计信息，进而修复慢查询计划的问题。

支持文档：https://www.postgresql.org/docs/current/sql-analyze.html

- Dan Tanner

---

哇，当你在本地开发中创建索引或尝试找到最佳查询时，这真的是一个改变游戏规则的东西！非常感谢！ - Fred Hors

这在我们的情况下没有帮助。如果您的数据库经常发生变化，这是一个好主意！ :) - rogerdpack

查询时间从2分钟缩短到250毫秒。感谢这种神奇的黑科技！ - tanner burton

这立即解决了我的问题，我的RDS aurora-postgresql集群在一个小版本更新后突然变得很慢。谢谢！ - Ryan Ashcraft

Answer 4

这不是一个修复方法，但对于我来说，将limit 1改为limit 50并返回第一行结果要快得多...在这种情况下使用Postgres 9.x。只是想提一下作为OP提到的解决方法。

- rogerdpack

---

我实际上遇到了类似于 LIMIT 50 的问题（对于返回约2000行的查询，没有任何限制可以正常工作）。因此，这可能取决于许多变量，当PG选择不同的计划时，它通常是我们无法控制的，即使在 ANALYSE 之后也是如此。 - virgo47

Answer 5

我可以向你展示我处理这个问题的自定义方法。这个方法可能不是正确的方式，但是运行速度很快。以下是我的PostgreSQL函数：

CREATE OR REPLACE FUNCTION get_limited_object_values(_object_id BIGINT, _limit INTEGER)
    RETURNS SETOF object_values
    LANGUAGE plpgsql AS
$$
DECLARE
    row_object_value object_values;
    index INT := 0;
BEGIN
    FOR row_object_value IN (SELECT * FROM object_values WHERE (objectID = _object_id) ORDER BY timestamp DESC) LOOP
            IF index >= _limit THEN
                EXIT;
            END IF;
            RETURN NEXT row_object_value;
            index := index + 1;
        END LOOP;
    RETURN;
END;
$$;

你可以像这样使用它： SELECT * FROM get_limited_object_values(53708, 1);

- Ulvi Valiyev