Postgres中使用LIMIT时未使用索引

Question

我有一个单词表，其中(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)

为什么会发生这种情况？我如何才能使索引在所有情况下都被使用？谢谢。

- alste

---

没有ORDER BY的LIMIT似乎价值有限（无意双关）。你期望返回哪100行？ - Joe Stefanelli

顺便问一下：language_id = 17 AND status IS NULL 子句的选择性是多少？words表的总大小是多少？ - wildplasser

真的吗？顺序是按照id DESC发生的。那会不会减慢速度？需要在该列上建立索引吗？@wildplasser总大小为1000万行，选择性约为500,000行。 - alste

所以你期望查询获取5%的数据。计划取决于键结构（+“聚集”）+统计信息的可用性和形状+元组大小（->每页估计元组数->需要获取的页面数）。 - wildplasser

Answer 1

请查看关于使用EXPLAIN和查询规划的PostgreSQL文档。查询规划器在LIMIT 100情况下更喜欢顺序扫描而不是索引扫描的原因很简单，因为顺序扫描更便宜。

查询中没有ORDER BY子句，所以规划器满足于与筛选条件匹配的前100个（随机）行。索引扫描需要先读取索引页，然后读取数据页以获取相应的行。顺序扫描只需要读取数据页以获取行。在您的情况下，表统计信息似乎表明有足够的（随机）行与筛选条件匹配。获取100行的顺序页面读取成本被认为比先读取索引再获取实际行的成本更低。当您提高限制或较少的行匹配筛选条件时，您可能会看到不同的计划。

默认情况下，规划器认为随机页面读取的成本（random_page_cost）是顺序页面读取的四倍（seq_page_cost）。可以调整这些设置来优化查询计划（例如，当整个数据库都在内存中时，随机页面读取不比顺序页面读取更昂贵，应该优先考虑索引扫描）。您还可以通过启用/禁用某些扫描类型来尝试不同的查询计划，例如：

set enable_seqscan = [on | off]
set enable_indexscan = [on | off]

虽然可以在全局范围内启用/禁用某些类型的扫描，但这只应该用于按会话基础进行调试或故障排除。在测试查询计划之前还要运行 VACUUM ANALYZE words，否则测试之间的自动清理（autovaccum）可能会影响结果。

- tscho

Answer 2

我认为PostgreSQL查询规划器认为，在第二种情况下，也就是有LIMIT的情况下，由于LIMIT太小，应用索引没有意义。所以这不是一个问题。

- Marek Příhoda

---

你说得没错，但我该如何避免这种情况呢？实际上，我正在使用 jsonb gin 索引，但由于限制为 1，所以该索引未被使用且耗时巨大。 - Anurag

嗨@Anurag，你是如何解决这个问题的？ - TheSohan

Answer 3

无限制：行数=540556次循环=1 总运行时间：460.277毫秒

有限制：行数=100次循环=1 总运行时间：0.240毫秒

我看不出有什么问题。如果您的查询产生了50万行，它需要更多时间。

- wildplasser

Answer 4

两个查询返回不同行数也很奇怪。我猜你一直在插入数据...嗯，如果你做一个子查询呢？

select * from (select ...) limit 100;

- Klaus Byskov Pedersen

---

"(5行)" 指的是执行计划中的行数，而不是查询返回的行数。 - Anna