┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Limit (cost=415617.74..415617.75 rows=1 width=32) (actual time=6278.473..6279.272 rows=1.00 loops=1) │ │ Buffers: shared hit=21397 read=198907, temp read=51435 written=73036 │ │ CTE frequent_ss_items │ │ -> Finalize HashAggregate (cost=78507.24..79271.17 rows=19098 width=48) (actual time=2125.253..3165.902 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: 85 Memory Usage: 8257kB Disk Usage: 97760kB │ │ Rows Removed by Filter: 1491101 │ │ Buffers: shared hit=5905 read=48595, temp read=36578 written=51384 │ │ -> Gather (cost=71906.38..77934.29 rows=57295 width=48) (actual time=998.246..1394.185 rows=1597189.00 loops=1) │ │ Workers Planned: 2 │ │ Workers Launched: 2 │ │ Buffers: shared hit=5905 read=48595, temp read=12001 written=19929 │ │ -> Partial HashAggregate (cost=70906.38..71204.79 rows=23873 width=48) (actual time=995.506..1292.610 rows=532396.33 loops=3) │ │ Group Key: substr((item.i_item_desc)::text, 1, 30), item.i_item_sk, date_dim_2.d_date │ │ Batches: 21 Memory Usage: 8257kB Disk Usage: 27800kB │ │ Buffers: shared hit=5905 read=48595, temp read=12001 written=19929 │ │ Worker 0: Batches: 21 Memory Usage: 8257kB Disk Usage: 27840kB │ │ Worker 1: Batches: 21 Memory Usage: 8257kB Disk Usage: 27816kB │ │ -> Parallel Hash Join (cost=3601.97..70667.65 rows=23873 width=40) (actual time=8.943..709.417 rows=551858.00 loops=3) │ │ Hash Cond: (store_sales.ss_item_sk = item.i_item_sk) │ │ Buffers: shared hit=5905 read=48595 │ │ -> Parallel Hash Join (cost=2079.74..69023.06 rows=23873 width=8) (actual time=5.322..298.122 rows=551858.00 loops=3) │ │ Hash Cond: (store_sales.ss_sold_date_sk = date_dim_2.d_date_sk) │ │ Buffers: shared hit=4621 read=48595 │ │ -> Parallel Seq Scan on store_sales (cost=0.00..63792.90 rows=1200090 width=8) (actual time=0.094..79.395 rows=960134.67 loops=3) │ │ Buffers: shared hit=3197 read=48595 │ │ -> Parallel Hash (cost=2068.55..2068.55 rows=895 width=8) (actual time=5.197..5.198 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.528..5.062 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.541..3.541 rows=6000.00 loops=3) │ │ Buckets: 32768 Batches: 1 Memory Usage: 2816kB │ │ Buffers: shared hit=1284 │ │ -> Parallel Seq Scan on item (cost=0.00..1389.88 rows=10588 width=106) (actual time=0.007..1.967 rows=6000.00 loops=3) │ │ Buffers: shared hit=1284 │ │ CTE best_ss_customer │ │ -> Finalize GroupAggregate (cost=248290.35..260324.30 rows=19602 width=36) (actual time=2751.847..2937.416 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=10340 read=96344, temp read=14857 written=21652 │ │ InitPlan 2 │ │ -> Aggregate (cost=83231.91..83231.92 rows=1 width=32) (actual time=1564.003..1564.038 rows=1.00 loops=1) │ │ Buffers: shared hit=6023 read=48031, temp read=5194 written=5215 │ │ -> GroupAggregate (cost=71759.45..82547.49 rows=54754 width=36) (actual time=552.755..1557.316 rows=76412.00 loops=1) │ │ Group Key: customer.c_customer_sk │ │ Buffers: shared hit=6023 read=48031, temp read=5194 written=5215 │ │ -> Gather Merge (cost=71759.45..81315.52 rows=54754 width=14) (actual time=552.744..980.712 rows=1616595.00 loops=1) │ │ Workers Planned: 2 │ │ Workers Launched: 2 │ │ Buffers: shared hit=6023 read=48031, temp read=5194 written=5215 │ │ -> Merge Join (cost=70759.43..73995.53 rows=22814 width=14) (actual time=550.175..742.811 rows=538865.00 loops=3) │ │ Merge Cond: (store_sales_1.ss_customer_sk = customer.c_customer_sk) │ │ Buffers: shared hit=6023 read=48031, temp read=5194 written=5215 │ │ -> Sort (cost=70758.99..70818.67 rows=23873 width=14) (actual time=550.127..624.908 rows=538866.00 loops=3) │ │ Sort Key: store_sales_1.ss_customer_sk │ │ Sort Method: external merge Disk: 13928kB │ │ Buffers: shared hit=5193 read=48031, temp read=5194 written=5215 │ │ Worker 0: Sort Method: external merge Disk: 13768kB │ │ Worker 1: Sort Method: external merge Disk: 13856kB │ │ -> Parallel Hash Join (cost=2079.74..69023.06 rows=23873 width=14) (actual time=5.319..353.361 rows=551858.00 loops=3) │ │ Hash Cond: (store_sales_1.ss_sold_date_sk = date_dim_3.d_date_sk) │ │ Buffers: shared hit=5185 read=48031 │ │ -> Parallel Seq Scan on store_sales store_sales_1 (cost=0.00..63792.90 rows=1200090 width=18) (actual time=0.098..78.411 rows=960134.67 loops=3) │ │ Buffers: shared hit=3761 read=48031 │ │ -> Parallel Hash (cost=2068.55..2068.55 rows=895 width=4) (actual time=5.182..5.182 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.491..5.066 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.044..10.392 rows=99999.67 loops=3) │ │ Heap Fetches: 0 │ │ Index Searches: 3 │ │ Buffers: shared hit=830 │ │ -> Gather Merge (cost=165058.43..175622.21 rows=58807 width=36) (actual time=1187.714..1250.643 rows=166212.00 loops=1) │ │ Workers Planned: 2 │ │ Workers Launched: 2 │ │ Buffers: shared hit=4317 read=48313, temp read=9663 written=16437 │ │ -> Merge Join (cost=164058.41..167834.40 rows=24503 width=36) (actual time=1182.893..1216.692 rows=55404.00 loops=3) │ │ Merge Cond: (store_sales_2.ss_customer_sk = customer_1.c_customer_sk) │ │ Buffers: shared hit=4317 read=48313, temp read=9663 written=16437 │ │ -> Sort (cost=164057.97..164211.82 rows=61537 width=36) (actual time=1182.841..1188.159 rows=55405.00 loops=3) │ │ Sort Key: store_sales_2.ss_customer_sk │ │ Sort Method: external merge Disk: 4400kB │ │ Buffers: shared hit=3487 read=48313, temp read=9663 written=16437 │ │ Worker 0: Sort Method: external merge Disk: 4400kB │ │ Worker 1: Sort Method: external merge Disk: 4376kB │ │ -> Partial HashAggregate (cost=146674.12..159162.96 rows=61537 width=36) (actual time=866.155..1159.595 rows=55405.00 loops=3) │ │ Group Key: store_sales_2.ss_customer_sk │ │ Planned Partitions: 4 Batches: 5 Memory Usage: 8241kB Disk Usage: 23504kB │ │ Buffers: shared hit=3479 read=48313, temp read=8016 written=14787 │ │ Worker 0: Batches: 5 Memory Usage: 8241kB Disk Usage: 23568kB │ │ Worker 1: Batches: 5 Memory Usage: 8241kB Disk Usage: 23512kB │ │ -> Parallel Seq Scan on store_sales store_sales_2 (cost=0.00..63792.90 rows=1200090 width=14) (actual time=0.073..77.520 rows=960134.67 loops=3) │ │ Buffers: shared hit=3479 read=48313 │ │ -> Index Only Scan using customer_pkey on customer customer_1 (cost=0.29..2644.32 rows=100000 width=4) (actual time=0.049..9.848 rows=100000.00 loops=3) │ │ Heap Fetches: 0 │ │ Index Searches: 3 │ │ Buffers: shared hit=830 │ │ -> Aggregate (cost=76022.26..76022.27 rows=1 width=32) (actual time=6278.472..6278.992 rows=1.00 loops=1) │ │ Buffers: shared hit=14712 read=134788, temp read=41550 written=58616 │ │ -> Append (cost=4140.05..76021.82 rows=177 width=32) (actual time=6278.468..6278.989 rows=0.00 loops=1) │ │ Buffers: shared hit=14712 read=134788, temp read=41550 written=58616 │ │ -> Hash Join (cost=4140.05..49238.05 rows=103 width=32) (actual time=6217.582..6217.602 rows=0.00 loops=1) │ │ Hash Cond: (catalog_sales.cs_item_sk = frequent_ss_items.item_sk) │ │ Buffers: shared hit=11830 read=117422, temp read=41550 written=58616 │ │ -> Hash Semi Join (cost=3705.84..48801.65 rows=206 width=14) (actual time=3051.823..3051.843 rows=0.00 loops=1) │ │ Hash Cond: (catalog_sales.cs_bill_customer_sk = best_ss_customer.c_customer_sk) │ │ Buffers: shared hit=5925 read=68827, temp read=4972 written=7232 │ │ -> Gather (cost=3068.78..48160.69 rows=609 width=18) (actual time=54.502..112.724 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.512..110.786 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.095..47.112 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.152..4.153 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.772..4.120 rows=10.33 loops=3) │ │ Filter: ((d_year = 2000) AND (d_moy = 7)) │ │ Rows Removed by Filter: 24339 │ │ Buffers: shared hit=1424 │ │ -> Hash (cost=392.04..392.04 rows=19602 width=4) (actual time=2937.393..2937.393 rows=17.00 loops=1) │ │ Buckets: 32768 Batches: 1 Memory Usage: 257kB │ │ Buffers: shared hit=3655 read=32225, temp read=4972 written=7232 │ │ -> CTE Scan on best_ss_customer (cost=0.00..392.04 rows=19602 width=4) (actual time=2751.850..2937.383 rows=17.00 loops=1) │ │ Storage: Memory Maximum Storage: 17kB │ │ Buffers: shared hit=3655 read=32225, temp read=4972 written=7232 │ │ -> Hash (cost=431.70..431.70 rows=200 width=4) (actual time=3165.753..3165.754 rows=43.00 loops=1) │ │ Buckets: 1024 Batches: 1 Memory Usage: 10kB │ │ Buffers: shared hit=5905 read=48595, temp read=36578 written=51384 │ │ -> HashAggregate (cost=429.70..431.70 rows=200 width=4) (actual time=3165.739..3165.745 rows=43.00 loops=1) │ │ Group Key: frequent_ss_items.item_sk │ │ Batches: 1 Memory Usage: 32kB │ │ Buffers: shared hit=5905 read=48595, temp read=36578 written=51384 │ │ -> CTE Scan on frequent_ss_items (cost=0.00..381.96 rows=19098 width=4) (actual time=2125.257..3165.705 rows=43.00 loops=1) │ │ Storage: Memory Maximum Storage: 19kB │ │ Buffers: shared hit=5905 read=48595, temp read=36578 written=51384 │ │ -> Hash Join (cost=4140.05..26782.88 rows=74 width=32) (actual time=60.885..61.385 rows=0.00 loops=1) │ │ Hash Cond: (web_sales.ws_item_sk = frequent_ss_items_1.item_sk) │ │ Buffers: shared hit=2882 read=17366 │ │ -> Hash Semi Join (cost=3705.84..26347.09 rows=148 width=14) (actual time=60.830..61.325 rows=0.00 loops=1) │ │ Hash Cond: (web_sales.ws_bill_customer_sk = best_ss_customer_1.c_customer_sk) │ │ Buffers: shared hit=2882 read=17366 │ │ -> Gather (cost=3068.78..25707.58 rows=305 width=18) (actual time=6.180..60.441 rows=6668.00 loops=1) │ │ Workers Planned: 2 │ │ Workers Launched: 2 │ │ Buffers: shared hit=2882 read=17366 │ │ -> Parallel Hash Join (cost=2068.78..24677.08 rows=127 width=18) (actual time=4.598..57.985 rows=2222.67 loops=3) │ │ Hash Cond: (web_sales.ws_sold_date_sk = date_dim_1.d_date_sk) │ │ Buffers: shared hit=2882 read=17366 │ │ -> Parallel Seq Scan on web_sales (cost=0.00..21821.43 rows=299743 width=22) (actual time=0.111..23.606 rows=239794.67 loops=3) │ │ Buffers: shared hit=1458 read=17366 │ │ -> Parallel Hash (cost=2068.55..2068.55 rows=18 width=4) (actual time=3.973..3.973 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.602..3.936 rows=10.33 loops=3) │ │ Filter: ((d_year = 2000) AND (d_moy = 7)) │ │ Rows Removed by Filter: 24339 │ │ Buffers: shared hit=1424 │ │ -> Hash (cost=392.04..392.04 rows=19602 width=4) (actual time=0.006..0.007 rows=17.00 loops=1) │ │ Buckets: 32768 Batches: 1 Memory Usage: 257kB │ │ -> CTE Scan on best_ss_customer best_ss_customer_1 (cost=0.00..392.04 rows=19602 width=4) (actual time=0.001..0.003 rows=17.00 loops=1) │ │ Storage: Memory Maximum Storage: 17kB │ │ -> Hash (cost=431.70..431.70 rows=200 width=4) (actual time=0.049..0.050 rows=43.00 loops=1) │ │ Buckets: 1024 Batches: 1 Memory Usage: 10kB │ │ -> HashAggregate (cost=429.70..431.70 rows=200 width=4) (actual time=0.020..0.027 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.96 rows=19098 width=4) (actual time=0.001..0.004 rows=43.00 loops=1) │ │ Storage: Memory Maximum Storage: 19kB │ │ Planning: │ │ Buffers: shared hit=50 │ │ Planning Time: 2.446 ms │ │ Execution Time: 6293.846 ms │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘