query-31.patch.explain
text/plain
│ Sort (cost=302668.66..302668.66 rows=1 width=210) (actual time=3825537.172..3825541.540 rows=43.00 loops=1) │ │ Sort Key: ((ss3.store_sales / ss2.store_sales)) │ │ Sort Method: quicksort Memory: 28kB │ │ Buffers: shared hit=21757 read=69012, temp read=14486 written=25552 │ │ CTE ss │ │ -> Finalize GroupAggregate (cost=178135.51..215272.86 rows=262517 width=54) (actual time=1471.638..1733.635 rows=35117.00 loops=1) │ │ Group Key: customer_address.ca_county, date_dim.d_qoy, date_dim.d_year │ │ Buffers: shared hit=3533 read=50849, temp read=14486 written=25552 │ │ -> Gather Merge (cost=178135.51..208709.94 rows=262517 width=54) (actual time=1471.627..1586.417 rows=234867.00 loops=1) │ │ Workers Planned: 2 │ │ Workers Launched: 2 │ │ Buffers: shared hit=3533 read=50849, temp read=14486 written=25552 │ │ -> Sort (cost=177135.48..177408.94 rows=109382 width=54) (actual time=1463.292..1497.110 rows=78658.67 loops=3) │ │ Sort Key: customer_address.ca_county, date_dim.d_qoy, date_dim.d_year │ │ Sort Method: external merge Disk: 7944kB │ │ Buffers: shared hit=3533 read=50849, temp read=14486 written=25552 │ │ Worker 0: Sort Method: external merge Disk: 8000kB │ │ Worker 1: Sort Method: external merge Disk: 7928kB │ │ -> Parallel Hash Join (cost=147862.49..164239.25 rows=109382 width=54) (actual time=839.965..1235.101 rows=80523.33 loops=3) │ │ Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk) │ │ Buffers: shared hit=3503 read=50849, temp read=11502 written=22562 │ │ -> Parallel Hash Join (cost=145471.66..161547.68 rows=114565 width=50) (actual time=820.740..1192.922 rows=96392.00 loops=3) │ │ Hash Cond: (store_sales.ss_addr_sk = customer_address.ca_address_sk) │ │ Buffers: shared hit=2079 read=50849, temp read=11502 written=22562 │ │ -> Partial HashAggregate (cost=143673.89..158993.80 rows=288022 width=40) (actual time=810.581..1155.245 rows=98213.67 loops=3) │ │ Group Key: store_sales.ss_sold_date_sk, store_sales.ss_addr_sk │ │ Planned Partitions: 16 Batches: 17 Memory Usage: 8337kB Disk Usage: 31640kB │ │ Buffers: shared hit=943 read=50849, temp read=11502 written=22562 │ │ Worker 0: Batches: 17 Memory Usage: 8337kB Disk Usage: 31760kB │ │ Worker 1: Batches: 17 Memory Usage: 8337kB Disk Usage: 31640kB │ │ -> Parallel Seq Scan on store_sales (cost=0.00..63792.90 rows=1200090 width=14) (actual time=0.126..79.442 rows=960134.67 loops=3) │ │ Buffers: shared hit=943 read=50849 │ │ -> Parallel Hash (cost=1430.12..1430.12 rows=29412 width=18) (actual time=10.036..10.038 rows=16666.67 loops=3) │ │ Buckets: 65536 Batches: 1 Memory Usage: 3264kB │ │ Buffers: shared hit=1136 │ │ -> Parallel Seq Scan on customer_address (cost=0.00..1430.12 rows=29412 width=18) (actual time=0.007..5.102 rows=16666.67 loops=3) │ │ Buffers: shared hit=1136 │ │ -> Parallel Hash (cost=1853.70..1853.70 rows=42970 width=12) (actual time=19.092..19.094 rows=24349.67 loops=3) │ │ Buckets: 131072 Batches: 1 Memory Usage: 4512kB │ │ Buffers: shared hit=1424 │ │ -> Parallel Seq Scan on date_dim (cost=0.00..1853.70 rows=42970 width=12) (actual time=0.012..10.264 rows=24349.67 loops=3) │ │ Buffers: shared hit=1424 │ │ CTE ws │ │ -> Finalize GroupAggregate (cost=52144.19..62314.79 rows=71894 width=54) (actual time=275.121..340.107 rows=23312.00 loops=1) │ │ Group Key: customer_address_1.ca_county, date_dim_1.d_qoy, date_dim_1.d_year │ │ Buffers: shared hit=18224 read=18163 │ │ -> Gather Merge (cost=52144.19..60517.44 rows=71894 width=54) (actual time=275.107..297.072 rows=60190.00 loops=1) │ │ Workers Planned: 2 │ │ Workers Launched: 2 │ │ Buffers: shared hit=18224 read=18163 │ │ -> Sort (cost=51144.17..51219.06 rows=29956 width=54) (actual time=271.870..272.906 rows=20293.33 loops=3) │ │ Sort Key: customer_address_1.ca_county, date_dim_1.d_qoy, date_dim_1.d_year │ │ Sort Method: quicksort Memory: 2931kB │ │ Buffers: shared hit=18224 read=18163 │ │ Worker 0: Sort Method: quicksort Memory: 2938kB │ │ Worker 1: Sort Method: quicksort Memory: 2955kB │ │ -> Nested Loop (cost=43571.15..48916.86 rows=29956 width=54) (actual time=184.657..215.740 rows=20419.67 loops=3) │ │ Buffers: shared hit=18194 read=18163 │ │ -> Parallel Hash Join (cost=43570.84..47586.10 rows=29967 width=50) (actual time=184.630..201.358 rows=20451.00 loops=3) │ │ Hash Cond: (web_sales.ws_bill_addr_sk = customer_address_1.ca_address_sk) │ │ Buffers: shared hit=1797 read=18163 │ │ -> Partial HashAggregate (cost=41773.08..45599.48 rows=71938 width=40) (actual time=177.706..188.464 rows=20477.33 loops=3) │ │ Group Key: web_sales.ws_sold_date_sk, web_sales.ws_bill_addr_sk │ │ Planned Partitions: 4 Batches: 1 Memory Usage: 7953kB │ │ Buffers: shared hit=661 read=18163 │ │ Worker 0: Batches: 1 Memory Usage: 7953kB │ │ Worker 1: Batches: 1 Memory Usage: 7953kB │ │ -> Parallel Seq Scan on web_sales (cost=0.00..21821.43 rows=299743 width=14) (actual time=0.106..23.122 rows=239794.67 loops=3) │ │ Buffers: shared hit=661 read=18163 │ │ -> Parallel Hash (cost=1430.12..1430.12 rows=29412 width=18) (actual time=6.846..6.847 rows=16666.67 loops=3) │ │ Buckets: 65536 Batches: 1 Memory Usage: 3264kB │ │ Buffers: shared hit=1136 │ │ -> Parallel Seq Scan on customer_address customer_address_1 (cost=0.00..1430.12 rows=29412 width=18) (actual time=0.008..3.586 rows=16666.67 loops=3) │ │ Buffers: shared hit=1136 │ │ -> Memoize (cost=0.30..0.33 rows=1 width=12) (actual time=0.000..0.000 rows=1.00 loops=61353) │ │ Cache Key: web_sales.ws_sold_date_sk │ │ Cache Mode: logical │ │ Estimates: capacity=1822 distinct keys=1822 lookups=29967 hit percent=93.92% │ │ Hits: 18542 Misses: 1824 Evictions: 0 Overflows: 0 Memory Usage: 200kB │ │ Buffers: shared hit=16397 │ │ Worker 0: Hits: 18589 Misses: 1821 Evictions: 0 Overflows: 0 Memory Usage: 200kB │ │ Worker 1: Hits: 18754 Misses: 1823 Evictions: 0 Overflows: 0 Memory Usage: 200kB │ │ -> Index Scan using date_dim_pkey on date_dim date_dim_1 (cost=0.29..0.32 rows=1 width=12) (actual time=0.002..0.002 rows=1.00 loops=5468) │ │ Index Cond: (d_date_sk = web_sales.ws_sold_date_sk) │ │ Index Searches: 5465 │ │ Buffers: shared hit=16397 │ │ -> Nested Loop (cost=0.00..25081.00 rows=1 width=210) (actual time=43808.287..3825536.966 rows=43.00 loops=1) │ │ Join Filter: (((ss1.ca_county)::text = (ss2.ca_county)::text) AND (CASE WHEN (ws1.web_sales > '0'::numeric) THEN (ws2.web_sales / ws1.web_sales) ELSE NULL::numeric END > CASE WHEN (ss1.store_sales > '0'::numeric) THEN (ss2.store_sales / ss1.store_sales) ELSE NULL::numeric END)) │ │ Rows Removed by Join Filter: 226832 │ │ Buffers: shared hit=7500 read=22936, temp read=4819 written=8505 │ │ -> Merge Join (cost=0.00..8360.31 rows=1 width=224) (actual time=1747.759..1760.887 rows=825.00 loops=1) │ │ Merge Cond: ((ss1.ca_county)::text = (ws1.ca_county)::text) │ │ Buffers: shared hit=7500 read=22936, temp read=4321 written=8505 │ │ -> CTE Scan on ss ss1 (cost=0.00..6562.93 rows=7 width=114) (actual time=1471.648..1477.297 rows=1647.00 loops=1) │ │ Filter: ((d_qoy = 1) AND (d_year = 1999)) │ │ Rows Removed by Filter: 33470 │ │ Storage: Memory Maximum Storage: 2635kB │ │ Buffers: shared hit=1278 read=16903, temp read=4321 written=8505 │ │ -> Materialize (cost=0.00..1797.36 rows=2 width=110) (actual time=275.335..280.952 rows=911.00 loops=1) │ │ Storage: Memory Maximum Storage: 17kB │ │ Buffers: shared hit=6222 read=6033 │ │ -> CTE Scan on ws ws1 (cost=0.00..1797.35 rows=2 width=110) (actual time=275.333..279.774 rows=911.00 loops=1) │ │ Filter: ((d_qoy = 1) AND (d_year = 1999)) │ │ Rows Removed by Filter: 22390 │ │ Storage: Memory Maximum Storage: 1700kB │ │ Buffers: shared hit=6222 read=6033 │ │ -> Nested Loop (cost=0.00..16720.65 rows=1 width=440) (actual time=5.913..4634.838 rows=275.00 loops=825) │ │ Join Filter: (((ss2.ca_county)::text = (ss3.ca_county)::text) AND (CASE WHEN (ws2.web_sales > '0'::numeric) THEN (ws3.web_sales / ws2.web_sales) ELSE NULL::numeric END > CASE WHEN (ss2.store_sales > '0'::numeric) THEN (ss3.store_sales / ss2.store_sales) ELSE NULL::numeric END)) │ │ Rows Removed by Join Filter: 1037001 │ │ Buffers: temp read=498 │ │ -> Merge Join (cost=0.00..8360.31 rows=1 width=220) (actual time=0.001..5.266 rows=844.00 loops=825) │ │ Merge Cond: ((ss2.ca_county)::text = (ws2.ca_county)::text) │ │ -> CTE Scan on ss ss2 (cost=0.00..6562.93 rows=7 width=110) (actual time=0.001..4.131 rows=1634.00 loops=825) │ │ Filter: ((d_year = 1999) AND (d_qoy = 2)) │ │ Rows Removed by Filter: 33468 │ │ Storage: Memory Maximum Storage: 2635kB │ │ -> Materialize (cost=0.00..1797.36 rows=2 width=110) (actual time=0.000..0.053 rows=925.00 loops=825) │ │ Storage: Memory Maximum Storage: 74kB │ │ -> CTE Scan on ws ws2 (cost=0.00..1797.35 rows=2 width=110) (actual time=0.001..2.784 rows=925.00 loops=1) │ │ Filter: ((d_year = 1999) AND (d_qoy = 2)) │ │ Rows Removed by Filter: 22382 │ │ Storage: Memory Maximum Storage: 1700kB │ │ -> Merge Join (cost=0.00..8360.31 rows=1 width=220) (actual time=0.002..5.383 rows=1229.00 loops=696300) │ │ Merge Cond: ((ss3.ca_county)::text = (ws3.ca_county)::text) │ │ Buffers: temp read=498 │ │ -> CTE Scan on ss ss3 (cost=0.00..6562.93 rows=7 width=110) (actual time=0.001..4.051 rows=1796.00 loops=696300) │ │ Filter: ((d_year = 1999) AND (d_qoy = 3)) │ │ Rows Removed by Filter: 33292 │ │ Storage: Memory Maximum Storage: 2635kB │ │ Buffers: temp read=498 │ │ -> Materialize (cost=0.00..1797.36 rows=2 width=110) (actual time=0.000..0.047 rows=1261.00 loops=696300) │ │ Storage: Memory Maximum Storage: 95kB │ │ -> CTE Scan on ws ws3 (cost=0.00..1797.35 rows=2 width=110) (actual time=0.001..74.725 rows=1261.00 loops=1) │ │ Filter: ((d_year = 1999) AND (d_qoy = 3)) │ │ Rows Removed by Filter: 22051 │ │ Storage: Memory Maximum Storage: 1700kB │ │ Planning: │ │ Buffers: shared hit=12 │ │ Planning Time: 4.951 ms │ │ Execution Time: 3825542.556 ms │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘