感觉和having的子查询有关，有如下两个测试：
1、去掉子查询中的where条件形成笛卡尔积查询则执行计划一直运行跑不出结果：
explain
select
ps_partkey,
sum(ps_supplycost * ps_availqty) as value
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'GERMANY'
group by
ps_partkey having
sum(ps_supplycost * ps_availqty) > (
select
sum(ps_supplycost * ps_availqty) * 0.0000100000
from
partsupp,
supplier,
nation
-- where
-- ps_suppkey = s_suppkey
-- and s_nationkey = n_nationkey
-- and n_name = 'GERMANY'
)
order by
value desc;
2、去掉子查询中关联条件只保留partsupp，可以秒出：
explain
select
ps_partkey,
sum(ps_supplycost * ps_availqty) as value
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'GERMANY'
group by
ps_partkey having
sum(ps_supplycost * ps_availqty) > (
select
sum(ps_supplycost * ps_availqty) * 0.0000100000
from
partsupp
)
order by
value desc;
