postgresql - Postgres选择慢得多的Seq Scan，而不是Index Scan

首先，请确保计划者已更新统计信息:

my_db=> vacuum analyze;
VACUUM
Time: 1401.958 ms

仅选择foos.bar_id时，在该列上执行“仅索引扫描”可以很好地执行查询:

my_db=> EXPLAIN ANALYZE SELECT foos.bar_id FROM foos INNER JOIN bar_ids ON foos.bar_id = bar_ids.id;
 QUERY PLAN
 Nested Loop  (cost=0.43..16203.46 rows=353198 width=4) (actual time=0.045..114.746 rows=196205 loops=1)
   ->  Seq Scan on bar_ids  (cost=0.00..16.71 rows=871 width=4) (actual time=0.005..0.195 rows=871 loops=1)
   ->  Index Only Scan using index_foos_on_bar_id on foos  (cost=0.43..14.80 rows=378 width=4) (actual time=0.003..0.055 rows=225 loops=871)
         Index Cond: (bar_id = bar_ids.id)
         Heap Fetches: 0
 Planning time: 0.209 ms
 Execution time: 144.364 ms
(7 rows)

Time: 145.620 ms

但是，添加foos.id会使查询选择非常慢的Seq扫描:

my_db=> EXPLAIN ANALYZE SELECT foos.id, foos.bar_id FROM foos INNER JOIN bar_ids ON foos.bar_id = bar_ids.id;
 QUERY PLAN
 Hash Join  (cost=27.60..221339.63 rows=353198 width=8) (actual time=294.091..3341.926 rows=196205 loops=1)
   Hash Cond: (foos.bar_id = bar_ids.id)
   ->  Seq Scan on foos  (cost=0.00..182314.70 rows=7093070 width=8) (actual time=0.004..1855.900 rows=7111807 loops=1)
   ->  Hash  (cost=16.71..16.71 rows=871 width=4) (actual time=0.454..0.454 rows=866 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 39kB
         ->  Seq Scan on bar_ids  (cost=0.00..16.71 rows=871 width=4) (actual time=0.002..0.222 rows=871 loops=1)
 Planning time: 0.237 ms
 Execution time: 3371.622 ms
(8 rows)

Time: 3373.150 ms

禁用Seq扫描会强制对同一索引执行索引扫描，这比Seq扫描快一个数量级:

my_db=> set enable_seqscan=false;
SET
Time: 0.801 ms
my_db=> EXPLAIN ANALYZE SELECT foos.id, foos.bar_id FROM foos INNER JOIN bar_ids ON foos.bar_id = bar_ids.id;
 QUERY PLAN
 Nested Loop  (cost=10000000000.43..10000439554.99 rows=353198 width=8) (actual time=0.028..171.632 rows=196205 loops=1)
   ->  Seq Scan on bar_ids  (cost=10000000000.00..10000000016.71 rows=871 width=4) (actual time=0.005..0.212 rows=871 loops=1)
   ->  Index Scan using index_foos_on_bar_id on foos  (cost=0.43..500.86 rows=378 width=8) (actual time=0.003..0.118 rows=225 loops=871)
         Index Cond: (bar_id = bar_ids.id)
 Planning time: 0.186 ms
 Execution time: 201.958 ms
(6 rows)

Time: 203.185 ms

其他答案则表示，计划不佳是由于统计数据不正确所致。我的统计数据是最新的。是什么赋予了？
bar_ids是一个临时表，它可能与上次查询(Seq Scan on bar_ids (cost=10000000000.00..10000000016.71)中的疯狂成本估算有关，但是显式运行ANALYZE bar_ids不会更改查询计划。

最佳答案

在此处对OP的评论进行跟进。

对于第一个查询，当您仅选择foos.bar_id时，执行程序可以通过仅索引扫描来实现此目的，这很不错。在选择列表上附加另一列(未包含在索引中)意味着，继续使用该索引意味着典型的“重读”情况，在该情况下，我们先读取索引页，然后读取表页以获取剩余的内容列的值，这意味着可能会有大量的随机IO。

设置random_page_cost应该相对于seq_page_cost而言(宽松地)意味着随机IO比顺序IO(给定random_page_cost)贵seq_page_cost=1几倍。对于现代驱动器，随机IO并不那么昂贵，因此降低random_page_cost可以使索引扫描更可取。为此很难找到“最佳”值，但是从2左右开始是一个不错的经验法则。

E:我没有时间提早添加这些额外的想法，但这一直困扰着我。如果您是因为遇到类似问题而发生这种情况，请不要只是随便摆弄这个配置。在这种情况下，这似乎是一个富有成果的方法，因为OP已经澄清了统计信息是新鲜且合理的，索引扫描是可行的，并且计划者更倾向于表扫描，即使这显然更糟(就经验证据)。此配置不是 Elixir ，如果您遇到的性能问题与此处给出的问题不完全匹配，那么此解决方案可能不适合您!请考虑其他情况，可能需要查询重写或更改其他配置项(尤其是那些与内存使用和分配有关的配置项)。

关于postgresql - Postgres选择慢得多的Seq Scan，而不是Index Scan，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40746508/