为何DuckDB中含name子串过滤的GROUP BY查询性能大幅下降？

第一个查询快的原因 #

第一个查询里，type_str(type)作为SELECT列，DuckDB的优化器能识别到：分组后每个唯一的type值只需要调用一次type_str，不需要逐行执行。总调用次数等于表中不同type的数量，开销极低，所以速度快。

第二个查询慢的核心问题 #

虽然HAVING语法定义是分组后过滤，但DuckDB的查询优化器对引用了自定义Python UDF的别名做过滤时，出现了优化失效：它没有把name[0:3] = 'CAN'的判断推迟到分组后，反而提前到了分组前——也就是对表中每一行都调用type_str(type)，再判断子串是否符合，之后才进行分组。

Python UDF本身的调用开销远高于内置函数，逐行调用的话，哪怕表只有百万级数据，开销也会瞬间拉满，直接导致查询变慢。

可行的优化方案 #

• 方案1：在HAVING里直接用type计算，避免引用别名
  既然name是type_str(type)的结果，直接把判断逻辑换成基于type的调用，让优化器正确在分组后执行函数：

  select type_str(type) as name, type, count(*) as count 
  from objects
  group by type 
  having count > 1000 and type_str(type)[0:3] = 'CAN'
  order by count desc;
  

  这样每个分组只会调用一次type_str，开销回到和第一个查询相近的水平。

• 方案2：先分组过滤count，再在子查询中处理字符串判断
  先把符合count>1000的分组筛选出来，再对这些少量分组调用type_str并过滤，大幅减少函数调用次数：

  select type_str(type) as name, type, count
  from (
      select type, count(*) as count 
      from objects
      group by type 
      having count > 1000
  ) t
  where type_str(type)[0:3] = 'CAN'
  order by count desc;
  

• 方案3：替换Python UDF为内置逻辑
  如果枚举值数量不多，直接用CASE语句替代Python UDF，完全规避Python调用的额外开销：

  select 
      case type
          when 1 then 'CANADA'
          when 2 then 'CANYON'
          -- 补充其他枚举映射
          else 'UNKNOWN'
      end as name,
      type,
      count(*) as count
  from objects
  group by type 
  having count > 1000 and name[0:3] = 'CAN'
  order by count desc;
  

  也可以把Python UDF转换成DuckDB的内置UDF，性能会比Python版本提升很多。

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