explains select
    ps_suppkey
from
    lineitem,
    partsupp
where
    l_partkey = ps_partkey
    and l_suppkey = ps_suppkey
    and ps_partkey in (
        select
            p_partkey
        from
            part
    );

explain select
    ps_suppkey
from
    lineitem,
    partsupp
where
    l_partkey = ps_partkey
    and l_suppkey = ps_suppkey
    and ps_partkey in (
        select
            p_partkey
        from
            part where p_name like 'hot%'
    );

explain select
    ps_suppkey
from
    partsupp,
    (
        select
            l_partkey agg_partkey,
            l_suppkey agg_suppkey,
            0.5 * sum(l_quantity) AS agg_quantity
        from
            lineitem
        where
            l_shipdate >= date '1997-01-01'
            and l_shipdate < date '1997-01-01' + interval '1' year
        group by
            l_partkey,
            l_suppkey
    ) agg_lineitem
where
    agg_partkey = ps_partkey
    and agg_suppkey = ps_suppkey
    and ps_partkey in (
        select
            p_partkey
        from
            part
    );

explain select
    ps_suppkey
from
    partsupp,
    (
        select
            l_partkey agg_partkey,
            l_suppkey agg_suppkey,
            0.5 * sum(l_quantity) AS agg_quantity
        from
            lineitem
        where
            l_shipdate >= date '1997-01-01'
            and l_shipdate < date '1997-01-01' + interval '1' year
        group by
            l_partkey,
            l_suppkey
    ) agg_lineitem
where
    agg_partkey = ps_partkey
    and agg_suppkey = ps_suppkey
    and ps_partkey in (
        select
            p_partkey
        from
            part
        where 
            p_name like 'hot%'
    );
