set max_parallel_workers_per_gather = 0;

\d lineitem
\d part
\d partsupp

explain analyze select
        ps_suppkey
from
        partsupp,
        (
                select
                        l_partkey agg_partkey,
                        l_suppkey agg_suppkey
                from
                        lineitem
                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%'
        );

explain analyze select
        ps_suppkey
from
        partsupp,
        lineitem
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 analyze select
        *
from
		part,
        partsupp,
        lineitem
where
        l_partkey = ps_partkey
        and l_suppkey = ps_suppkey
        and ps_partkey = p_partkey
        and p_name like 'hot%';

ALTER TABLE LINEITEM ADD FOREIGN KEY (L_PARTKEY,L_SUPPKEY) REFERENCES PARTSUPP(PS_PARTKEY,PS_SUPPKEY);

\d lineitem
\d part
\d partsupp

explain analyze select
        ps_suppkey
from
        partsupp,
        (
                select
                        l_partkey agg_partkey,
                        l_suppkey agg_suppkey
                from
                        lineitem
                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%'
        );

explain analyze select
        ps_suppkey
from
        partsupp,
        lineitem
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 analyze select
        *
from
		part,
        partsupp,
        lineitem
where
        l_partkey = ps_partkey
        and l_suppkey = ps_suppkey
        and ps_partkey = p_partkey
        and p_name like 'hot%';


ALTER TABLE PARTSUPP ADD FOREIGN KEY (PS_PARTKEY) REFERENCES PART(P_PARTKEY);

\d lineitem
\d part
\d partsupp

explain analyze select
        ps_suppkey
from
        partsupp,
        (
                select
                        l_partkey agg_partkey,
                        l_suppkey agg_suppkey
                from
                        lineitem
                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%'
        );

explain analyze select
        ps_suppkey
from
        partsupp,
        lineitem
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 analyze select
        *
from
		part,
        partsupp,
        lineitem
where
        l_partkey = ps_partkey
        and l_suppkey = ps_suppkey
        and ps_partkey = p_partkey
        and p_name like 'hot%';

