Postgres 在 JOIN 期间选择 seq 扫描而不是索引扫描，"IN 子句"中有更多元素

Question

我有2个不同的查询，唯一的区别是' in子句'中的元素数量。

第一个查询有3个元素

EXPLAIN ANALYZE
SELECT *
FROM myi_post
LEFT JOIN myi_post_hashtag ON myi_post_hashtag.post_id = myi_post.id
WHERE myi_post.id IN ('2579','2577','2575')
ORDER by myi_post.id DESC 

正确使用索引扫描myi_post_hashtag

Nested Loop Left Join  (cost=0.56..44.23 rows=3 width=156) (actual time=0.009..0.018 rows=14 loops=1)
->  Index Scan Backward using myi_post_pkey on myi_post  (cost=0.28..19.23 rows=3 width=108) (actual time=0.006..0.009 rows=3 loops=1)
Index Cond: (id = ANY ('{2579,2577,2575}'::bigint[]))
->  Index Scan using myi_post_hashtag_post_id_index on myi_post_hashtag  (cost=0.28..8.32 rows=2 width=40) (actual time=0.001..0.002 rows=5 loops=3)
Index Cond: (post_id = myi_post.id)
Planning time: 0.199 ms
Execution time: 0.042 ms

第二个查询有4个元素

EXPLAIN ANALYZE
SELECT *
FROM myi_post
LEFT JOIN myi_post_hashtag ON myi_post_hashtag.post_id = myi_post.id
WHERE myi_post.id IN ('2579','2577','2575','2571')
ORDER by myi_post.id DESC 

它在myi_post_hashtag上使用seq扫描，这要慢得多

Sort  (cost=51.44..51.45 rows=4 width=156) (actual time=0.215..0.216 rows=16 loops=1)
Sort Key: myi_post.id DESC
Sort Method: quicksort  Memory: 29kB
->  Hash Right Join  (cost=24.74..51.40 rows=4 width=156) (actual time=0.198..0.207 rows=16 loops=1)
Hash Cond: (myi_post_hashtag.post_id = myi_post.id)
->  Seq Scan on myi_post_hashtag  (cost=0.00..23.40 rows=1240 width=40) (actual time=0.003..0.078 rows=1240 loops=1)
->  Hash  (cost=24.69..24.69 rows=4 width=108) (actual time=0.014..0.014 rows=4 loops=1)
Buckets: 1024  Batches: 1  Memory Usage: 9kB
->  Index Scan Backward using myi_post_pkey on myi_post  (cost=0.28..24.69 rows=4 width=108) (actual time=0.006..0.011 rows=4 loops=1)
Index Cond: (id = ANY ('{2579,2577,2575,2571}'::bigint[]))
Planning time: 0.212 ms
Execution time: 0.246 ms

这可能是什么原因?请注意，我已经尝试运行真空分析，但没有任何变化。是否有一种方法，我可以暗示Postgres总是使用索引?

Answer 1

PostgreSQL没有内置规划师提示，但是有一个第三方扩展可以添加它们，https://github.com/ossc-db/pg_hint_plan。我没有经常使用它，不能保证它，但它看起来很有希望作为你最后一个问题的答案。

至于原因，我认为这主要归结于PostgreSQL总是假设一些大量的数据将来自磁盘。而在你的情况下，我怀疑这是不正确的，要么是因为你已经一遍又一遍地测试相同的查询，所以与你的查询相关的部分数据已经在内存中，或者因为你所有的数据都在内存中，因为它都适合，并且经常被使用，足以自然地积累在那里。

如果你的数据在SSD上，将random_page_cost从4降低到1.1或1.0(即与seq_page_cost相同)是有意义的，我很确定这会导致它改变这个查询以使用你的首选计划。

即使您的数据在HDD上，但您假设它将保留在内存中缓存，那么做同样的事情可能是有意义的，但如果您的假设是错误的，它可能导致一些真正可怕的计划。如果你选择了这条路，你一定要使用pg_prewarm.autoprewarm.