ds_q45.txt
text/plain
On head:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=539939.00..539941.25 rows=100 width=46) (actual time=35194.017..35194.358 rows=100 loops=1)
-> GroupAggregate (cost=539939.00..541004.96 rows=47376 width=46) (actual time=35194.015..35194.345 rows=100 loops=1)
Group Key: customer_address.ca_zip, customer_address.ca_state
-> Sort (cost=539939.00..540057.44 rows=47376 width=20) (actual time=35193.994..35194.019 rows=101 loops=1)
Sort Key: customer_address.ca_zip, customer_address.ca_state
Sort Method: quicksort Memory: 48kB
-> Hash Join (cost=32899.51..536259.81 rows=47376 width=20) (actual time=1735.259..35192.430 rows=303 loops=1)
Hash Cond: (web_sales.ws_item_sk = item.i_item_sk)
Join Filter: ((substr((customer_address.ca_zip)::text, 1, 5) = ANY ('{85669,86197,88274,83405,86475,85392,85460,80348,81792}'::text[])) OR (hashed SubPlan 1))
Rows Removed by Join Filter: 2070923
-> Hash Join (cost=26911.21..527324.70 rows=90671 width=24) (actual time=1406.355..25817.821 rows=2071226 loops=1)
Hash Cond: (customer.c_current_addr_sk = customer_address.ca_address_sk)
-> Nested Loop (cost=2237.81..501404.58 rows=90671 width=14) (actual time=48.696..22331.297 rows=2071226 loops=1)
-> Gather (cost=2237.39..446823.34 rows=90671 width=14) (actual time=48.657..1736.963 rows=2071511 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=1237.38..436756.24 rows=90671 width=14) (actual time=52.428..10749.067 rows=414302 loops=5)
Hash Cond: (web_sales.ws_sold_date_sk = date_dim.d_date_sk)
-> Parallel Seq Scan on web_sales (cost=0.00..367798.12 rows=17998432 width=18) (actual time=0.035..5528.430 rows=14400247 loops=5)
-> Hash (cost=1236.23..1236.23 rows=92 width=4) (actual time=51.864..51.864 rows=91 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on date_dim (cost=0.00..1236.23 rows=92 width=4) (actual time=25.196..51.808 rows=91 loops=5)
Filter: ((d_qoy = 2) AND (d_year = 1999))
Rows Removed by Filter: 72958
-> Index Scan using customer_pkey on customer (cost=0.43..0.59 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=2071511)
Index Cond: (c_customer_sk = web_sales.ws_bill_customer_sk)
-> Hash (cost=12173.40..12173.40 rows=1000000 width=18) (actual time=1356.992..1356.992 rows=1000000 loops=1)
Buckets: 1048576 Batches: 1 Memory Usage: 58500kB
-> Seq Scan on customer_address (cost=0.00..12173.40 rows=1000000 width=18) (actual time=0.021..688.872 rows=1000000 loops=1)
-> Hash (cost=3432.80..3432.80 rows=204000 width=21) (actual time=263.624..263.624 rows=204000 loops=1)
Buckets: 262144 Batches: 1 Memory Usage: 12607kB
-> Seq Scan on item (cost=0.00..3432.80 rows=204000 width=21) (actual time=0.008..146.867 rows=204000 loops=1)
SubPlan 1
-> Index Scan using item_pkey on item item_1 (cost=0.42..5.48 rows=10 width=17) (actual time=0.026..0.092 rows=10 loops=1)
Index Cond: (i_item_sk = ANY ('{2,3,5,7,11,13,17,19,23,29}'::integer[]))
Planning time: 3.126 ms
Execution time: 35195.336 ms
(37 rows)
With patches:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----
Limit (cost=533081.38..533083.63 rows=100 width=46) (actual time=19506.763..19507.347 rows=100 loops=1)
-> GroupAggregate (cost=533081.38..534147.34 rows=47376 width=46) (actual time=19506.760..19507.322 rows=100 loops=1)
Group Key: customer_address.ca_zip, customer_address.ca_state
-> Sort (cost=533081.38..533199.82 rows=47376 width=20) (actual time=19506.710..19506.752 rows=101 loops=1)
Sort Key: customer_address.ca_zip, customer_address.ca_state
Sort Method: quicksort Memory: 48kB
-> Gather (cost=83592.41..529402.19 rows=47376 width=20) (actual time=5199.329..19505.115 rows=303 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=82592.41..523664.59 rows=47376 width=20) (actual time=5236.517..19493.461 rows=61 loops=5)
Hash Cond: (web_sales.ws_item_sk = item.i_item_sk)
Join Filter: ((substr((customer_address.ca_zip)::text, 1, 5) = ANY ('{85669,86197,88274,83405,86475,85392,85460,80348,81792}'::text[])) OR (hashed SubPlan
1))
Rows Removed by Join Filter: 414185
-> Hash Join (cost=76604.11..514729.49 rows=90671 width=24) (actual time=4710.986..17176.452 rows=414245 loops=5)
Hash Cond: (customer.c_current_addr_sk = customer_address.ca_address_sk)
-> Hash Join (cost=51930.71..488809.36 rows=90671 width=14) (actual time=3070.350..15047.712 rows=414245 loops=5)
Hash Cond: (web_sales.ws_bill_customer_sk = customer.c_customer_sk)
-> Hash Join (cost=1237.38..436756.24 rows=90671 width=14) (actual time=56.386..11554.924 rows=414302 loops=5)
Hash Cond: (web_sales.ws_sold_date_sk = date_dim.d_date_sk)
-> Parallel Seq Scan on web_sales (cost=0.00..367798.12 rows=17998432 width=18) (actual time=0.099..6058.670 rows=14400247 loops=5)
-> Hash (cost=1236.23..1236.23 rows=92 width=4) (actual time=56.100..56.100 rows=91 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on date_dim (cost=0.00..1236.23 rows=92 width=4) (actual time=29.093..56.032 rows=91 loops=5)
Filter: ((d_qoy = 2) AND (d_year = 1999))
Rows Removed by Filter: 72958
-> Hash (cost=25694.42..25694.42 rows=1999912 width=8) (actual time=3011.842..3011.842 rows=2000000 loops=5)
Buckets: 2097152 Batches: 1 Memory Usage: 94509kB
-> Seq Scan on customer (cost=0.00..25694.42 rows=1999912 width=8) (actual time=0.065..1584.443 rows=2000000 loops=5)
-> Hash (cost=12173.40..12173.40 rows=1000000 width=18) (actual time=1639.416..1639.416 rows=1000000 loops=5)
Buckets: 1048576 Batches: 1 Memory Usage: 58500kB
-> Seq Scan on customer_address (cost=0.00..12173.40 rows=1000000 width=18) (actual time=0.046..855.394 rows=1000000 loops=5)
-> Hash (cost=3432.80..3432.80 rows=204000 width=21) (actual time=320.818..320.818 rows=204000 loops=5)
Buckets: 262144 Batches: 1 Memory Usage: 12607kB
-> Seq Scan on item (cost=0.00..3432.80 rows=204000 width=21) (actual time=0.016..186.555 rows=204000 loops=5)
SubPlan 1
-> Index Scan using item_pkey on item item_1 (cost=0.42..5.48 rows=10 width=17) (actual time=0.026..0.098 rows=10 loops=5)
Index Cond: (i_item_sk = ANY ('{2,3,5,7,11,13,17,19,23,29}'::integer[]))
Planning time: 3.940 ms
Execution time: 19519.561 ms
(39 rows)