SET SET SET QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=658057.31..658254.70 rows=1 width=90) (actual time=932.801..938.198 rows=1.00 loops=1) Buffers: shared hit=702782 read=445411 -> GroupAggregate (cost=658057.31..12530839.14 rows=60150 width=90) (actual time=932.799..938.196 rows=1.00 loops=1) Group Key: nation.n_name, (EXTRACT(year FROM orders.o_orderdate)) Buffers: shared hit=702782 read=445411 -> Incremental Sort (cost=658057.31..12498267.52 rows=1809678 width=81) (actual time=930.630..936.129 rows=11401.00 loops=1) Sort Key: nation.n_name, (EXTRACT(year FROM orders.o_orderdate)) DESC Presorted Key: nation.n_name Full-sort Groups: 1 Sort Method: quicksort Average Memory: 29kB Peak Memory: 29kB Pre-sorted Groups: 1 Sort Method: quicksort Average Memory: 12850kB Peak Memory: 12850kB Buffers: shared hit=702782 read=445411 -> Nested Loop (cost=165657.78..12329573.86 rows=1809678 width=81) (actual time=183.100..913.870 rows=128453.00 loops=1) Buffers: shared hit=702779 read=445411 -> Nested Loop (cost=165657.35..1638142.07 rows=1809678 width=53) (actual time=183.076..465.070 rows=128453.00 loops=1) Join Filter: (supplier.s_suppkey = lineitem.l_suppkey) Buffers: shared hit=354156 read=280226 -> Gather Merge (cost=165656.91..194423.15 rows=240250 width=48) (actual time=183.051..190.083 rows=17183.00 loops=1) Workers Planned: 4 Workers Launched: 4 Buffers: shared hit=313181 read=141200 -> Sort (cost=164656.85..164807.01 rows=60062 width=48) (actual time=180.188..180.255 rows=4034.80 loops=5) Sort Key: nation.n_name Sort Method: quicksort Memory: 9108kB Buffers: shared hit=313181 read=141200 Worker 0: Sort Method: quicksort Memory: 9233kB Worker 1: Sort Method: quicksort Memory: 9279kB Worker 2: Sort Method: quicksort Memory: 8847kB Worker 3: Sort Method: quicksort Memory: 9358kB -> Nested Loop (cost=3545.12..159889.68 rows=60062 width=48) (actual time=4.401..162.463 rows=86649.60 loops=5) Buffers: shared hit=313153 read=141200 -> Parallel Hash Join (cost=3544.97..158367.22 rows=60062 width=26) (actual time=4.390..153.190 rows=86649.60 loops=5) Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey) Buffers: shared hit=313020 read=141199 -> Nested Loop (cost=0.43..154663.57 rows=60611 width=18) (actual time=0.075..133.814 rows=86649.60 loops=5) Buffers: shared hit=313020 read=138978 -> Parallel Seq Scan on part (cost=0.00..47231.01 rows=15152 width=4) (actual time=0.034..30.053 rows=21662.40 loops=5) Filter: ((p_name)::text ~~ '%navy%'::text) Rows Removed by Filter: 378338 Buffers: shared read=40981 -> Index Scan using idx_partsupp_partkey on partsupp (cost=0.43..6.91 rows=18 width=14) (actual time=0.004..0.005 rows=4.00 loops=108312) Index Cond: (ps_partkey = part.p_partkey) Index Searches: 108312 Buffers: shared hit=313020 read=97997 -> Parallel Hash (cost=2809.24..2809.24 rows=58824 width=8) (actual time=4.210..4.211 rows=20000.00 loops=5) Buckets: 131072 Batches: 1 Memory Usage: 5056kB Buffers: shared read=2221 -> Parallel Seq Scan on supplier (cost=0.00..2809.24 rows=58824 width=8) (actual time=0.030..2.809 rows=20000.00 loops=5) Buffers: shared read=2221 -> Memoize (cost=0.15..2.17 rows=1 width=30) (actual time=0.000..0.000 rows=1.00 loops=433248) Cache Key: supplier.s_nationkey Cache Mode: logical Estimates: capacity=25 distinct keys=25 lookups=60062 hit percent=99.96% Hits: 85811 Misses: 25 Evictions: 0 Overflows: 0 Memory Usage: 4kB Buffers: shared hit=133 read=1 Worker 0: Hits: 87591 Misses: 25 Evictions: 0 Overflows: 0 Memory Usage: 4kB Worker 1: Hits: 88247 Misses: 25 Evictions: 0 Overflows: 0 Memory Usage: 4kB Worker 2: Hits: 82103 Misses: 25 Evictions: 0 Overflows: 0 Memory Usage: 4kB Worker 3: Hits: 89371 Misses: 25 Evictions: 0 Overflows: 0 Memory Usage: 4kB -> Index Scan using nation_pkey on nation (cost=0.14..2.16 rows=1 width=30) (actual time=0.001..0.001 rows=1.00 loops=125) Index Cond: (n_nationkey = supplier.s_nationkey) Index Searches: 125 Buffers: shared hit=133 read=1 -> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.44..6.00 rows=1 width=29) (actual time=0.004..0.016 rows=7.48 loops=17183) Index Cond: ((l_partkey = partsupp.ps_partkey) AND (l_suppkey = partsupp.ps_suppkey)) Index Searches: 17183 Buffers: shared hit=40975 read=139026 -> Index Scan using orders_pkey on orders (cost=0.43..5.91 rows=1 width=8) (actual time=0.003..0.003 rows=1.00 loops=128453) Index Cond: (o_orderkey = lineitem.l_orderkey) Index Searches: 128453 Buffers: shared hit=348623 read=165185 Planning: Buffers: shared hit=505 read=97 Planning Time: 3.825 ms Execution Time: 938.679 ms (74 rows) SET QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=496384.57..496565.79 rows=1 width=90) (actual time=105666.564..105700.162 rows=1.00 loops=1) Buffers: shared hit=901268686 read=2745398, temp read=87593 written=150932 -> GroupAggregate (cost=496384.57..11396537.77 rows=60150 width=90) (actual time=105666.563..105700.161 rows=1.00 loops=1) Group Key: nation.n_name, EXTRACT(year FROM orders.o_orderdate) Buffers: shared hit=901268686 read=2745398, temp read=87593 written=150932 -> Nested Loop (cost=496384.57..11363966.15 rows=1809678 width=81) (actual time=105627.541..105697.643 rows=11401.00 loops=1) Join Filter: (supplier.s_suppkey = partsupp.ps_suppkey) Buffers: shared hit=901268686 read=2745398, temp read=87593 written=150932 -> Gather Merge (cost=496384.14..705266.22 rows=1809678 width=63) (actual time=105627.514..105662.541 rows=11401.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=901238156 read=2730512, temp read=87593 written=150932 -> Sort (cost=495384.12..495384.45 rows=135 width=63) (actual time=102969.522..102970.172 rows=4229.00 loops=3) Sort Key: nation.n_name, (EXTRACT(year FROM orders.o_orderdate)) DESC Sort Method: quicksort Memory: 1414kB Buffers: shared hit=901238156 read=2730512, temp read=87593 written=150932 Worker 0: Sort Method: external merge Disk: 255288kB Worker 1: Sort Method: quicksort Memory: 1453kB -> Nested Loop (cost=490981.32..495379.34 rows=135 width=63) (actual time=102147.254..102616.361 rows=1082730.67 loops=3) Join Filter: (supplier.s_nationkey = nation.n_nationkey) Rows Removed by Join Filter: 13003040 Buffers: shared hit=901238133 read=2730507, temp read=79631 written=119016 -> Merge Join (cost=490981.32..495329.13 rows=135 width=41) (actual time=102147.213..102220.035 rows=1082730.67 loops=3) Merge Cond: (supplier.s_suppkey = lineitem.l_suppkey) Buffers: shared hit=901238131 read=2730506, temp read=79631 written=119016 -> Parallel Index Scan using supplier_pkey on supplier (cost=0.29..4240.96 rows=41667 width=8) (actual time=0.012..2.740 rows=33333.33 loops=3) Index Searches: 1 Buffers: shared hit=3 read=2496 -> Sort (cost=490981.03..490981.84 rows=326 width=33) (actual time=102146.693..102172.372 rows=1098622.33 loops=3) Sort Key: lineitem.l_suppkey Sort Method: external sort Disk: 158680kB Buffers: shared hit=901238128 read=2728010, temp read=79631 written=119016 Worker 0: Sort Method: external sort Disk: 158680kB Worker 1: Sort Method: external sort Disk: 158680kB -> Nested Loop (cost=0.99..490967.42 rows=326 width=33) (actual time=0.068..101484.996 rows=3248192.00 loops=3) Buffers: shared hit=901238121 read=2728009 -> Nested Loop (cost=0.56..428042.39 rows=10770 width=29) (actual time=0.035..17748.686 rows=59986052.00 loops=3) Buffers: shared hit=181456410 read=2681546 -> Seq Scan on orders (cost=0.00..411276.24 rows=2693 width=8) (actual time=0.026..465.963 rows=15000000.00 loops=3) Buffers: shared hit=244154 read=518371 -> Index Scan using idx_lineitem_orderkey on lineitem (cost=0.56..6.19 rows=4 width=29) (actual time=0.001..0.001 rows=4.00 loops=45000000) Index Cond: (l_orderkey = orders.o_orderkey) Index Searches: 45000000 Buffers: shared hit=181212256 read=2163175 -> Index Scan using part_pkey on part (cost=0.43..5.84 rows=1 width=4) (actual time=0.001..0.001 rows=0.05 loops=179958156) Index Cond: (p_partkey = lineitem.l_partkey) Filter: ((p_name)::text ~~ '%navy%'::text) Rows Removed by Filter: 1 Index Searches: 179958156 Buffers: shared hit=719781711 read=46463 -> Materialize (cost=0.00..1.38 rows=25 width=30) (actual time=0.000..0.000 rows=13.01 loops=3248192) Storage: Memory Maximum Storage: 18kB Buffers: shared hit=2 read=1 -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=30) (actual time=0.030..0.030 rows=25.00 loops=3) Buffers: shared hit=2 read=1 -> Index Scan using partsupp_pkey on partsupp (cost=0.43..5.87 rows=1 width=14) (actual time=0.003..0.003 rows=1.00 loops=11401) Index Cond: ((ps_partkey = lineitem.l_partkey) AND (ps_suppkey = lineitem.l_suppkey)) Index Searches: 11401 Buffers: shared hit=30530 read=14886 Planning: Buffers: shared hit=58 read=40 Planning Time: 6.404 ms Execution Time: 105708.188 ms (63 rows)