query-31.master.explain
text/plain
│ Sort (cost=656889.77..656889.77 rows=1 width=210) (actual time=17164.506..17164.519 rows=43.00 loops=1) │ │ Sort Key: ((ss3.store_sales / ss2.store_sales)) │ │ Sort Method: quicksort Memory: 28kB │ │ Buffers: shared hit=6533 read=69203, temp read=4343 written=12055 │ │ CTE ss │ │ -> HashAggregate (cost=323021.86..377372.99 rows=1476800 width=54) (actual time=3389.564..3677.220 rows=35136.00 loops=1) │ │ Group Key: customer_address.ca_county, date_dim.d_qoy, date_dim.d_year │ │ Planned Partitions: 64 Batches: 65 Memory Usage: 8209kB Disk Usage: 56840kB │ │ Buffers: shared hit=3408 read=50944, temp read=3962 written=10947 │ │ -> Hash Join (cost=5328.60..100701.93 rows=2625180 width=28) (actual time=46.394..2034.907 rows=2685273.00 loops=1) │ │ Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk) │ │ Buffers: shared hit=3408 read=50944 │ │ -> Hash Join (cost=2261.00..90416.35 rows=2749551 width=24) (actual time=18.753..1396.048 rows=2750429.00 loops=1) │ │ Hash Cond: (store_sales.ss_addr_sk = customer_address.ca_address_sk) │ │ Buffers: shared hit=1984 read=50944 │ │ -> Seq Scan on store_sales (cost=0.00..80594.17 rows=2880217 width=14) (actual time=0.063..228.063 rows=2880404.00 loops=1) │ │ Buffers: shared hit=848 read=50944 │ │ -> Hash (cost=1636.00..1636.00 rows=50000 width=18) (actual time=18.651..18.651 rows=50000.00 loops=1) │ │ Buckets: 65536 Batches: 1 Memory Usage: 3052kB │ │ Buffers: shared hit=1136 │ │ -> Seq Scan on customer_address (cost=0.00..1636.00 rows=50000 width=18) (actual time=0.005..9.555 rows=50000.00 loops=1) │ │ Buffers: shared hit=1136 │ -> Hash (cost=2154.49..2154.49 rows=73049 width=12) (actual time=27.627..27.629 rows=73049.00 loops=1) │ │ Buckets: 131072 Batches: 1 Memory Usage: 4163kB │ │ Buffers: shared hit=1424 │ │ -> Seq Scan on date_dim (cost=0.00..2154.49 rows=73049 width=12) (actual time=0.009..15.154 rows=73049.00 loops=1) │ │ Buffers: shared hit=1424 │ │ CTE ws │ │ -> HashAggregate (cost=96009.03..114825.35 rows=718952 width=54) (actual time=977.215..1014.889 rows=23320.00 loops=1) │ │ Group Key: customer_address_1.ca_county, date_dim_1.d_qoy, date_dim_1.d_year │ │ Planned Partitions: 32 Batches: 33 Memory Usage: 8209kB Disk Usage: 6032kB │ │ Buffers: shared hit=3125 read=18259, temp read=381 written=1108 │ │ -> Hash Join (cost=5328.60..35122.78 rows=718952 width=28) (actual time=46.623..611.054 rows=719118.00 loops=1) │ │ Hash Cond: (web_sales.ws_bill_addr_sk = customer_address_1.ca_address_sk) │ Buffers: shared hit=3125 read=18259 │ │ -> Hash Join (cost=3067.60..30973.94 rows=719120 width=18) (actual time=27.691..424.273 rows=719195.00 loops=1) │ │ Hash Cond: (web_sales.ws_sold_date_sk = date_dim_1.d_date_sk) │ │ Buffers: shared hit=1989 read=18259 │ │ -> Seq Scan on web_sales (cost=0.00..26017.84 rows=719384 width=14) (actual time=0.082..63.389 rows=719384.00 loops=1) │ │ Buffers: shared hit=565 read=18259 │ │ -> Hash (cost=2154.49..2154.49 rows=73049 width=12) (actual time=27.538..27.538 rows=73049.00 loops=1) │ │ Buckets: 131072 Batches: 1 Memory Usage: 4163kB │ │ Buffers: shared hit=1424 │ │ -> Seq Scan on date_dim date_dim_1 (cost=0.00..2154.49 rows=73049 width=12) (actual time=0.006..14.914 rows=73049.00 loops=1) │ │ Buffers: shared hit=1424 │ │ -> Hash (cost=1636.00..1636.00 rows=50000 width=18) (actual time=18.902..18.902 rows=50000.00 loops=1) │ │ Buckets: 65536 Batches: 1 Memory Usage: 3052kB │ │ Buffers: shared hit=1136 │ │ -> Seq Scan on customer_address customer_address_1 (cost=0.00..1636.00 rows=50000 width=18) (actual time=0.008..9.727 rows=50000.00 loops=1) │ │ Buffers: shared hit=1136 │ │ -> Nested Loop (cost=0.00..164691.41 rows=1 width=210) (actual time=4817.695..17164.430 rows=43.00 loops=1) │ │ Join Filter: (((ss1.ca_county)::text = (ws2.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) 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: 527207 │ │ Buffers: shared hit=6533 read=69203, temp read=4343 written=12055 │ │ -> Nested Loop (cost=0.00..146716.93 rows=1 width=554) (actual time=4671.968..15501.760 rows=570.00 loops=1) │ │ Join Filter: ((ss1.ca_county)::text = (ss3.ca_county)::text) │ │ Rows Removed by Join Filter: 1038674 │ │ Buffers: shared hit=6533 read=69203, temp read=4343 written=12055 │ │ -> Nested Loop (cost=0.00..109796.47 rows=1 width=444) (actual time=4669.164..12922.095 rows=578.00 loops=1) │ Join Filter: ((ss1.ca_county)::text = (ss2.ca_county)::text) │ │ Rows Removed by Join Filter: 1008217 │ │ Buffers: shared hit=6533 read=69203, temp read=3559 written=12055 │ │ -> Nested Loop (cost=0.00..72876.00 rows=1 width=334) (actual time=4666.835..10231.481 rows=617.00 loops=1) │ │ Join Filter: ((ss1.ca_county)::text = (ws1.ca_county)::text) │ │ Rows Removed by Join Filter: 1089697 │ │ Buffers: shared hit=6533 read=69203, temp read=3559 written=12055 │ │ -> Nested Loop (cost=0.00..35954.71 rows=2 width=220) (actual time=1031.594..3687.112 rows=662.00 loops=1) │ │ Join Filter: ((ws1.ca_county)::text = (ws3.ca_county)::text) │ │ Rows Removed by Join Filter: 1148109 │ │ Buffers: shared hit=3125 read=18259, temp read=381 written=1108 │ │ -> CTE Scan on ws ws1 (cost=0.00..17973.80 rows=18 width=110) (actual time=977.224..980.082 rows=911.00 loops=1) │ Filter: ((d_qoy = 1) AND (d_year = 1999)) │ │ Rows Removed by Filter: 22409 │ │ Storage: Memory Maximum Storage: 1700kB │ │ Buffers: shared hit=3125 read=18259, temp written=1107 │ │ -> CTE Scan on ws ws3 (cost=0.00..17973.80 rows=18 width=110) (actual time=0.005..2.857 rows=1261.00 loops=911) │ │ Filter: ((d_year = 1999) AND (d_qoy = 3)) │ │ Rows Removed by Filter: 22059 │ │ Storage: Memory Maximum Storage: 1700kB │ │ Buffers: temp read=381 written=1 │ │ -> CTE Scan on ss ss1 (cost=0.00..36920.00 rows=37 width=114) (actual time=5.121..9.740 rows=1647.00 loops=662) │ │ Filter: ((d_qoy = 1) AND (d_year = 1999)) │ │ Rows Removed by Filter: 33489 │ │ Storage: Memory Maximum Storage: 2636kB │ │ Buffers: shared hit=3408 read=50944, temp read=3178 written=10947 │ │ -> CTE Scan on ss ss2 (cost=0.00..36920.00 rows=37 width=110) (actual time=0.001..4.216 rows=1635.00 loops=617) │ │ Filter: ((d_year = 1999) AND (d_qoy = 2)) │ │ Rows Removed by Filter: 33501 │ │ Storage: Memory Maximum Storage: 2636kB │ │ -> CTE Scan on ss ss3 (cost=0.00..36920.00 rows=37 width=110) (actual time=0.006..4.305 rows=1798.00 loops=578) │ │ Filter: ((d_year = 1999) AND (d_qoy = 3)) │ │ Rows Removed by Filter: 33338 │ │ Storage: Memory Maximum Storage: 2636kB │ │ Buffers: temp read=784 │ │ -> CTE Scan on ws ws2 (cost=0.00..17973.80 rows=18 width=110) (actual time=0.001..2.810 rows=925.00 loops=570) │ │ Filter: ((d_year = 1999) AND (d_qoy = 2)) │ Rows Removed by Filter: 22395 │ │ Storage: Memory Maximum Storage: 1700kB │ │ Planning: │ │ Buffers: shared hit=12 │ │ Planning Time: 2.180 ms │ │ Execution Time: 17166.558 ms │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘