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)