能否通过索引提升PostgreSQL LEFT OUTER JOIN查询性能？

我运营一个图片托管网站，数据库包含image、user表，以及记录多对多关系的collaborators表（一张图片可关联多个协作用户）。当前使用PostgreSQL执行以下查询，用于找出用户999是所有者或协作者的所有图片：

SELECT image.id
FROM image
LEFT OUTER JOIN collaborators ON (image.id = collaborators.image_id)
WHERE (image.user_id = 999 OR collaborators.user_id = 999)

数据库约有10万用户、60万图片，collaborators表条目极少，但查询耗时超4秒。执行计划如下：

Hash Right Join  (cost=107937.02..110540.78 rows=3318 width=4) (actual time=2928.556..4428.901 rows=25 loops=1)
  Hash Cond: (collaborators.image_id = image.id)
  Filter: ((image.user_id = 999) OR (collaborators.user_id = 999))
  Rows Removed by Filter: 653094
  ->  Seq Scan on collaborators  (cost=0.00..30.40 rows=2040 width=8) (actual time=0.013..0.016 rows=8 loops=1)
  ->  Hash  (cost=97220.01..97220.01 rows=653201 width=8) (actual time=2905.239..2905.240 rows=653119 loops=1)
        Buckets: 131072  Batches: 16  Memory Usage: 2623kB
        ->  Seq Scan on image  (cost=0.00..97220.01 rows=653201 width=8) (actual time=0.016..1689.448 rows=653119 loops=1)
Planning time: 0.228 ms
Execution time: 4429.183 ms

由于SQL由Django生成无法修改，可通过以下索引优化查询性能：

索引优化方案 #

1. 为image表创建覆盖索引 #

当前查询需要筛选image.user_id = 999的记录，同时要获取image.id用于关联。创建覆盖索引可以让数据库直接从索引取数，避免全表扫描：

CREATE INDEX idx_image_user_id_id ON image (user_id) INCLUDE (id);

这个索引能快速定位用户999的所有图片，且直接返回id字段，无需回表查询额外数据。

2. 为collaborators表创建复合索引 #

查询需要通过collaborators.user_id = 999找到关联图片ID，再和image表关联。创建复合索引：

CREATE INDEX idx_collaborators_user_id_image_id ON collaborators (user_id, image_id);

该索引能快速定位用户999作为协作者的所有image_id，且因为包含image_id，可直接用于关联查询，避免扫描整个collaborators表。

3. 验证优化效果 #

创建索引后重新执行查询，查看执行计划：

• 应不再出现image表的全表扫描（Seq Scan），替换为索引扫描（Index Scan）
• collaborators表的扫描也会变成索引扫描，返回行数大幅减少

优化原理 #

当前执行计划中，数据库对65万行的image表做全表扫描，再通过哈希连接和过滤筛选出仅25条有效数据，大部分时间浪费在无效数据的扫描和过滤上。通过上述索引：

• idx_image_user_id_id直接定位用户999的所有图片，跳过全表扫描
• idx_collaborators_user_id_image_id快速找到用户999作为协作者的图片ID，再关联image表
• 两个索引配合后，数据库能高效合并所有者和协作者的图片集合，大幅缩短查询时间

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