# 1. Setting: default (enable_hashagg = ON, enable_groupagg = ON) # Mix strategy was used # Execution time was 41 sec ==== QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=159135.83..7790760.21 rows=100 width=66) (actual time=12449.124..41253.731 rows=100.00 loops=1) Buffers: shared hit=116473 read=30167, temp read=1482 written=11035 -> GroupAggregate (cost=159135.83..1689495507.58 rows=22136 width=66) (actual time=12449.121..41253.648 rows=100.00 loops=1) Group Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count Buffers: shared hit=116473 read=30167, temp read=1482 written=11035 -> Nested Loop (cost=159135.83..1689494898.84 rows=22136 width=34) (actual time=12317.664..41252.393 rows=101.00 loops=1) Join Filter: (cd.cd_demo_sk = c.c_current_cdemo_sk) Rows Removed by Join Filter: 197188565 Buffers: shared hit=116473 read=30167, temp read=1482 written=11035 -> Gather Merge (cost=125190.47..348899.28 rows=1920800 width=38) (actual time=10122.049..10269.869 rows=34276.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=4889 read=11029, temp read=1482 written=11035 -> Sort (cost=124190.45..126191.28 rows=800333 width=38) (actual time=9985.641..10044.083 rows=11982.00 loops=3) Sort Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count Sort Method: external merge Disk: 28488kB Buffers: shared hit=4889 read=11029, temp read=1482 written=11035 Worker 0: Sort Method: external merge Disk: 29392kB Worker 1: Sort Method: external merge Disk: 30112kB -> Parallel Seq Scan on customer_demographics cd (cost=0.00..23831.33 rows=800333 width=38) (actual time=0.377..852.678 rows=640266.67 loops=3) Buffers: shared hit=4799 read=11029 -> Materialize (cost=33945.36..1051363622.90 rows=22136 width=4) (actual time=0.052..0.337 rows=5752.97 loops=34276) Storage: Memory Maximum Storage: 289kB Buffers: shared hit=111584 read=19138 -> Nested Loop (cost=33945.36..1051363512.22 rows=22136 width=4) (actual time=1785.640..2183.175 rows=5753.00 loops=1) Buffers: shared hit=111584 read=19138 -> Nested Loop (cost=33945.07..1051355209.75 rows=24605 width=8) (actual time=1785.567..2119.781 rows=6409.00 loops=1) Buffers: shared hit=92357 read=19138 -> HashAggregate (cost=33944.78..34272.85 rows=32807 width=4) (actual time=819.215..842.604 rows=28544.00 loops=1) Group Key: ss.ss_customer_sk Batches: 1 Memory Usage: 2329kB Buffers: shared hit=2187 read=11744 -> Gather (cost=2683.76..33862.76 rows=32807 width=4) (actual time=23.253..768.211 rows=33609.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=2187 read=11744 -> Hash Join (cost=1683.76..29582.06 rows=13670 width=4) (actual time=22.719..780.073 rows=11203.00 loops=3) Hash Cond: (ss.ss_sold_date_sk = d.d_date_sk) Buffers: shared hit=2187 read=11744 -> Parallel Seq Scan on store_sales ss (cost=0.00..24747.68 rows=1200168 width=8) (actual time=5.144..424.772 rows=960134.67 loops=3) Buffers: shared hit=1002 read=11744 -> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=17.501..17.502 rows=848.00 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 38kB Buffers: shared hit=1185 -> Seq Scan on date_dim d (cost=0.00..1673.36 rows=832 width=4) (actual time=0.024..17.253 rows=848.00 loops=3) Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002)) Rows Removed by Filter: 72201 Buffers: shared hit=1185 -> Index Scan using customer_pkey on customer c (cost=0.29..36766.33 rows=1 width=12) (actual time=0.044..0.044 rows=0.22 loops=28544) Index Cond: (c_customer_sk = ss.ss_customer_sk) Filter: ((ANY (c_customer_sk = (hashed SubPlan 2).col1)) OR (ANY (c_customer_sk = (hashed SubPlan 4).col1))) Rows Removed by Filter: 1 Index Searches: 28544 Buffers: shared hit=90170 read=7394 SubPlan 2 -> Gather (cost=2683.76..10471.46 rows=8194 width=4) (actual time=18.769..103.477 rows=8156.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=2496 read=1873 -> Hash Join (cost=1683.76..8652.06 rows=3414 width=4) (actual time=27.114..97.488 rows=2718.67 loops=3) Hash Cond: (ws.ws_sold_date_sk = d_1.d_date_sk) Buffers: shared hit=2496 read=1873 -> Parallel Seq Scan on web_sales ws (cost=0.00..6181.43 rows=299743 width=8) (actual time=0.005..26.446 rows=239794.67 loops=3) Buffers: shared hit=1311 read=1873 -> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=27.079..27.080 rows=848.00 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 38kB Buffers: shared hit=1185 -> Seq Scan on date_dim d_1 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.033..26.791 rows=848.00 loops=3) Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002)) Rows Removed by Filter: 72201 Buffers: shared hit=1185 SubPlan 4 -> Gather (cost=2683.76..18287.89 rows=16419 width=4) (actual time=11.472..831.348 rows=16874.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=2043 read=5521 -> Hash Join (cost=1683.76..15645.99 rows=6841 width=4) (actual time=18.591..824.234 rows=5624.67 loops=3) Hash Cond: (cs.cs_sold_date_sk = d_2.d_date_sk) Buffers: shared hit=2043 read=5521 -> Parallel Seq Scan on catalog_sales cs (cost=0.00..12385.45 rows=600645 width=8) (actual time=0.334..455.274 rows=480516.00 loops=3) Buffers: shared hit=858 read=5521 -> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=18.205..18.206 rows=848.00 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 38kB Buffers: shared hit=1185 -> Seq Scan on date_dim d_2 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.048..17.917 rows=848.00 loops=3) Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002)) Rows Removed by Filter: 72201 Buffers: shared hit=1185 -> Index Scan using customer_address_pkey on customer_address ca (cost=0.29..0.34 rows=1 width=4) (actual time=0.009..0.009 rows=0.90 loops=6409) Index Cond: (ca_address_sk = c.c_current_addr_sk) Filter: ((ca_county)::text = ANY ('{"Rush County","Toole County","Jefferson County","Dona Ana County","La Porte County"}'::text[])) Rows Removed by Filter: 0 Index Searches: 6409 Buffers: shared hit=19227 Planning: Buffers: shared hit=316 Planning Time: 5.683 ms Execution Time: 41263.299 ms (98 rows) ==== # 2. Setting: enable_hashagg = OFF, enable_groupagg = ON # GroupAgg was used # Execution time was 44 sec ==== QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=161514.62..7793138.25 rows=100 width=66) (actual time=10197.188..44152.766 rows=100.00 loops=1) Buffers: shared hit=118758 read=27882, temp read=1482 written=11040 -> GroupAggregate (cost=161514.62..1689497722.34 rows=22136 width=66) (actual time=10197.187..44152.676 rows=100.00 loops=1) Group Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count Buffers: shared hit=118758 read=27882, temp read=1482 written=11040 -> Nested Loop (cost=161514.62..1689497113.60 rows=22136 width=34) (actual time=10129.162..44151.929 rows=101.00 loops=1) Join Filter: (cd.cd_demo_sk = c.c_current_cdemo_sk) Rows Removed by Join Filter: 197184348 Buffers: shared hit=118758 read=27882, temp read=1482 written=11040 -> Gather Merge (cost=125190.47..348899.28 rows=1920800 width=38) (actual time=8741.033..8900.320 rows=34276.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=5469 read=10449, temp read=1482 written=11040 -> Sort (cost=124190.45..126191.28 rows=800333 width=38) (actual time=8499.285..8571.085 rows=11989.67 loops=3) Sort Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count Sort Method: external merge Disk: 30368kB Buffers: shared hit=5469 read=10449, temp read=1482 written=11040 Worker 0: Sort Method: external merge Disk: 29104kB Worker 1: Sort Method: external merge Disk: 28560kB -> Parallel Seq Scan on customer_demographics cd (cost=0.00..23831.33 rows=800333 width=38) (actual time=2.220..766.199 rows=640266.67 loops=3) Buffers: shared hit=5379 read=10449 -> Materialize (cost=36324.15..1051365837.65 rows=22136 width=4) (actual time=0.032..0.363 rows=5752.84 loops=34276) Storage: Memory Maximum Storage: 289kB Buffers: shared hit=113289 read=17433 -> Nested Loop (cost=36324.15..1051365726.97 rows=22136 width=4) (actual time=1084.423..1391.426 rows=5753.00 loops=1) Buffers: shared hit=113289 read=17433 -> Nested Loop (cost=36323.86..1051357424.51 rows=24605 width=8) (actual time=1084.375..1346.642 rows=6409.00 loops=1) Buffers: shared hit=94062 read=17433 -> Unique (cost=36323.57..36487.60 rows=32807 width=4) (actual time=545.023..574.883 rows=28544.00 loops=1) Buffers: shared hit=2031 read=11900 -> Sort (cost=36323.57..36405.58 rows=32807 width=4) (actual time=545.019..556.733 rows=33609.00 loops=1) Sort Key: ss.ss_customer_sk Sort Method: quicksort Memory: 1537kB Buffers: shared hit=2031 read=11900 -> Gather (cost=2683.76..33862.76 rows=32807 width=4) (actual time=33.440..529.429 rows=33609.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=2031 read=11900 -> Hash Join (cost=1683.76..29582.06 rows=13670 width=4) (actual time=37.334..511.304 rows=11203.00 loops=3) Hash Cond: (ss.ss_sold_date_sk = d.d_date_sk) Buffers: shared hit=2031 read=11900 -> Parallel Seq Scan on store_sales ss (cost=0.00..24747.68 rows=1200168 width=8) (actual time=4.599..258.454 rows=960134.67 loops=3) Buffers: shared hit=846 read=11900 -> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=32.247..32.248 rows=848.00 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 38kB Buffers: shared hit=1185 -> Seq Scan on date_dim d (cost=0.00..1673.36 rows=832 width=4) (actual time=0.024..31.303 rows=848.00 loops=3) Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002)) Rows Removed by Filter: 72201 Buffers: shared hit=1185 -> Index Scan using customer_pkey on customer c (cost=0.29..36766.33 rows=1 width=12) (actual time=0.026..0.026 rows=0.22 loops=28544) Index Cond: (c_customer_sk = ss.ss_customer_sk) Filter: ((ANY (c_customer_sk = (hashed SubPlan 2).col1)) OR (ANY (c_customer_sk = (hashed SubPlan 4).col1))) Rows Removed by Filter: 1 Index Searches: 28544 Buffers: shared hit=92031 read=5533 SubPlan 2 -> Gather (cost=2683.76..10471.46 rows=8194 width=4) (actual time=28.354..100.442 rows=8156.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=4369 -> Hash Join (cost=1683.76..8652.06 rows=3414 width=4) (actual time=15.023..74.578 rows=2718.67 loops=3) Hash Cond: (ws.ws_sold_date_sk = d_1.d_date_sk) Buffers: shared hit=4369 -> Parallel Seq Scan on web_sales ws (cost=0.00..6181.43 rows=299743 width=8) (actual time=0.007..19.459 rows=239794.67 loops=3) Buffers: shared hit=3184 -> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=14.987..14.987 rows=848.00 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 38kB Buffers: shared hit=1185 -> Seq Scan on date_dim d_1 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.016..14.672 rows=848.00 loops=3) Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002)) Rows Removed by Filter: 72201 Buffers: shared hit=1185 SubPlan 4 -> Gather (cost=2683.76..18287.89 rows=16419 width=4) (actual time=69.799..443.128 rows=16874.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=2031 read=5533 -> Hash Join (cost=1683.76..15645.99 rows=6841 width=4) (actual time=66.236..401.988 rows=5624.67 loops=3) Hash Cond: (cs.cs_sold_date_sk = d_2.d_date_sk) Buffers: shared hit=2031 read=5533 -> Parallel Seq Scan on catalog_sales cs (cost=0.00..12385.45 rows=600645 width=8) (actual time=10.580..208.806 rows=480516.00 loops=3) Buffers: shared hit=846 read=5533 -> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=55.629..55.630 rows=848.00 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 38kB Buffers: shared hit=1185 -> Seq Scan on date_dim d_2 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.021..53.491 rows=848.00 loops=3) Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002)) Rows Removed by Filter: 72201 Buffers: shared hit=1185 -> Index Scan using customer_address_pkey on customer_address ca (cost=0.29..0.34 rows=1 width=4) (actual time=0.006..0.006 rows=0.90 loops=6409) Index Cond: (ca_address_sk = c.c_current_addr_sk) Filter: ((ca_county)::text = ANY ('{"Rush County","Toole County","Jefferson County","Dona Ana County","La Porte County"}'::text[])) Rows Removed by Filter: 0 Index Searches: 6409 Buffers: shared hit=19227 Settings: enable_hashagg = 'off' Planning: Buffers: shared hit=316 Planning Time: 2.101 ms Execution Time: 44168.266 ms (101 rows) ==== # 3. Setting: enable_hashagg = ON, enable_groupagg = OFF # HashAgg was used # Execution time was 1 sec ==== QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1051399723.16..1051399723.41 rows=100 width=66) (actual time=1096.948..1097.642 rows=100.00 loops=1) Buffers: shared hit=133811 read=19929 -> Sort (cost=1051399723.16..1051399778.50 rows=22136 width=66) (actual time=1096.946..1097.633 rows=100.00 loops=1) Sort Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count Sort Method: top-N heapsort Memory: 49kB Buffers: shared hit=133811 read=19929 -> HashAggregate (cost=1051398655.78..1051398877.14 rows=22136 width=66) (actual time=1087.378..1090.664 rows=5740.00 loops=1) Group Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count Batches: 1 Memory Usage: 1049kB Buffers: shared hit=133805 read=19929 -> Nested Loop (cost=33945.79..1051398268.40 rows=22136 width=34) (actual time=702.467..1076.172 rows=5753.00 loops=1) Buffers: shared hit=133805 read=19929 -> Nested Loop (cost=33945.36..1051363512.22 rows=22136 width=4) (actual time=702.451..958.299 rows=5753.00 loops=1) Buffers: shared hit=114045 read=16677 -> Nested Loop (cost=33945.07..1051355209.75 rows=24605 width=8) (actual time=702.387..921.978 rows=6409.00 loops=1) Buffers: shared hit=94818 read=16677 -> HashAggregate (cost=33944.78..34272.85 rows=32807 width=4) (actual time=354.993..373.328 rows=28544.00 loops=1) Group Key: ss.ss_customer_sk Batches: 1 Memory Usage: 2329kB Buffers: shared hit=2223 read=11708 -> Gather (cost=2683.76..33862.76 rows=32807 width=4) (actual time=17.988..336.471 rows=33609.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=2223 read=11708 -> Hash Join (cost=1683.76..29582.06 rows=13670 width=4) (actual time=16.964..337.747 rows=11203.00 loops=3) Hash Cond: (ss.ss_sold_date_sk = d.d_date_sk) Buffers: shared hit=2223 read=11708 -> Parallel Seq Scan on store_sales ss (cost=0.00..24747.68 rows=1200168 width=8) (actual time=0.394..157.640 rows=960134.67 loops=3) Buffers: shared hit=1038 read=11708 -> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=16.492..16.493 rows=848.00 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 38kB Buffers: shared hit=1185 -> Seq Scan on date_dim d (cost=0.00..1673.36 rows=832 width=4) (actual time=0.021..15.934 rows=848.00 loops=3) Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002)) Rows Removed by Filter: 72201 Buffers: shared hit=1185 -> Index Scan using customer_pkey on customer c (cost=0.29..36766.33 rows=1 width=12) (actual time=0.018..0.018 rows=0.22 loops=28544) Index Cond: (c_customer_sk = ss.ss_customer_sk) Filter: ((ANY (c_customer_sk = (hashed SubPlan 2).col1)) OR (ANY (c_customer_sk = (hashed SubPlan 4).col1))) Rows Removed by Filter: 1 Index Searches: 28544 Buffers: shared hit=92595 read=4969 SubPlan 2 -> Gather (cost=2683.76..10471.46 rows=8194 width=4) (actual time=72.010..153.327 rows=8156.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=4369 -> Hash Join (cost=1683.76..8652.06 rows=3414 width=4) (actual time=44.282..111.007 rows=2718.67 loops=3) Hash Cond: (ws.ws_sold_date_sk = d_1.d_date_sk) Buffers: shared hit=4369 -> Parallel Seq Scan on web_sales ws (cost=0.00..6181.43 rows=299743 width=8) (actual time=0.006..22.145 rows=239794.67 loops=3) Buffers: shared hit=3184 -> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=44.238..44.239 rows=848.00 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 38kB Buffers: shared hit=1185 -> Seq Scan on date_dim d_1 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.015..42.452 rows=848.00 loops=3) Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002)) Rows Removed by Filter: 72201 Buffers: shared hit=1185 SubPlan 4 -> Gather (cost=2683.76..18287.89 rows=16419 width=4) (actual time=34.268..183.058 rows=16874.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=2595 read=4969 -> Hash Join (cost=1683.76..15645.99 rows=6841 width=4) (actual time=30.132..174.422 rows=5624.67 loops=3) Hash Cond: (cs.cs_sold_date_sk = d_2.d_date_sk) Buffers: shared hit=2595 read=4969 -> Parallel Seq Scan on catalog_sales cs (cost=0.00..12385.45 rows=600645 width=8) (actual time=3.432..69.160 rows=480516.00 loops=3) Buffers: shared hit=1410 read=4969 -> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=26.671..26.672 rows=848.00 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 38kB Buffers: shared hit=1185 -> Seq Scan on date_dim d_2 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.019..26.377 rows=848.00 loops=3) Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002)) Rows Removed by Filter: 72201 Buffers: shared hit=1185 -> Index Scan using customer_address_pkey on customer_address ca (cost=0.29..0.34 rows=1 width=4) (actual time=0.005..0.005 rows=0.90 loops=6409) Index Cond: (ca_address_sk = c.c_current_addr_sk) Filter: ((ca_county)::text = ANY ('{"Rush County","Toole County","Jefferson County","Dona Ana County","La Porte County"}'::text[])) Rows Removed by Filter: 0 Index Searches: 6409 Buffers: shared hit=19227 -> Index Scan using customer_demographics_pkey on customer_demographics cd (cost=0.43..1.57 rows=1 width=38) (actual time=0.019..0.019 rows=1.00 loops=5753) Index Cond: (cd_demo_sk = c.c_current_cdemo_sk) Index Searches: 5753 Buffers: shared hit=19760 read=3252 Settings: enable_groupagg = 'off' Planning: Buffers: shared hit=316 Planning Time: 21.057 ms Execution Time: 1105.672 ms (91 rows) ==== # 4. Setting: enable_hashagg = OFF, enable_groupagg = OFF # GroupAgg was used # Execution time was 38 sec ==== QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=161514.62..7793138.25 rows=100 width=66) (actual time=7611.145..38354.564 rows=100.00 loops=1) Buffers: shared hit=119783 read=26857, temp read=1482 written=11039 -> GroupAggregate (cost=161514.62..1689497722.34 rows=22136 width=66) (actual time=7611.128..38354.435 rows=100.00 loops=1) Group Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count Buffers: shared hit=119783 read=26857, temp read=1482 written=11039 -> Nested Loop (cost=161514.62..1689497113.60 rows=22136 width=34) (actual time=7510.502..38353.114 rows=101.00 loops=1) Join Filter: (cd.cd_demo_sk = c.c_current_cdemo_sk) Rows Removed by Join Filter: 197184348 Buffers: shared hit=119783 read=26857, temp read=1482 written=11039 -> Gather Merge (cost=125190.47..348899.28 rows=1920800 width=38) (actual time=6363.769..6499.733 rows=34276.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=5833 read=10085, temp read=1482 written=11039 -> Sort (cost=124190.45..126191.28 rows=800333 width=38) (actual time=6163.457..6232.614 rows=11966.67 loops=3) Sort Key: cd.cd_gender, cd.cd_marital_status, cd.cd_education_status, cd.cd_income_band, cd.cd_credit_rating, cd.cd_dep_count Sort Method: external merge Disk: 28992kB Buffers: shared hit=5833 read=10085, temp read=1482 written=11039 Worker 0: Sort Method: external merge Disk: 28752kB Worker 1: Sort Method: external merge Disk: 30280kB -> Parallel Seq Scan on customer_demographics cd (cost=0.00..23831.33 rows=800333 width=38) (actual time=0.737..321.713 rows=640266.67 loops=3) Buffers: shared hit=5743 read=10085 -> Materialize (cost=36324.15..1051365837.65 rows=22136 width=4) (actual time=0.025..0.325 rows=5752.84 loops=34276) Storage: Memory Maximum Storage: 289kB Buffers: shared hit=113950 read=16772 -> Nested Loop (cost=36324.15..1051365726.97 rows=22136 width=4) (actual time=865.332..1133.116 rows=5753.00 loops=1) Buffers: shared hit=113950 read=16772 -> Nested Loop (cost=36323.86..1051357424.51 rows=24605 width=8) (actual time=863.365..1073.375 rows=6409.00 loops=1) Buffers: shared hit=94723 read=16772 -> Unique (cost=36323.57..36487.60 rows=32807 width=4) (actual time=393.964..415.541 rows=28544.00 loops=1) Buffers: shared hit=2128 read=11803 -> Sort (cost=36323.57..36405.58 rows=32807 width=4) (actual time=393.957..402.146 rows=33609.00 loops=1) Sort Key: ss.ss_customer_sk Sort Method: quicksort Memory: 1537kB Buffers: shared hit=2128 read=11803 -> Gather (cost=2683.76..33862.76 rows=32807 width=4) (actual time=80.713..349.762 rows=33609.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=2128 read=11803 -> Hash Join (cost=1683.76..29582.06 rows=13670 width=4) (actual time=44.679..308.141 rows=11203.00 loops=3) Hash Cond: (ss.ss_sold_date_sk = d.d_date_sk) Buffers: shared hit=2128 read=11803 -> Parallel Seq Scan on store_sales ss (cost=0.00..24747.68 rows=1200168 width=8) (actual time=9.033..125.081 rows=960134.67 loops=3) Buffers: shared hit=943 read=11803 -> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=35.605..35.606 rows=848.00 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 38kB Buffers: shared hit=1185 -> Seq Scan on date_dim d (cost=0.00..1673.36 rows=832 width=4) (actual time=0.036..35.172 rows=848.00 loops=3) Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002)) Rows Removed by Filter: 72201 Buffers: shared hit=1185 -> Index Scan using customer_pkey on customer c (cost=0.29..36766.33 rows=1 width=12) (actual time=0.022..0.022 rows=0.22 loops=28544) Index Cond: (c_customer_sk = ss.ss_customer_sk) Filter: ((ANY (c_customer_sk = (hashed SubPlan 2).col1)) OR (ANY (c_customer_sk = (hashed SubPlan 4).col1))) Rows Removed by Filter: 1 Index Searches: 28544 Buffers: shared hit=92595 read=4969 SubPlan 2 -> Gather (cost=2683.76..10471.46 rows=8194 width=4) (actual time=39.476..124.540 rows=8156.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=4369 -> Hash Join (cost=1683.76..8652.06 rows=3414 width=4) (actual time=20.456..94.877 rows=2718.67 loops=3) Hash Cond: (ws.ws_sold_date_sk = d_1.d_date_sk) Buffers: shared hit=4369 -> Parallel Seq Scan on web_sales ws (cost=0.00..6181.43 rows=299743 width=8) (actual time=0.020..28.916 rows=239794.67 loops=3) Buffers: shared hit=3184 -> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=20.396..20.397 rows=848.00 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 38kB Buffers: shared hit=1185 -> Seq Scan on date_dim d_1 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.019..20.094 rows=848.00 loops=3) Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002)) Rows Removed by Filter: 72201 Buffers: shared hit=1185 SubPlan 4 -> Gather (cost=2683.76..18287.89 rows=16419 width=4) (actual time=56.442..326.649 rows=16874.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=2595 read=4969 -> Hash Join (cost=1683.76..15645.99 rows=6841 width=4) (actual time=64.537..301.087 rows=5624.67 loops=3) Hash Cond: (cs.cs_sold_date_sk = d_2.d_date_sk) Buffers: shared hit=2595 read=4969 -> Parallel Seq Scan on catalog_sales cs (cost=0.00..12385.45 rows=600645 width=8) (actual time=11.234..130.365 rows=480516.00 loops=3) Buffers: shared hit=1410 read=4969 -> Hash (cost=1673.36..1673.36 rows=832 width=4) (actual time=53.242..53.243 rows=848.00 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 38kB Buffers: shared hit=1185 -> Seq Scan on date_dim d_2 (cost=0.00..1673.36 rows=832 width=4) (actual time=0.025..52.328 rows=848.00 loops=3) Filter: ((d_moy >= 1) AND (d_moy <= 4) AND (d_year = 2002)) Rows Removed by Filter: 72201 Buffers: shared hit=1185 -> Index Scan using customer_address_pkey on customer_address ca (cost=0.29..0.34 rows=1 width=4) (actual time=0.009..0.009 rows=0.90 loops=6409) Index Cond: (ca_address_sk = c.c_current_addr_sk) Filter: ((ca_county)::text = ANY ('{"Rush County","Toole County","Jefferson County","Dona Ana County","La Porte County"}'::text[])) Rows Removed by Filter: 0 Index Searches: 6409 Buffers: shared hit=19227 Settings: enable_hashagg = 'off', enable_groupagg = 'off' Planning: Buffers: shared hit=316 Planning Time: 10.736 ms Execution Time: 38358.196 ms (101 rows) ====