postgresql - 为什么 Postgres 在我的 JOIN 子句中使用顺序扫描？

Question

使用 PG 9.5，我有一个查询将 rubber 表的 FK 列连接到 fuzzy 表的主要 id 列。两列都使用标准 btree 索引进行索引。 rubber表行数超过230MM，fuzzy表行数超过25MM。当我对这些表进行连接并在 fuzzy 中对列应用约束时，PG 在连接中继续使用顺序扫描，查询大约需要 2 分钟。

SELECT * FROM rubber r 
JOIN fuzzy fp ON fp.id = r.fuzzy_id
WHERE fp.bean_num IN (73470871);

我已将其缩小为连接，即查询中顺序的、缓慢的部分。即，以下非常快，并使用索引:

SELECT * FROM rubber WHERE fuzzy_id = 12345

但是当我尝试这样的事情时，它和上面的 JOIN 查询一样慢:

SELECT * FROM rubber WHERE fuzzy_id IN (
    SELECT id FROM fuzzy WHERE bean_num IN (73470871)
);

我怀疑这与查询规划器在尝试匹配某些外键集时无法(决定不？)使用索引有关。外键不是唯一的，但不是高度重复的，并且没有一个设置为空，所以我无法利用部分索引之类的东西。

表定义:

-- 231MM rows
CREATE TABLE rubber (
    id bigint DEFAULT nextval('rubber_id_seq1'::regclass) PRIMARY KEY,
    context_id integer NOT NULL REFERENCES context(id) ON DELETE CASCADE,
    fuzzy_id integer REFERENCES fuzzy(id),
);

CREATE UNIQUE INDEX rubber_pkey1 ON rubber(id int8_ops);
CREATE INDEX rubber_context_id_idx1 ON rubber(context_id int4_ops);
CREATE INDEX rubber_fingerprint_id_idx1 ON rubber(fingerprint_id int4_ops);
CREATE INDEX rubber_conclusion_id_idx1 ON rubber(conclusion_id int4_ops);
CREATE UNIQUE INDEX rubber_id_idx ON rubber(id int8_ops);
CREATE INDEX rubber_fuzzy_id_idx1 ON rubber(fuzzy_id int4_ops);

-- 26.5MM rows
CREATE TABLE fuzzy (
    id SERIAL PRIMARY KEY,
    trip_id integer NOT NULL REFERENCES trip(id),
    device_id integer NOT NULL REFERENCES device(id),
    chirp_vision_id integer NOT NULL REFERENCES chirp_vision(id),
    mode_id integer NOT NULL REFERENCES mode(id),
    fig_id integer NOT NULL REFERENCES fig(id),
    gist_id integer NOT NULL REFERENCES gist(id),
    bean_num integer REFERENCES bean_num(id),
    key_path jsonb NOT NULL,
    CONSTRAINT fingerprint_tuple UNIQUE (chirp_vision_id, gist_id, key_path, trip_id, fig_id, device_id, mode_id)
);

CREATE UNIQUE INDEX fuzzy_pkey ON fuzzy(id int4_ops);
CREATE INDEX fuzzy_fig_id_idx ON fuzzy(fig_id int4_ops);
CREATE INDEX fuzzy_gist_id_idx ON fuzzy(gist_id int4_ops);
CREATE INDEX fuzzy_bean_num_idx ON fuzzy(bean_num int4_ops);
CREATE UNIQUE INDEX fingerprint_tuple ON fuzzy(chirp_vision_id int4_ops,gist_id int4_ops,key_path jsonb_ops,trip_id int4_ops,fig_id int4_ops,device_id int4_ops,mode_id int4_ops);

解释(缓冲区，分析):

"QUERY PLAN"
"Hash Join  (cost=5288.99..6339911.22 rows=15277 width=189) (actual time=82319.995..136625.784 rows=483 loops=1)"
"  Hash Cond: (r.fuzzy_id = fp.id)"
"  Buffers: shared hit=599 read=3151247"
"  ->  Seq Scan on rubber r  (cost=0.00..5466479.88 rows=231463888 width=80) (actual time=0.078..117561.885 rows=231463887 loops=1)"
"        Buffers: shared hit=597 read=3151244"
"  ->  Hash  (cost=5267.11..5267.11 rows=1750 width=109) (actual time=2.251..2.251 rows=23 loops=1)"
"        Buckets: 2048  Batches: 1  Memory Usage: 20kB"
"        Buffers: shared hit=2 read=3"
"        ->  Index Scan using fuzzy_bean_num_idx on fuzzy fp  (cost=0.44..5267.11 rows=1750 width=109) (actual time=2.220..2.244 rows=23 loops=1)"
"              Index Cond: (bean_num = 73470871)"
"              Buffers: shared hit=2 read=3"
"Planning time: 0.382 ms"
"Execution time: 136625.875 ms"

有没有办法从这样的查询中获得更好的性能？

dba stack exchange comment 中也有一个有趣的评论，建议在 (fuzzy_id, bean_num) 上建立索引会有帮助，但我不明白这有什么帮助。

更新:我已经迁移到 PG 12.3，这个查询现在可以在几百毫秒内运行。

Answer 1

问题:为什么要在 rubber.id 上创建 2 个(几乎)相同的索引:

CREATE UNIQUE INDEX rubber_pkey1 ON rubber(id int8_ops);
CREATE UNIQUE INDEX rubber_id_idx ON rubber(id int8_ops);

建议:DROP INDEX rubber_id_idx;

一个可能对 JOIN 非常有用的索引，可以为计划者提供有关这些表之间关系的更好信息，是这个:

CREATE INDEX fuzzy_bean_num_idx_2 ON fuzzy(bean_num, id);

您可能需要对 statistics 的数量进行不同(更好)的设置。以及。也许只是一张 table ，也许两张 table ，也许是整个系统。

编辑:更改统计信息设置后，您必须为这些表运行ANALYZE 以更新统计信息。

Offtopic:9.5 版本已经过时，将在未来几个月内停产。较新版本的行为确实有所不同，也可能会解决此性能问题。