sql – 在postgres中使用LIMIT时未使用的索引

发布时间：2020-12-12 06:31:42 所属栏目：MsSql教程来源：网络整理

导读：我有一个带有索引的单词表(language_id,state).以下是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=441

我有一个带有索引的单词表(language_id,state).以下是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]太小而不值得应用索引.所以这不是问题.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!