如何优化含跨表排序的PostgreSQL关联查询性能？

查询语句 #

select "vehicles".* from "vehicles"
left join "users" on "vehicles"."user_id" = "users"."id" 
where "vehicles"."status" not in ('draft', 'deactivated') and "vehicles"."auction_id" = 292 
order by users.dealership_name ASC, vehicles.year ASC, vehicles.make ASC, vehicles.model ASC, vehicles.vin ASC;

问题描述 #

我想优化这个查询的性能。除了WHERE和JOIN条件对应的索引外，我还分别创建了users.dealership_name ASC和vehicles.year ASC, vehicles.make ASC, vehicles.model ASC, vehicles.vin ASC这两个索引，但查询并未使用它们。我知道反范式化可以解决问题，但重构成本太高，希望找到更简单的优化方案。附EXPLAIN ANALYZE查询结果：查询执行时进行了显式Sort操作，未触发上述两个排序相关索引。

优化方案 #

• 创建联合覆盖索引，减少回表与排序开销
  单独的排序索引无法和筛选、关联逻辑结合，优化器会认为直接排序结果集成本更低。可以创建包含筛选、关联、排序字段的联合索引：

  • 针对vehicles表：

    CREATE INDEX idx_vehicles_auction_status_user_sort ON vehicles(auction_id, status, user_id, year, make, model, vin);
    

    该索引包含WHERE条件的auction_id、status，JOIN所需的user_id，以及排序用的year、make、model、vin。数据库可以直接通过索引筛选数据，同时获取排序字段，无需回表读取全表数据，也避免了后续的Sort操作。
  • 针对users表：

    CREATE INDEX idx_users_id_dealership ON users(id, dealership_name);
    

    该索引包含JOIN的id和排序用的dealership_name，关联时无需回表查询用户表其他字段，直接从索引获取排序所需数据。

• 更新表统计信息
  执行以下命令更新表的统计信息，让优化器能准确评估索引使用成本：

  ANALYZE vehicles;
  ANALYZE users;
  

• 谨慎使用索引强制提示
  如果优化器仍未选择目标索引，可以尝试用FORCE INDEX临时指定，但优先依赖优化器的自动选择：

  select "vehicles".* from "vehicles"
  left join "users" FORCE INDEX (idx_users_id_dealership) on "vehicles"."user_id" = "users"."id" 
  where "vehicles"."status" not in ('draft', 'deactivated') and "vehicles"."auction_id" = 292 
  order by users.dealership_name ASC, vehicles.year ASC, vehicles.make ASC, vehicles.model ASC, vehicles.vin ASC;
  

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