postgresql - Postgres 选择慢得多的序列扫描而不是索引扫描

Question

我首先确保规划器已更新统计数据:

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 Scan:

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

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

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 不会更改查询计划。

Answer 1

根据此处对 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 ，如果您遇到的性能问题与此处给出的问题不匹配，那么此解决方案可能不适合您!请考虑其他情况可能需要重写查询或更改其他配置项(尤其是与内存使用和分配相关的配置项)。