PostgreSQL lookup表双向关联查询索引优化方案咨询

根因分析 #

慢查询触发全表扫描的核心原因是：现有ensembl_lookup表的feature_id、variant_id均为单列索引，当从feature_id侧关联查询时，需要通过feature_id索引回表获取variant_id，针对你表中18亿级的海量数据，优化器判定索引回表的随机IO代价远高于全表顺序扫描，因此选择了并行顺序扫描。
之前的聚簇、调整seqscan优先级无效的原因：仅聚簇没有解决回表开销，优化器代价估算差距过大，即便降低seqscan优先级也不会选择走单列索引。

优化方案 #

方案1：替换单列索引为复合覆盖索引（无需修改表结构，改造成本低） #

直接创建两个双向覆盖的复合索引，完全替代原有单列索引的功能，且支持索引覆盖扫描无需回表：

-- 支持从feature_id查询variant_id的覆盖索引，匹配从ensembl_transcript入口的查询
CREATE INDEX ensembl_lookup_feature_variant_idx ON ensembl_lookup (feature_id, variant_id);
-- 支持从variant_id查询feature_id的覆盖索引，匹配原有从variant_fact入口的查询
CREATE INDEX ensembl_lookup_variant_feature_idx ON ensembl_lookup (variant_id, feature_id);
-- 删除原有无用的单列索引
DROP INDEX IF EXISTS ensembl_lookup_feature_id_index, ensembl_lookup_variant_id_index;

方案2：重构关联表结构（性能最优，适合长期使用） #

ensembl_lookup是纯多对多关联表，不需要独立的自增主键lookup_id，直接使用联合主键实现索引组织表，存储开销更低，查询性能更高：

-- 删除冗余主键列
ALTER TABLE ensembl_lookup DROP COLUMN lookup_id;
-- 创建联合主键，天然支持feature_id到variant_id的覆盖查询
ALTER TABLE ensembl_lookup ADD PRIMARY KEY (feature_id, variant_id);
-- 创建反向联合索引，支持variant_id到feature_id的覆盖查询
CREATE INDEX ensembl_lookup_variant_feature_idx ON ensembl_lookup (variant_id, feature_id);
-- 删除原有无用的单列索引
DROP INDEX IF EXISTS ensembl_lookup_feature_id_index, ensembl_lookup_variant_id_index;

必要操作：更新统计信息 #

针对18亿行级的大表，更新统计信息让优化器生成更准确的执行计划：

ANALYZE VERBOSE ensembl_lookup;

效果预期 #

改造完成后，从ensembl_transcript入口的查询执行计划会替换为嵌套循环+复合索引扫描，无需全表扫描，执行时间可从278秒优化到百毫秒级别，同时原有从variant_fact入口的查询性能不受影响，甚至有小幅提升。

内容的提问来源于stack exchange，提问作者user11058068