PostgreSQL索引优化：加速LEFT OUTER JOIN查询

我有PostgreSQL数据库中的两个表：prediction_fsd（约500万条数据）和site（约300万条数据），执行以下LEFT OUTER JOIN查询时耗时约4秒，希望通过创建索引优化性能，不清楚该在哪些表和字段建索引：

SELECT prediction_fsd.id AS prediction_fsd_id, 
       prediction_fsd.site_id AS prediction_fsd_site_id, 
       prediction_fsd.html_hash AS prediction_fsd_html_hash, 
       prediction_fsd.prediction AS prediction_fsd_prediction, 
       prediction_fsd.algorithm AS prediction_fsd_algorithm, 
       prediction_fsd.model_version AS prediction_fsd_model_version,
       prediction_fsd.timestamp AS prediction_fsd_timestamp, 
       site_1.id AS site_1_id, 
       site_1.url AS site_1_url, 
       site_1.status AS site_1_status 
  FROM prediction_fsd
  LEFT OUTER JOIN site AS site_1
         ON site_1.id = prediction_fsd.site_id 
 WHERE 95806 = prediction_fsd.site_id
   AND prediction_fsd.algorithm = 'xgboost'
 ORDER BY prediction_fsd.timestamp DESC 
 LIMIT 1

索引创建建议 #

• 针对prediction_fsd表：创建包含过滤条件、排序字段的联合覆盖索引

  CREATE INDEX idx_pred_fsd_site_alg_ts ON prediction_fsd (site_id, algorithm, timestamp DESC)
  INCLUDE (id, html_hash, prediction, model_version);
  

  这个索引的作用：

  • 利用site_id和algorithm快速过滤出符合WHERE条件的记录
  • 按timestamp DESC排序的结构可以直接定位到最新的一条数据，避免额外排序操作
  • INCLUDE子句包含查询需要的其他字段，实现覆盖索引，无需回表查询原表数据，进一步提升效率

• 针对site表：确保id字段有索引
  如果site.id不是主键（主键默认自带唯一索引），需要创建索引：

  CREATE UNIQUE INDEX idx_site_id ON site (id);
  

  主键索引或唯一索引可以让JOIN操作快速定位到关联的site记录，避免全表扫描site表

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