Presto中如何优先执行子查询并将结果复用于外层查询

Presto默认会对IN子查询做半连接优化，当外层表Table1远小于内层过滤后的结果集时，优化器可能优先调度外层查询执行，你可以通过以下三种方式强制先执行内层逻辑：

方法1：使用物化CTE（推荐，Presto 0.237及以上版本支持） #

直接将内层查询定义为物化CTE，Presto会优先执行并缓存CTE的结果，再应用到外层查询，语法示例：

WITH filtered_numid AS MATERIALIZED (
    SELECT NUMid 
    FROM Table2 
    WHERE email = 'xyz@gmail.com'
)
SELECT * 
FROM Table1 
WHERE NUMid IN (SELECT NUMid FROM filtered_numid)

方法2：使用临时表存储内层结果 #

如果你的Presto版本不支持物化CTE，可以显式创建临时表存储内层查询的结果，再执行外层查询：

-- 第一步：创建临时表存储筛选后的NUMid，会话结束后自动删除
CREATE TEMP TABLE temp_filtered_numid AS
SELECT NUMid 
FROM Table2 
WHERE email = 'xyz@gmail.com';

-- 第二步：用临时表做过滤条件查询Table1
SELECT * 
FROM Table1 
WHERE NUMid IN (SELECT NUMid FROM temp_filtered_numid);

方法3：使用JOIN + 广播提示强制执行顺序 #

如果内层筛选后的结果集很小，你可以改用INNER JOIN搭配广播提示，强制Presto先拉取小的内层结果集，再关联外层大表：

SELECT /*+ BROADCAST(t2) */ t1.*
FROM Table1 t1
INNER JOIN (
    SELECT DISTINCT NUMid 
    FROM Table2 
    WHERE email = 'xyz@gmail.com'
) t2 ON t1.NUMid = t2.NUMid

注意这里加了DISTINCT是为了避免Table2中同一个NUMid出现多次导致结果重复，和原IN查询的逻辑保持一致。

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