[Bug]: Tpch5T query Q9 caused 3cn oom

[heni02]

Is there an existing issue for the same bug?

• I have checked the existing issues.

Branch Name

main

Commit ID

9304352

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

job:https://github.com/matrixorigin/mo-nightly-regression/actions/runs/10054767838/job/27819600253

heap:
https://grafana.ci.matrixorigin.cn/explore?panes=%7B%22LeX%22:%7B%22datasource%22:%22pyroscope%22,%22queries%22:%5B%7B%22groupBy%22:%5B%5D,%22labelSelector%22:%22%7Bnamespace%3D%5C%22mo-ben-nightly-93043522e-20240723%5C%22%7D%22,%22queryType%22:%22both%22,%22refId%22:%22A%22,%22profileTypeId%22:%22memory:alloc_objects:count:space:bytes%22,%22datasource%22:%7B%22type%22:%22grafana-pyroscope-datasource%22,%22uid%22:%22pyroscope%22%7D%7D%5D,%22range%22:%7B%22from%22:%221721793404415%22,%22to%22:%221721794680616%22%7D%7D%7D&schemaVersion=1&orgId=1
Q9:
select
nation,
o_year,
sum(amount) as sum_profit
from
(
select
n_name as nation,
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from
part,
supplier,
lineitem,
partsupp,
orders,
nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%:1%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc;

tpch5T table counts:
lineitem: 30000006798
customer: 750000000
partsupp: 4000000000
orders: 7500000000
part: 1000000000
supplier:50000000
nation:25
region:5

Expected Behavior

No response

Steps to Reproduce

tke 3cn tpch5T test

Additional information

No response

[badboynt1]

依赖spill的实现

[badboynt1]

现在可以验证一下，q9应该不会oom了
需要改一下gomemlimit调小到4g， gogc改回默认值100

[aressu1985]

fixed