postgresql - 如果没有数据，使用限制非常慢

Question

sql很简单。“orders_express_idx” btree( express )。 express 是索引。

效果很好。因为 express a 是存在的。

select * from orders where express =  'a'  order by id desc limit 1;

Limit  (cost=0.43..1.29 rows=1 width=119)
->  Index Scan Backward using orders_pkey on orders  (cost=0.43..4085057.23 rows=4793692 width=119)
     Filter: ((express)::text = 'a'::text)

工作缓慢。数据不存在。我使用限制。

select * from orders where express =  'b'  order by id desc limit 1;

 Limit  (cost=0.43..648.86 rows=1 width=119)
 ->  Index Scan Backward using orders_pkey on orders  (cost=0.43..4085061.83 rows=6300 width=119)
     Filter: ((express)::text = 'a'::text)

效果很好。数据不存在。但我没有使用限制。

select * from orders where express =  'b'  order by id desc;

Sort  (cost=24230.91..24246.66 rows=6300 width=119)
Sort Key: id
->  Index Scan using orders_express_idx on orders  (cost=0.56..23833.35 rows=6300 width=119)
     Index Cond: ((express)::text = 'a'::text)

Answer 1

https://www.postgresql.org/docs/9.6/static/using-explain.html

用

进入seciotn

Here is an example showing the effects of LIMIT:

还有:

This is the same query as above, but we added a LIMIT so that not all the rows need be retrieved, and the planner changed its mind about what to do. Notice that the total cost and row count of the Index Scan node are shown as if it were run to completion. However, the Limit node is expected to stop after retrieving only a fifth of those rows, so its total cost is only a fifth as much, and that's the actual estimated cost of the query. This plan is preferred over adding a Limit node to the previous plan because the Limit could not avoid paying the startup cost of the bitmap scan, so the total cost would be something over 25 units with that approach.

所以基本上 - 是的。添加 LIMIT 会改变计划，因此它可以对较小的数据集(预期)变得更有效，但也可能产生负面影响(取决于统计信息和设置(扫描成本、effective_cache_size 等)。 ..

如果您给出上述查询的执行计划，我们会解释发生了什么。但基本上这是记录在案的行为 - LIMIT 改变了计划，从而改变了执行时间 - 是的。