在postgres中使用LIMIT时不使用索引 [英] Index not used when LIMIT is used in postgres
本文介绍了在postgres中使用LIMIT时不使用索引的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个单词表,其索引为(language_id,state)。以下是EXPLAIN ANALYZE的结果:
I have a words table with an index on (language_id, state). Here are the results for EXPLAIN ANALYZE:
无限制
explain analyze SELECT "words".* FROM "words" WHERE (words.language_id = 27) AND (state IS NULL);
Bitmap Heap Scan on words (cost=10800.38..134324.10 rows=441257 width=96) (actual time=233.257..416.026 rows=540556 loops=1)
Recheck Cond: ((language_id = 27) AND (state IS NULL))
-> Bitmap Index Scan on ls (cost=0.00..10690.07 rows=441257 width=0) (actual time=230.849..230.849 rows=540556 loops=1)
Index Cond: ((language_id = 27) AND (state IS NULL))
Total runtime: 460.277 ms
(5 rows)
限制100
explain analyze SELECT "words".* FROM "words" WHERE (words.language_id = 27) AND (state IS NULL) LIMIT 100;
Limit (cost=0.00..51.66 rows=100 width=96) (actual time=0.081..0.184 rows=100 loops=1)
-> Seq Scan on words (cost=0.00..227935.59 rows=441257 width=96) (actual time=0.080..0.160 rows=100 loops=1)
Filter: ((state IS NULL) AND (language_id = 27))
Total runtime: 0.240 ms
(4 rows)
为什么这发生了吗?
谢谢。
推荐答案
我认为PostreSQL查询计划程序只是认为在第二种情况下-具有LIMIT的那个-不值得应用索引,因为它[LIMIT]太小。所以这不是问题。
I think that the PostreSQL query planner just thinks that in the second case - the one with the LIMIT - it's not worth applying the index as it [the LIMIT] is too small. So it's not an issue.
这篇关于在postgres中使用LIMIT时不使用索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文