如何通过小查询表过滤大表且避免扫描整个大表

问题原因 #

你遇到的问题核心是查询优化器没有触发动态分区裁剪：
当你使用硬编码的IN列表时，优化器在编译阶段就明确知道需要匹配的PATHUUID值，结合大表TAGVALUES按PATHUUID分区/聚类的元数据，可以直接计算出需要扫描的分区范围，跳过无关分区。
但当你用关联或者CTE的方式时，默认优化器会选择先执行关联逻辑，编译阶段无法确定最终要匹配的PATHUUID集合，也就没法提前做分区裁剪，只能全表扫描大表后再过滤匹配结果。

可行解决方案 #

方案1：改用IN子查询（主流云数仓通用） #

现在大部分云数仓（Snowflake、BigQuery、Spark SQL 3.0+）都支持子查询结果下推的动态分区裁剪，直接把小表过滤逻辑写在IN子句中即可：

EXPLAIN
SELECT count(*)
FROM TAGVALUES v
WHERE v.PATHUUID IN (
  SELECT PATHUUID FROM TAGMAP t WHERE t.PATHTYPE = 'TYPE10'
)

执行后查看partitionsAssigned参数，应该和硬编码IN列表的扫描范围一致。

方案2：先固化过滤值到会话变量（全兼容方案） #

如果你的数仓不支持子查询下推，可以先把过滤值查询出来存为会话变量，再代入过滤逻辑：

-- 先将过滤的UUID集合存入数组变量
SET filter_uuids = (
  SELECT ARRAY_AGG(PATHUUID) 
  FROM TAGMAP t 
  WHERE t.PATHTYPE = 'TYPE10'
);

-- 用数组包含判断触发分区裁剪
EXPLAIN
SELECT count(*)
FROM TAGVALUES v
WHERE ARRAY_CONTAINS(v.PATHUUID::VARIANT, $filter_uuids);

方案3：关联场景加下推优化提示 #

如果业务逻辑必须使用关联写法，可以给优化器增加强制下推的提示（以下为Snowflake语法，其他数仓可查对应下推提示规则）：

EXPLAIN
SELECT /*+ PUSH_PRED(v) */ count(*)
FROM TAGVALUES v
INNER JOIN TAGMAP t
ON v.PATHUUID = t.PATHUUID
WHERE t.PATHTYPE = 'TYPE10';

PUSH_PRED提示会强制优化器把小表的过滤条件下推到大表的扫描阶段，触发分区裁剪。

注意事项 #

• 请确认大表TAGVALUES的分区键/聚类键为PATHUUID，如果分区键不是过滤字段，无论怎么调整查询逻辑都无法触发分区裁剪。
• 小表返回的过滤UUID数量不要过大，若返回值超过万级，优化器可能判定全表扫描效率更高，自动放弃分区裁剪逻辑。

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