tpch=# \d lineitem; Table "public.lineitem" Column | Type | Collation | Nullable | Default -----------------+-----------------------+-----------+----------+--------- l_orderkey | integer | | not null | l_partkey | integer | | not null | l_suppkey | integer | | not null | l_linenumber | integer | | not null | l_quantity | numeric | | | l_extendedprice | numeric | | | l_discount | numeric | | | l_tax | numeric | | | l_returnflag | character(1) | | | l_linestatus | character(1) | | | l_shipdate | date | | | l_commitdate | date | | | l_receiptdate | date | | | l_shipinstruct | character varying(25) | | | l_shipmode | character varying(10) | | | l_comment | character varying(44) | | | Indexes: "lineitem_pkey" PRIMARY KEY, btree (l_orderkey, l_linenumber) "idx_lineitem_orderkey" btree (l_orderkey) "idx_lineitem_part_supp" btree (l_partkey, l_suppkey) "idx_lineitem_shipdate" btree (l_shipdate, l_discount, l_quantity) Foreign-key constraints: "lineitem_l_orderkey_fkey" FOREIGN KEY (l_orderkey) REFERENCES orders(o_orderkey) "lineitem_l_partkey_fkey" FOREIGN KEY (l_partkey, l_suppkey) REFERENCES partsupp(ps_partkey, ps_suppkey) tpch=# \d part Table "public.part" Column | Type | Collation | Nullable | Default ---------------+-----------------------+-----------+----------+----------------------------------------- p_partkey | integer | | not null | nextval('part_p_partkey_seq'::regclass) p_name | character varying(55) | | | p_mfgr | character(25) | | | p_brand | character(10) | | | p_type | character varying(25) | | | p_size | integer | | | p_container | character(10) | | | p_retailprice | numeric | | | p_comment | character varying(23) | | | Indexes: "part_pkey" PRIMARY KEY, btree (p_partkey) tpch=# \d partsupp Table "public.partsupp" Column | Type | Collation | Nullable | Default ---------------+------------------------+-----------+----------+--------- ps_partkey | integer | | not null | ps_suppkey | integer | | not null | ps_availqty | integer | | | ps_supplycost | numeric | | | ps_comment | character varying(199) | | | Indexes: "partsupp_pkey" PRIMARY KEY, btree (ps_partkey, ps_suppkey) "idx_partsupp_partkey" btree (ps_partkey) "idx_partsupp_suppkey" btree (ps_suppkey) Foreign-key constraints: "partsupp_ps_suppkey_fkey" FOREIGN KEY (ps_suppkey) REFERENCES supplier(s_suppkey) Referenced by: TABLE "lineitem" CONSTRAINT "lineitem_l_partkey_fkey" FOREIGN KEY (l_partkey, l_suppkey) REFERENCES partsupp(ps_partkey, ps_suppkey) tpch=# set max_parallel_workers_per_gather = 0; SET tpch=# 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%' ); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=172852.62..2123144.13 rows=524 width=4) (actual time=400.426..16490.077 rows=86801 loops=1) Merge Cond: ((lineitem.l_partkey = partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey)) -> Group (cost=0.56..1857642.81 rows=5998611 width=8) (actual time=0.025..15634.679 rows=7995644 loops=1) Group Key: lineitem.l_partkey, lineitem.l_suppkey -> Index Only Scan using idx_lineitem_part_supp on lineitem (cost=0.56..1557712.25 rows=59986112 width=8) (actual time=0.023..11362.271 rows=59983724 loops=1) Heap Fetches: 0 -> Sort (cost=172852.06..173741.94 rows=355951 width=12) (actual time=399.428..408.893 rows=86836 loops=1) Sort Key: partsupp.ps_partkey, partsupp.ps_suppkey Sort Method: quicksort Memory: 7143kB -> Nested Loop (cost=0.43..140031.03 rows=355951 width=12) (actual time=0.048..374.713 rows=86836 loops=1) -> Seq Scan on part (cost=0.00..65961.68 rows=20202 width=4) (actual time=0.024..294.117 rows=21709 loops=1) Filter: ((p_name)::text ~~ 'hot%'::text) Rows Removed by Filter: 1978291 -> Index Only Scan using partsupp_pkey on partsupp (cost=0.43..3.49 rows=18 width=8) (actual time=0.003..0.003 rows=4 loops=21709) Index Cond: (ps_partkey = part.p_partkey) Heap Fetches: 0 Planning time: 0.765 ms Execution time: 16493.032 ms (18 rows) tpch=# set max_parallel_workers_per_gather = 8; SET tpch=# 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%' ); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=131617.44..2094902.90 rows=131 width=4) (actual time=161.644..17339.329 rows=86801 loops=1) Merge Cond: ((partsupp.ps_partkey = lineitem.l_partkey) AND (partsupp.ps_suppkey = lineitem.l_suppkey)) -> Sort (cost=131616.88..131839.35 rows=88988 width=12) (actual time=160.581..170.669 rows=86836 loops=1) Sort Key: partsupp.ps_partkey, partsupp.ps_suppkey Sort Method: quicksort Memory: 7143kB -> Nested Loop (cost=1000.43..124301.47 rows=88988 width=12) (actual time=0.456..122.994 rows=86836 loops=1) -> Gather (cost=1000.00..50232.12 rows=20202 width=4) (actual time=0.437..30.468 rows=21709 loops=1) Workers Planned: 4 Workers Launched: 4 -> Parallel Seq Scan on part (cost=0.00..47211.92 rows=5050 width=4) (actual time=0.078..110.786 rows=4342 loops=5) Filter: ((p_name)::text ~~ 'hot%'::text) Rows Removed by Filter: 395658 -> Index Only Scan using partsupp_pkey on partsupp (cost=0.43..3.49 rows=18 width=8) (actual time=0.003..0.004 rows=4 loops=21709) Index Cond: (ps_partkey = part.p_partkey) Heap Fetches: 0 -> Materialize (cost=0.56..1932625.44 rows=5998611 width=8) (actual time=0.029..16521.530 rows=7995644 loops=1) -> Group (cost=0.56..1857642.81 rows=5998611 width=8) (actual time=0.024..15703.698 rows=7995644 loops=1) Group Key: lineitem.l_partkey, lineitem.l_suppkey -> Index Only Scan using idx_lineitem_part_supp on lineitem (cost=0.56..1557712.25 rows=59986112 width=8) (actual time=0.023..11412.152 rows=59983724 loops=1) Heap Fetches: 0 Planning time: 0.912 ms Execution time: 17342.450 ms (22 rows)