PostgreSQL中SQL Join查询优化及索引设计方案咨询

针对该查询的额外优化方案 #

你计划创建的A表部分联合索引已经能覆盖大部分过滤和排序需求，还可以通过以下方式进一步提升性能：

• 优化A表索引为覆盖索引
  原索引只包含user_id和created_at，查询时还需要回表查询b_id字段用于关联B表。可以把b_id加入索引，同时明确指定created_at排序方向匹配查询要求，最终索引规则调整为：

  CREATE INDEX idx_a_user_created_b_pending ON A (user_id, created_at DESC, b_id) WHERE type = 'PENDING';
  

  调整后可以直接从索引中拿到所有关联需要的A表字段，完全避免回表IO开销。如果业务不需要返回A表的所有字段，还可以把查询需要的其他A表字段也加入索引末尾，实现全量覆盖。
• 新增B表联合索引
  因为B表需要根据可变参数provider_id过滤，还要返回id字段用于和A表关联，可以创建如下联合索引：

  CREATE INDEX idx_b_provider_id ON B (provider_id, id);
  

  该索引可以直接完成B表的过滤和关联字段提取，不需要回表查询B表数据，即使B表数据量小也能进一步降低过滤耗时。
• 改写查询优先过滤小表
  利用B表数据量远小于A表的特点，先过滤出B表符合条件的记录再关联A表，减少join阶段的匹配次数，改写后参考SQL：

  SELECT A.*, B.*
  FROM (
      SELECT * FROM B WHERE provider_id = 'XXX'
  ) filtered_B
  INNER JOIN A 
      ON A.b_id = filtered_B.id
      AND A.user_id = 'XXX'
      AND A.type = 'PENDING'
  ORDER BY A.created_at DESC
  LIMIT 1;
  

• 避免全字段查询
  尽量把SELECT *替换为业务实际需要的字段列表，减少数据传输和IO开销，也更便于扩展覆盖索引。
• 高频查询场景加缓存
  如果该查询访问频率很高，且业务允许一定的延迟，可以缓存固定user_id+provider_id组合的查询结果，过期时间根据业务对数据实时性的要求设置，进一步降低数据库压力。

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