20_patch.out
application/octet-stream
Filename: 20_patch.out
Type: application/octet-stream
Part: 1
Message:
Proposal : Parallel Merge Join
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3680900.21..3680900.21 rows=1 width=51) (actual time=45616.560..45616.560 rows=1 loops=1)
-> Sort (cost=3680900.21..3680905.21 rows=2000 width=51) (actual time=45616.558..45616.558 rows=1 loops=1)
Sort Key: supplier.s_name
Sort Method: top-N heapsort Memory: 25kB
-> Gather (cost=3408638.46..3680890.21 rows=2000 width=51) (actual time=21561.425..45613.685 rows=2326 loops=1)
Workers Planned: 3
Workers Launched: 3
-> Hash Join (cost=3407638.46..3679690.21 rows=2000 width=51) (actual time=21243.551..44355.465 rows=582 loops=4)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
-> Nested Loop Semi Join (cost=3405052.88..3677103.30 rows=320 width=16) (actual time=21169.958..44336.816 rows=14725 loops=4)
Join Filter: (lineitem.l_partkey = part.p_partkey)
-> Merge Join (cost=3405052.45..3676948.66 rows=320 width=32) (actual time=21165.849..37814.551 rows=1357812 loops=4)
Merge Cond: ((partsupp.ps_partkey = lineitem.l_partkey) AND (partsupp.ps_suppkey = lineitem.l_suppkey))
Join Filter: ((partsupp.ps_availqty)::numeric > ((0.5 * sum(lineitem.l_quantity))))
Rows Removed by Join Filter: 2876
-> Sort (cost=536799.62..543251.23 rows=2580645 width=20) (actual time=1816.420..2279.836 rows=2000000 loops=4)
Sort Key: partsupp.ps_partkey, partsupp.ps_suppkey
Sort Method: external merge Disk: 66952kB
-> Parallel Seq Scan on partsupp (cost=0.00..209046.45 rows=2580645 width=20) (actual time=0.022..583.189 rows=2000000 loops=4)
-> Materialize (cost=2868252.83..3096312.87 rows=4895341 width=48) (actual time=19349.283..32738.973 rows=5348658 loops=4)
-> GroupAggregate (cost=2868252.83..3035121.11 rows=4895341 width=48) (actual time=19349.274..31155.442 rows=5348658 loops=4)
Group Key: lineitem.l_partkey, lineitem.l_suppkey
-> Sort (cost=2868252.83..2891612.37 rows=9343816 width=21) (actual time=19349.239..22424.498 rows=8952058 loops=4)
Sort Key: lineitem.l_partkey, lineitem.l_suppkey
Sort Method: external merge Disk: 275960kB
-> Bitmap Heap Scan on lineitem (cost=239686.68..1594823.92 rows=9343816 width=21) (actual time=2364.268..10758.368 rows=9109641 loops=4)
Recheck Cond: ((l_shipdate >= '1993-01-01'::date) AND (l_shipdate < '1994-01-01 00:00:00'::timestamp without time zone))
Heap Blocks: exact=1114490
-> Bitmap Index Scan on idx_lineitem_shipdate (cost=0.00..237350.73 rows=9343816 width=0) (actual time=1901.240..1901.240 rows=9109641 loops=4)
Index Cond: ((l_shipdate >= '1993-01-01'::date) AND (l_shipdate < '1994-01-01 00:00:00'::timestamp without time zone))
-> Index Scan using part_pkey on part (cost=0.43..0.47 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=5431247)
Index Cond: (p_partkey = partsupp.ps_partkey)
Filter: ((p_name)::text ~~ 'snow%'::text)
Rows Removed by Filter: 1
-> Hash (cost=2535.58..2535.58 rows=4000 width=55) (actual time=12.637..12.637 rows=3991 loops=4)
Buckets: 4096 Batches: 1 Memory Usage: 381kB
-> Nested Loop (cost=79.29..2535.58 rows=4000 width=55) (actual time=0.796..11.026 rows=3991 loops=4)
-> Seq Scan on nation (cost=0.00..1.31 rows=1 width=4) (actual time=0.016..0.019 rows=1 loops=4)
Filter: (n_name = 'PERU'::bpchar)
Rows Removed by Filter: 24
-> Bitmap Heap Scan on supplier (cost=79.29..2494.27 rows=4000 width=63) (actual time=0.777..10.074 rows=3991 loops=4)
Recheck Cond: (s_nationkey = nation.n_nationkey)
Heap Blocks: exact=1907
-> Bitmap Index Scan on idx_supplier_nation_key (cost=0.00..78.29 rows=4000 width=0) (actual time=0.541..0.541 rows=3991 loops=4)
Index Cond: (s_nationkey = nation.n_nationkey)
Planning time: 2.017 ms
Execution time: 45662.844 ms
(47 rows)
s_name | s_address
---------------------------+--------------------------------
Supplier#000000008 | 9Sq4bBH2FQEmaFOocY45sRTxo6yuoG
(1 row)