PostgreSQL Trigram与btree索引对比及9.6版本慢查询优化咨询

一、索引优化建议 #

• 先清理无效重复索引：
  你当前重复创建了idx_cakes_cake_short_name索引，且基于lower(cake_short_name) varchar_pattern_ops的索引仅适用于前缀匹配的LIKE查询，对你使用的前后带通配符的ilike查询无优化效果，可直接删除这两个无效索引。
• 新增CakeViews表复合索引：
  原仅对cake_id建索引，而子查询同时过滤cake_id和createdAt，新增覆盖过滤条件的复合索引即可避免回表扫描：

  CREATE INDEX idx_cakeviews_cakeid_createdat ON public."CakeViews" (cake_id, "createdAt");
  

• 新增Cakes表部分GIN索引：
  你的查询大部分场景过滤has_recipe = true，基于该条件建部分索引体积更小、查询效率更高：

  CREATE INDEX idx_cakes_recipe_shortname_trgm ON public."Cakes" USING gin (cake_short_name gin_trgm_ops) WHERE has_recipe = true;
  CREATE INDEX idx_cakes_recipe_fullname_trgm ON public."Cakes" USING gin (cake_full_name gin_trgm_ops) WHERE has_recipe = true;
  

二、查询语句效率缺陷修复 #

1. WHERE条件逻辑错误导致全表扫描
   原查询的WHERE条件括号优先级错误，实际执行逻辑是：

   (has_recipe = true AND 名称匹配) OR 名称匹配
   

   等于后半部分的名称匹配完全没有has_recipe限制，会扫描所有符合名称条件的行，额外扫描大量无效数据。修正后逻辑：

   WHERE has_recipe = true 
   AND (
     cake_full_name ilike myquery OR cake_short_name ilike myquery
     OR cake_full_name ilike lower(queryLiteral) OR cake_short_name ilike lower(queryLiteral)
   )
   

2. 关联子查询导致的N+1查询问题
   原查询中统计浏览量的逻辑是每行Cakes数据都单独查一次CakeViews表，数据量大时性能极差。改成预聚合JOIN的方式，一次计算所有蛋糕的3天浏览量：

   -- 预聚合最近3天的浏览量
   WITH cake_views_stats AS (
     SELECT cake_id, count(*) as views 
     FROM "CakeViews" 
     WHERE "createdAt" > CURRENT_DATE - 3 
     GROUP BY cake_id
   ),
   myconstants (myquery, queryLiteral) as (
      values ('%a%', 'a')
   )
   SELECT
     count(*) OVER() AS full_count,
     c.cake_id,
     c.cake_short_name,
     c.cake_full_name,
     c.has_recipe,
     COALESCE(cvs.views, 0) as views
   FROM "Cakes" c
   CROSS JOIN myconstants
   LEFT JOIN cake_views_stats cvs ON c.cake_id = cvs.cake_id
   WHERE -- 替换为修正后的WHERE条件
   ORDER BY views desc,
     -- 原有排序逻辑保持不变
   LIMIT 10
   

3. 窗口函数全量扫描开销
   你使用的count(*) OVER()会强制扫描所有符合条件的行来统计总条数，哪怕仅返回10条结果。如果对总条数的精度要求不高，可考虑去掉该字段，或使用PostgreSQL的统计信息估算行数，能大幅提升小limit查询的速度。

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