query-23.master.explain
application/octet-stream
Filename: query-23.master.explain
Type: application/octet-stream
Part: 0
Message:
Re: Eager aggregation, take 3
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit (cost=443280.24..443280.25 rows=1 width=32) (actual time=5152.691..5153.357 rows=1.00 loops=1) │
│ Buffers: shared hit=21117 read=198641, temp read=32443 written=46477 │
│ CTE frequent_ss_items │
│ -> HashAggregate (cost=77970.21..78734.16 rows=19099 width=48) (actual time=1082.051..2014.158 rows=43.00 loops=1) │
│ Group Key: (substr((item.i_item_desc)::text, 1, 30)), item.i_item_sk, date_dim_2.d_date │
│ Filter: (count(*) > 4) │
│ Batches: 21 Memory Usage: 8257kB Disk Usage: 89312kB │
│ Rows Removed by Filter: 1491101 │
│ Buffers: shared hit=6578 read=47922, temp read=19095 written=27470 │
│ -> Gather (cost=4601.97..77397.25 rows=57296 width=40) (actual time=12.577..355.882 rows=1655574.00 loops=1) │
│ Workers Planned: 2 │
│ Workers Launched: 2 │
│ Buffers: shared hit=6578 read=47922 │
│ -> Parallel Hash Join (cost=3601.97..70667.65 rows=23873 width=40) (actual time=8.486..707.665 rows=551858.00 loops=3) │
│ Hash Cond: (store_sales.ss_item_sk = item.i_item_sk) │
│ Buffers: shared hit=6578 read=47922 │
│ -> Parallel Hash Join (cost=2079.74..69023.06 rows=23873 width=8) (actual time=5.333..295.584 rows=551858.00 loops=3) │
│ Hash Cond: (store_sales.ss_sold_date_sk = date_dim_2.d_date_sk) │
│ Buffers: shared hit=5294 read=47922 │
│ -> Parallel Seq Scan on store_sales (cost=0.00..63792.90 rows=1200090 width=8) (actual time=0.020..78.806 rows=960134.67 loops=3) │
│ Buffers: shared hit=3870 read=47922 │
│ -> Parallel Hash (cost=2068.55..2068.55 rows=895 width=8) (actual time=5.283..5.283 rows=487.00 loops=3) │
│ Buckets: 2048 Batches: 1 Memory Usage: 112kB │
│ Buffers: shared hit=1424 │
│ -> Parallel Seq Scan on date_dim date_dim_2 (cost=0.00..2068.55 rows=895 width=8) (actual time=2.577..5.155 rows=487.00 loops=3) │
│ Filter: (d_year = ANY ('{2000,2001,2002,2003}'::integer[])) │
│ Rows Removed by Filter: 23863 │
│ Buffers: shared hit=1424 │
│ -> Parallel Hash (cost=1389.88..1389.88 rows=10588 width=106) (actual time=3.097..3.097 rows=6000.00 loops=3) │
│ Buckets: 32768 Batches: 1 Memory Usage: 2784kB │
│ Buffers: shared hit=1284 │
│ -> Parallel Seq Scan on item (cost=0.00..1389.88 rows=10588 width=106) (actual time=0.008..1.739 rows=6000.00 loops=3) │
│ Buffers: shared hit=1284 │
│ CTE best_ss_customer │
│ -> Finalize GroupAggregate (cost=256452.57..287954.53 rows=33333 width=36) (actual time=2810.198..2964.457 rows=17.00 loops=1) │
│ Group Key: customer_1.c_customer_sk │
│ Filter: (sum(((store_sales_2.ss_quantity)::numeric * store_sales_2.ss_sales_price)) > (0.95000000000000000000 * (InitPlan 2).col1)) │
│ Rows Removed by Filter: 90841 │
│ Buffers: shared hit=9448 read=96690, temp read=13348 written=19007 │
│ InitPlan 2 │
│ -> Aggregate (cost=83231.91..83231.92 rows=1 width=32) (actual time=1559.939..1559.965 rows=1.00 loops=1) │
│ Buffers: shared hit=5850 read=48204, temp read=5193 written=5214 │
│ -> GroupAggregate (cost=71759.45..82547.49 rows=54754 width=36) (actual time=553.364..1553.235 rows=76412.00 loops=1) │
│ Group Key: customer.c_customer_sk │
│ Buffers: shared hit=5850 read=48204, temp read=5193 written=5214 │
│ -> Gather Merge (cost=71759.45..81315.52 rows=54754 width=14) (actual time=553.348..976.805 rows=1616595.00 loops=1) │
│ Workers Planned: 2 │
│ Workers Launched: 2 │
│ Buffers: shared hit=5850 read=48204, temp read=5193 written=5214 │
│ -> Merge Join (cost=70759.43..73995.53 rows=22814 width=14) (actual time=550.425..742.392 rows=538865.00 loops=3) │
│ Merge Cond: (store_sales_1.ss_customer_sk = customer.c_customer_sk) │
│ Buffers: shared hit=5850 read=48204, temp read=5193 written=5214 │
│ -> Sort (cost=70758.99..70818.67 rows=23873 width=14) (actual time=550.369..624.451 rows=538866.00 loops=3) │
│ Sort Key: store_sales_1.ss_customer_sk │
│ Sort Method: external merge Disk: 13840kB │
│ Buffers: shared hit=5020 read=48204, temp read=5193 written=5214 │
│ Worker 0: Sort Method: external merge Disk: 13816kB │
│ Worker 1: Sort Method: external merge Disk: 13888kB │
│ -> Parallel Hash Join (cost=2079.74..69023.06 rows=23873 width=14) (actual time=5.282..353.040 rows=551858.00 loops=3) │
│ Hash Cond: (store_sales_1.ss_sold_date_sk = date_dim_3.d_date_sk) │
│ Buffers: shared hit=5012 read=48204 │
│ -> Parallel Seq Scan on store_sales store_sales_1 (cost=0.00..63792.90 rows=1200090 width=18) (actual time=0.009..79.137 rows=960134.67 loops=3) │
│ Buffers: shared hit=3588 read=48204 │
│ -> Parallel Hash (cost=2068.55..2068.55 rows=895 width=4) (actual time=5.240..5.240 rows=487.00 loops=3) │
│ Buckets: 2048 Batches: 1 Memory Usage: 112kB │
│ Buffers: shared hit=1424 │
│ -> Parallel Seq Scan on date_dim date_dim_3 (cost=0.00..2068.55 rows=895 width=4) (actual time=2.540..5.127 rows=487.00 loops=3) │
│ Filter: (d_year = ANY ('{2000,2001,2002,2003}'::integer[])) │
│ Rows Removed by Filter: 23863 │
│ Buffers: shared hit=1424 │
│ -> Index Only Scan using customer_pkey on customer (cost=0.29..2644.32 rows=100000 width=4) (actual time=0.050..10.363 rows=100000.00 loops=3) │
│ Heap Fetches: 0 │
│ Index Searches: 3 │
│ Buffers: shared hit=830 │
│ -> Gather Merge (cost=173220.65..201172.61 rows=240000 width=36) (actual time=1250.147..1284.368 rows=166033.00 loops=1) │
│ Workers Planned: 2 │
│ Workers Launched: 2 │
│ Buffers: shared hit=3598 read=48486, temp read=8155 written=13793 │
│ -> Sort (cost=172220.63..172470.63 rows=100000 width=36) (actual time=1246.679..1252.364 rows=55344.33 loops=3) │
│ Sort Key: customer_1.c_customer_sk │
│ Sort Method: external merge Disk: 4384kB │
│ Buffers: shared hit=3598 read=48486, temp read=8155 written=13793 │
│ Worker 0: Sort Method: external merge Disk: 4392kB │
│ Worker 1: Sort Method: external merge Disk: 4392kB │
│ -> Partial HashAggregate (cost=148729.14..161178.81 rows=100000 width=36) (actual time=946.661..1221.729 rows=55344.33 loops=3) │
│ Group Key: customer_1.c_customer_sk │
│ Planned Partitions: 4 Batches: 5 Memory Usage: 8241kB Disk Usage: 19456kB │
│ Buffers: shared hit=3584 read=48486, temp read=6509 written=12144 │
│ Worker 0: Batches: 5 Memory Usage: 8241kB Disk Usage: 19456kB │
│ Worker 1: Batches: 5 Memory Usage: 8241kB Disk Usage: 19480kB │
│ -> Parallel Hash Join (cost=2581.83..69525.09 rows=1146846 width=14) (actual time=10.759..459.558 rows=916884.00 loops=3) │
│ Hash Cond: (store_sales_2.ss_customer_sk = customer_1.c_customer_sk) │
│ Buffers: shared hit=3584 read=48486 │
│ -> Parallel Seq Scan on store_sales store_sales_2 (cost=0.00..63792.90 rows=1200090 width=14) (actual time=0.008..75.386 rows=960134.67 loops=3) │
│ Buffers: shared hit=3306 read=48486 │
│ -> Parallel Hash (cost=2060.99..2060.99 rows=41667 width=4) (actual time=10.571..10.572 rows=33333.33 loops=3) │
│ Buckets: 131072 Batches: 1 Memory Usage: 4992kB │
│ Buffers: shared hit=278 │
│ -> Parallel Index Only Scan using customer_pkey on customer customer_1 (cost=0.29..2060.99 rows=41667 width=4) (actual time=0.038..4.301 rows=33333.33 loops=3) │
│ Heap Fetches: 0 │
│ Index Searches: 1 │
│ Buffers: shared hit=278 │
│ -> Aggregate (cost=76591.55..76591.56 rows=1 width=32) (actual time=5152.690..5153.013 rows=1.00 loops=1) │
│ Buffers: shared hit=15066 read=134100, temp read=23542 written=33799 │
│ -> Append (cost=4586.33..76590.80 rows=301 width=32) (actual time=5152.687..5153.010 rows=0.00 loops=1) │
│ Buffers: shared hit=15066 read=134100, temp read=23542 written=33799 │
│ -> Hash Semi Join (cost=4586.33..49686.84 rows=175 width=32) (actual time=5091.468..5091.496 rows=0.00 loops=1) │
│ Hash Cond: (catalog_sales.cs_bill_customer_sk = best_ss_customer.c_customer_sk) │
│ Buffers: shared hit=12245 read=116673, temp read=23542 written=33799 │
│ -> Hash Join (cost=3503.00..48599.90 rows=304 width=14) (actual time=2068.443..2127.107 rows=36.00 loops=1) │
│ Hash Cond: (catalog_sales.cs_item_sk = frequent_ss_items.item_sk) │
│ Buffers: shared hit=8848 read=84524, temp read=19095 written=27470 │
│ -> Gather (cost=3068.78..48160.69 rows=609 width=18) (actual time=54.361..111.505 rows=14018.00 loops=1) │
│ Workers Planned: 2 │
│ Workers Launched: 2 │
│ Buffers: shared hit=2270 read=36602 │
│ -> Parallel Hash Join (cost=2068.78..47099.79 rows=254 width=18) (actual time=52.242..109.408 rows=4672.67 loops=3) │
│ Hash Cond: (catalog_sales.cs_sold_date_sk = date_dim.d_date_sk) │
│ Buffers: shared hit=2270 read=36602 │
│ -> Parallel Seq Scan on catalog_sales (cost=0.00..43454.28 rows=600628 width=22) (actual time=0.076..46.359 rows=480516.00 loops=3) │
│ Buffers: shared hit=846 read=36602 │
│ -> Parallel Hash (cost=2068.55..2068.55 rows=18 width=4) (actual time=4.270..4.270 rows=10.33 loops=3) │
│ Buckets: 1024 Batches: 1 Memory Usage: 72kB │
│ Buffers: shared hit=1424 │
│ -> Parallel Seq Scan on date_dim (cost=0.00..2068.55 rows=18 width=4) (actual time=2.820..4.236 rows=10.33 loops=3) │
│ Filter: ((d_year = 2000) AND (d_moy = 7)) │
│ Rows Removed by Filter: 24339 │
│ Buffers: shared hit=1424 │
│ -> Hash (cost=431.73..431.73 rows=200 width=4) (actual time=2014.027..2014.028 rows=43.00 loops=1) │
│ Buckets: 1024 Batches: 1 Memory Usage: 10kB │
│ Buffers: shared hit=6578 read=47922, temp read=19095 written=27470 │
│ -> HashAggregate (cost=429.73..431.73 rows=200 width=4) (actual time=2014.011..2014.017 rows=43.00 loops=1) │
│ Group Key: frequent_ss_items.item_sk │
│ Batches: 1 Memory Usage: 32kB │
│ Buffers: shared hit=6578 read=47922, temp read=19095 written=27470 │
│ -> CTE Scan on frequent_ss_items (cost=0.00..381.98 rows=19099 width=4) (actual time=1082.054..2013.983 rows=43.00 loops=1) │
│ Storage: Memory Maximum Storage: 19kB │
│ Buffers: shared hit=6578 read=47922, temp read=19095 written=27470 │
│ -> Hash (cost=666.66..666.66 rows=33333 width=4) (actual time=2964.342..2964.342 rows=17.00 loops=1) │
│ Buckets: 65536 Batches: 1 Memory Usage: 513kB │
│ Buffers: shared hit=3397 read=32149, temp read=4447 written=6329 │
│ -> CTE Scan on best_ss_customer (cost=0.00..666.66 rows=33333 width=4) (actual time=2810.201..2964.332 rows=17.00 loops=1) │
│ Storage: Memory Maximum Storage: 17kB │
│ Buffers: shared hit=3397 read=32149, temp read=4447 written=6329 │
│ -> Merge Semi Join (cost=26149.81..26902.45 rows=126 width=32) (actual time=61.215..61.511 rows=0.00 loops=1) │
│ Merge Cond: (web_sales.ws_bill_customer_sk = best_ss_customer_1.c_customer_sk) │
│ Buffers: shared hit=2821 read=17427 │
│ -> Sort (cost=26149.81..26150.19 rows=152 width=14) (actual time=61.205..61.501 rows=22.00 loops=1) │
│ Sort Key: web_sales.ws_bill_customer_sk │
│ Sort Method: quicksort Memory: 25kB │
│ Buffers: shared hit=2821 read=17427 │
│ -> Hash Join (cost=3503.00..26144.30 rows=152 width=14) (actual time=11.455..61.467 rows=22.00 loops=1) │
│ Hash Cond: (web_sales.ws_item_sk = frequent_ss_items_1.item_sk) │
│ Buffers: shared hit=2821 read=17427 │
│ -> Gather (cost=3068.78..25707.58 rows=305 width=18) (actual time=6.443..60.627 rows=6668.00 loops=1) │
│ Workers Planned: 2 │
│ Workers Launched: 2 │
│ Buffers: shared hit=2821 read=17427 │
│ -> Parallel Hash Join (cost=2068.78..24677.08 rows=127 width=18) (actual time=5.306..58.277 rows=2222.67 loops=3) │
│ Hash Cond: (web_sales.ws_sold_date_sk = date_dim_1.d_date_sk) │
│ Buffers: shared hit=2821 read=17427 │
│ -> Parallel Seq Scan on web_sales (cost=0.00..21821.43 rows=299743 width=22) (actual time=0.055..23.821 rows=239794.67 loops=3) │
│ Buffers: shared hit=1397 read=17427 │
│ -> Parallel Hash (cost=2068.55..2068.55 rows=18 width=4) (actual time=4.250..4.250 rows=10.33 loops=3) │
│ Buckets: 1024 Batches: 1 Memory Usage: 72kB │
│ Buffers: shared hit=1424 │
│ -> Parallel Seq Scan on date_dim date_dim_1 (cost=0.00..2068.55 rows=18 width=4) (actual time=2.733..4.199 rows=10.33 loops=3) │
│ Filter: ((d_year = 2000) AND (d_moy = 7)) │
│ Rows Removed by Filter: 24339 │
│ Buffers: shared hit=1424 │
│ -> Hash (cost=431.73..431.73 rows=200 width=4) (actual time=0.032..0.033 rows=43.00 loops=1) │
│ Buckets: 1024 Batches: 1 Memory Usage: 10kB │
│ -> HashAggregate (cost=429.73..431.73 rows=200 width=4) (actual time=0.019..0.026 rows=43.00 loops=1) │
│ Group Key: frequent_ss_items_1.item_sk │
│ Batches: 1 Memory Usage: 32kB │
│ -> CTE Scan on frequent_ss_items frequent_ss_items_1 (cost=0.00..381.98 rows=19099 width=4) (actual time=0.001..0.004 rows=43.00 loops=1) │
│ Storage: Memory Maximum Storage: 19kB │
│ -> CTE Scan on best_ss_customer best_ss_customer_1 (cost=0.00..666.66 rows=33333 width=4) (actual time=0.001..0.002 rows=16.00 loops=1) │
│ Storage: Memory Maximum Storage: 17kB │
│ Planning: │
│ Buffers: shared hit=50 │
│ Planning Time: 1.917 ms │
│ Execution Time: 5161.436 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘