当我添加一个时,我的查询变得非常慢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)
该表包含 44,884,559 行和 66,762 个不同的 objectID。
我在两个字段上都有单独的索引:timestamp
and objectID
.
我做了一个vacuum analyze
在桌子上,我已经重新索引了桌子。
此外,当我将限制设置为 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)
一般来说,我认为这与规划者对执行成本做出错误的假设有关,因此选择较慢的执行计划。
这是真正的原因吗?有解决办法吗?