                                                                                                      QUERY PLAN                                                                                                       
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3524256.05..3524256.30 rows=13 width=108) (actual time=299322.408..299322.693 rows=100 loops=1)
   Buffers: shared hit=114405266
   CTE cross_items
     ->  Hash Join  (cost=1768414.34..1777201.59 rows=27 width=4) (actual time=70812.620..70932.587 rows=92954 loops=1)
           Hash Cond: ((item_3.i_brand_id = a.brand_id) AND (item_3.i_class_id = a.class_id) AND (item_3.i_category_id = a.category_id))
           Buffers: shared hit=1110410
           ->  Seq Scan on item item_3  (cost=0.00..7984.00 rows=102000 width=16) (actual time=0.020..22.179 rows=102000 loops=1)
                 Buffers: shared hit=6964
           ->  Hash  (cost=1768235.84..1768235.84 rows=10200 width=12) (actual time=70812.525..70812.542 rows=11478 loops=1)
                 Buckets: 16384  Batches: 1  Memory Usage: 622kB
                 Buffers: shared hit=1103446
                 ->  Subquery Scan on a  (cost=11773.03..1768235.84 rows=10200 width=12) (actual time=70803.806..70807.682 rows=11676 loops=1)
                       Buffers: shared hit=1103446
                       ->  HashSetOp Intersect  (cost=11773.03..1768133.84 rows=10200 width=16) (actual time=70803.803..70804.941 rows=11676 loops=1)
                             Buffers: shared hit=1103446
                             ->  Append  (cost=11773.03..1767271.35 rows=114999 width=16) (actual time=60385.765..68508.887 rows=4330904 loops=1)
                                   Buffers: shared hit=1103446
                                   ->  Result  (cost=11773.03..1474977.78 rows=10200 width=16) (actual time=60385.764..60390.365 rows=11676 loops=1)
                                         Buffers: shared hit=907329
                                         ->  HashSetOp Intersect  (cost=11773.03..1474875.78 rows=10200 width=16) (actual time=60385.759..60387.650 rows=11676 loops=1)
                                               Buffers: shared hit=907329
                                               ->  Append  (cost=11773.03..1470307.47 rows=609108 width=16) (actual time=115.096..46788.192 rows=25067754 loops=1)
                                                     Buffers: shared hit=907329
                                                     ->  Subquery Scan on "*SELECT* 1"  (cost=11773.03..897353.20 rows=400429 width=16) (actual time=115.094..27583.804 rows=16496728 loops=1)
                                                           Buffers: shared hit=525277
                                                           ->  Hash Join  (cost=11773.03..893348.91 rows=400429 width=12) (actual time=115.093..23939.540 rows=16496728 loops=1)
                                                                 Hash Cond: (store_sales_1.ss_item_sk = iss.i_item_sk)
                                                                 Buffers: shared hit=525277
                                                                 ->  Hash Join  (cost=2514.03..883038.74 rows=400429 width=4) (actual time=19.870..15505.310 rows=16496728 loops=1)
                                                                       Hash Cond: (store_sales_1.ss_sold_date_sk = d1.d_date_sk)
                                                                       Buffers: shared hit=518313
                                                                       ->  Seq Scan on store_sales store_sales_1  (cost=0.00..804917.92 rows=28800992 width=8) (actual time=0.004..4310.744 rows=28800991 loops=1)
                                                                             Buffers: shared hit=516908
                                                                       ->  Hash  (cost=2500.73..2500.73 rows=1064 width=4) (actual time=19.847..19.848 rows=1096 loops=1)
                                                                             Buckets: 2048  Batches: 1  Memory Usage: 55kB
                                                                             Buffers: shared hit=1405
                                                                             ->  Seq Scan on date_dim d1  (cost=0.00..2500.73 rows=1064 width=4) (actual time=9.792..19.541 rows=1096 loops=1)
                                                                                   Filter: ((d_year >= 1999) AND (d_year <= 2001))
                                                                                   Rows Removed by Filter: 71953
                                                                                   Buffers: shared hit=1405
                                                                 ->  Hash  (cost=7984.00..7984.00 rows=102000 width=16) (actual time=94.865..94.866 rows=102000 loops=1)
                                                                       Buckets: 131072  Batches: 1  Memory Usage: 5803kB
                                                                       Buffers: shared hit=6964
                                                                       ->  Seq Scan on item iss  (cost=0.00..7984.00 rows=102000 width=16) (actual time=0.006..60.753 rows=102000 loops=1)
                                                                             Buffers: shared hit=6964
                                                     ->  Subquery Scan on "*SELECT* 2"  (cost=11773.03..569908.73 rows=208679 width=16) (actual time=1235.364..15692.343 rows=8571026 loops=1)
                                                           Buffers: shared hit=382052
                                                           ->  Hash Join  (cost=11773.03..567821.94 rows=208679 width=12) (actual time=1235.362..13794.964 rows=8571026 loops=1)
                                                                 Hash Cond: (catalog_sales_1.cs_item_sk = ics.i_item_sk)
                                                                 Buffers: shared hit=382052
                                                                 ->  Hash Join  (cost=2514.03..558015.14 rows=208679 width=4) (actual time=1142.407..9488.024 rows=8571026 loops=1)
                                                                       Hash Cond: (catalog_sales_1.cs_sold_date_sk = d2.d_date_sk)
                                                                       Buffers: shared hit=375088
                                                                       ->  Seq Scan on catalog_sales catalog_sales_1  (cost=0.00..517695.61 rows=14401261 width=8) (actual time=0.010..2244.900 rows=14401261 loops=1)
                                                                             Buffers: shared hit=373683
                                                                       ->  Hash  (cost=2500.73..2500.73 rows=1064 width=4) (actual time=18.390..18.391 rows=1096 loops=1)
                                                                             Buckets: 2048  Batches: 1  Memory Usage: 55kB
                                                                             Buffers: shared hit=1405
                                                                             ->  Seq Scan on date_dim d2  (cost=0.00..2500.73 rows=1064 width=4) (actual time=8.534..18.080 rows=1096 loops=1)
                                                                                   Filter: ((d_year >= 1999) AND (d_year <= 2001))
                                                                                   Rows Removed by Filter: 71953
                                                                                   Buffers: shared hit=1405
                                                                 ->  Hash  (cost=7984.00..7984.00 rows=102000 width=16) (actual time=92.570..92.571 rows=102000 loops=1)
                                                                       Buckets: 131072  Batches: 1  Memory Usage: 5803kB
                                                                       Buffers: shared hit=6964
                                                                       ->  Seq Scan on item ics  (cost=0.00..7984.00 rows=102000 width=16) (actual time=0.011..58.481 rows=102000 loops=1)
                                                                             Buffers: shared hit=6964
                                   ->  Subquery Scan on "*SELECT* 3"  (cost=11773.03..291718.57 rows=104799 width=16) (actual time=110.735..7509.850 rows=4319228 loops=1)
                                         Buffers: shared hit=196117
                                         ->  Hash Join  (cost=11773.03..290670.58 rows=104799 width=12) (actual time=110.733..6554.346 rows=4319228 loops=1)
                                               Hash Cond: (web_sales_1.ws_item_sk = iws.i_item_sk)
                                               Buffers: shared hit=196117
                                               ->  Hash Join  (cost=2514.03..281136.47 rows=104799 width=4) (actual time=18.342..4302.490 rows=4319228 loops=1)
                                                     Hash Cond: (web_sales_1.ws_sold_date_sk = d3.d_date_sk)
                                                     Buffers: shared hit=189153
                                                     ->  Seq Scan on web_sales web_sales_1  (cost=0.00..259726.89 rows=7197889 width=8) (actual time=0.018..1140.582 rows=7197566 loops=1)
                                                           Buffers: shared hit=187748
                                                     ->  Hash  (cost=2500.73..2500.73 rows=1064 width=4) (actual time=18.301..18.302 rows=1096 loops=1)
                                                           Buckets: 2048  Batches: 1  Memory Usage: 55kB
                                                           Buffers: shared hit=1405
                                                           ->  Seq Scan on date_dim d3  (cost=0.00..2500.73 rows=1064 width=4) (actual time=8.508..17.995 rows=1096 loops=1)
                                                                 Filter: ((d_year >= 1999) AND (d_year <= 2001))
                                                                 Rows Removed by Filter: 71953
                                                                 Buffers: shared hit=1405
                                               ->  Hash  (cost=7984.00..7984.00 rows=102000 width=16) (actual time=92.027..92.028 rows=102000 loops=1)
                                                     Buckets: 131072  Batches: 1  Memory Usage: 5803kB
                                                     Buffers: shared hit=6964
                                                     ->  Seq Scan on item iws  (cost=0.00..7984.00 rows=102000 width=16) (actual time=0.007..57.925 rows=102000 loops=1)
                                                           Buffers: shared hit=6964
   CTE avg_sales
     ->  Aggregate  (cost=1738253.26..1738253.27 rows=1 width=32) (actual time=52789.751..52789.758 rows=1 loops=1)
           Buffers: shared hit=1082554
           ->  Append  (cost=2514.03..1732898.96 rows=713907 width=10) (actual time=16.174..34210.648 rows=29386982 loops=1)
                 Buffers: shared hit=1082554
                 ->  Hash Join  (cost=2514.03..883038.74 rows=400429 width=10) (actual time=16.172..16389.076 rows=16496728 loops=1)
                       Hash Cond: (store_sales_2.ss_sold_date_sk = date_dim_3.d_date_sk)
                       Buffers: shared hit=518313
                       ->  Seq Scan on store_sales store_sales_2  (cost=0.00..804917.92 rows=28800992 width=14) (actual time=0.017..4114.305 rows=28800991 loops=1)
                             Buffers: shared hit=516908
                       ->  Hash  (cost=2500.73..2500.73 rows=1064 width=4) (actual time=16.130..16.130 rows=1096 loops=1)
                             Buckets: 2048  Batches: 1  Memory Usage: 55kB
                             Buffers: shared hit=1405
                             ->  Seq Scan on date_dim date_dim_3  (cost=0.00..2500.73 rows=1064 width=4) (actual time=7.645..15.899 rows=1096 loops=1)
                                   Filter: ((d_year >= 1999) AND (d_year <= 2001))
                                   Rows Removed by Filter: 71953
                                   Buffers: shared hit=1405
                 ->  Hash Join  (cost=2514.03..558015.14 rows=208679 width=10) (actual time=1120.853..9156.241 rows=8571026 loops=1)
                       Hash Cond: (catalog_sales_2.cs_sold_date_sk = date_dim_4.d_date_sk)
                       Buffers: shared hit=375088
                       ->  Seq Scan on catalog_sales catalog_sales_2  (cost=0.00..517695.61 rows=14401261 width=14) (actual time=0.010..2151.625 rows=14401261 loops=1)
                             Buffers: shared hit=373683
                       ->  Hash  (cost=2500.73..2500.73 rows=1064 width=4) (actual time=18.086..18.086 rows=1096 loops=1)
                             Buckets: 2048  Batches: 1  Memory Usage: 55kB
                             Buffers: shared hit=1405
                             ->  Seq Scan on date_dim date_dim_4  (cost=0.00..2500.73 rows=1064 width=4) (actual time=8.470..17.797 rows=1096 loops=1)
                                   Filter: ((d_year >= 1999) AND (d_year <= 2001))
                                   Rows Removed by Filter: 71953
                                   Buffers: shared hit=1405
                 ->  Hash Join  (cost=2514.03..281136.47 rows=104799 width=10) (actual time=18.232..4604.020 rows=4319228 loops=1)
                       Hash Cond: (web_sales_2.ws_sold_date_sk = date_dim_5.d_date_sk)
                       Buffers: shared hit=189153
                       ->  Seq Scan on web_sales web_sales_2  (cost=0.00..259726.89 rows=7197889 width=14) (actual time=0.023..1088.669 rows=7197566 loops=1)
                             Buffers: shared hit=187748
                       ->  Hash  (cost=2500.73..2500.73 rows=1064 width=4) (actual time=18.190..18.191 rows=1096 loops=1)
                             Buckets: 2048  Batches: 1  Memory Usage: 55kB
                             Buffers: shared hit=1405
                             ->  Seq Scan on date_dim date_dim_5  (cost=0.00..2500.73 rows=1064 width=4) (actual time=8.534..17.900 rows=1096 loops=1)
                                   Filter: ((d_year >= 1999) AND (d_year <= 2001))
                                   Rows Removed by Filter: 71953
                                   Buffers: shared hit=1405
   ->  GroupAggregate  (cost=8801.19..8801.43 rows=13 width=108) (actual time=299322.406..299322.650 rows=100 loops=1)
         Group Key: ('store'::text), item.i_brand_id, item.i_class_id, item.i_category_id
         Group Key: ('store'::text), item.i_brand_id, item.i_class_id
         Group Key: ('store'::text), item.i_brand_id
         Group Key: ('store'::text)
         Group Key: ()
         Buffers: shared hit=114405266
         ->  Sort  (cost=8801.19..8801.19 rows=3 width=84) (actual time=299322.391..299322.413 rows=82 loops=1)
               Sort Key: ('store'::text), item.i_brand_id, item.i_class_id, item.i_category_id
               Sort Method: quicksort  Memory: 4163kB
               Buffers: shared hit=114405266
               ->  Append  (cost=5281.11..8801.16 rows=3 width=84) (actual time=183521.431..299272.958 rows=33616 loops=1)
                     Buffers: shared hit=114405260
                     ->  GroupAggregate  (cost=5281.11..5281.25 rows=1 width=84) (actual time=183521.430..184287.927 rows=11395 loops=1)
                           Group Key: item.i_brand_id, item.i_class_id, item.i_category_id
                           Filter: (sum(((store_sales.ss_quantity)::numeric * store_sales.ss_list_price)) > $2)
                           Buffers: shared hit=30445800
                           InitPlan 3 (returns $2)
                             ->  CTE Scan on avg_sales  (cost=0.00..0.02 rows=1 width=32) (actual time=52789.758..52789.758 rows=1 loops=1)
                                   Buffers: shared hit=1082554
                           ->  Sort  (cost=5281.09..5281.10 rows=4 width=22) (actual time=130731.293..130819.579 rows=874217 loops=1)
                                 Sort Key: item.i_brand_id, item.i_class_id, item.i_category_id
                                 Sort Method: quicksort  Memory: 92223kB
                                 Buffers: shared hit=29363246
                                 ->  Hash Join  (cost=2502.45..5281.05 rows=4 width=22) (actual time=71049.776..130102.156 rows=874217 loops=1)
                                       Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk)
                                       Buffers: shared hit=29363246
                                       ->  Nested Loop  (cost=1.34..2755.68 rows=9242 width=26) (actual time=71031.936..124195.813 rows=27690393 loops=1)
                                             Buffers: shared hit=29361841
                                             ->  Nested Loop  (cost=0.90..221.25 rows=27 width=20) (actual time=71031.901..71684.096 rows=92954 loops=1)
                                                   Buffers: shared hit=1389272
                                                   ->  HashAggregate  (cost=0.61..0.88 rows=27 width=4) (actual time=71031.868..71084.251 rows=92954 loops=1)
                                                         Group Key: cross_items.ss_item_sk
                                                         Batches: 1  Memory Usage: 11305kB
                                                         Buffers: shared hit=1110410
                                                         ->  CTE Scan on cross_items  (cost=0.00..0.54 rows=27 width=4) (actual time=70812.624..70973.305 rows=92954 loops=1)
                                                               Buffers: shared hit=1110410
                                                   ->  Index Scan using item_pkey on item  (cost=0.29..8.16 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=92954)
                                                         Index Cond: (i_item_sk = cross_items.ss_item_sk)
                                                         Buffers: shared hit=278862
                                             ->  Index Scan using store_sales_ss_item_sk_idx on store_sales  (cost=0.44..90.45 rows=342 width=18) (actual time=0.011..0.461 rows=298 loops=92954)
                                                   Index Cond: (ss_item_sk = item.i_item_sk)
                                                   Buffers: shared hit=27972569
                                       ->  Hash  (cost=2500.73..2500.73 rows=30 width=4) (actual time=17.785..17.786 rows=30 loops=1)
                                             Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                             Buffers: shared hit=1405
                                             ->  Seq Scan on date_dim  (cost=0.00..2500.73 rows=30 width=4) (actual time=9.106..17.775 rows=30 loops=1)
                                                   Filter: ((d_year = 2001) AND (d_moy = 11))
                                                   Rows Removed by Filter: 73019
                                                   Buffers: shared hit=1405
                     ->  GroupAggregate  (cost=2260.04..2260.11 rows=1 width=84) (actual time=75510.802..75933.083 rows=11376 loops=1)
                           Group Key: item_1.i_brand_id, item_1.i_class_id, item_1.i_category_id
                           Filter: (sum(((catalog_sales.cs_quantity)::numeric * catalog_sales.cs_list_price)) > $3)
                           Rows Removed by Filter: 19
                           Buffers: shared hit=55724197
                           InitPlan 4 (returns $3)
                             ->  CTE Scan on avg_sales avg_sales_1  (cost=0.00..0.02 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=1)
                           ->  Sort  (cost=2260.02..2260.02 rows=2 width=22) (actual time=75510.572..75560.033 rows=462066 loops=1)
                                 Sort Key: item_1.i_brand_id, item_1.i_class_id, item_1.i_category_id
                                 Sort Method: quicksort  Memory: 48352kB
                                 Buffers: shared hit=55724197
                                 ->  Nested Loop  (cost=1.63..2260.01 rows=2 width=22) (actual time=68.806..75152.395 rows=462066 loops=1)
                                       Buffers: shared hit=55724197
                                       ->  Nested Loop  (cost=1.33..771.73 rows=4718 width=26) (actual time=67.445..27992.485 rows=13843675 loops=1)
                                             Buffers: shared hit=14401033
                                             ->  Nested Loop  (cost=0.90..221.25 rows=27 width=20) (actual time=67.405..674.939 rows=92954 loops=1)
                                                   Buffers: shared hit=278862
                                                   ->  HashAggregate  (cost=0.61..0.88 rows=27 width=4) (actual time=67.365..119.172 rows=92954 loops=1)
                                                         Group Key: cross_items_1.ss_item_sk
                                                         Batches: 1  Memory Usage: 11305kB
                                                         ->  CTE Scan on cross_items cross_items_1  (cost=0.00..0.54 rows=27 width=4) (actual time=0.002..13.142 rows=92954 loops=1)
                                                   ->  Index Scan using item_pkey on item item_1  (cost=0.29..8.16 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=92954)
                                                         Index Cond: (i_item_sk = cross_items_1.ss_item_sk)
                                                         Buffers: shared hit=278862
                                             ->  Index Scan using catalog_sales_cs_item_sk_idx on catalog_sales  (cost=0.43..18.64 rows=175 width=18) (actual time=0.008..0.229 rows=149 loops=92954)
                                                   Index Cond: (cs_item_sk = item_1.i_item_sk)
                                                   Buffers: shared hit=14122171
                                       ->  Index Scan using date_dim_pkey on date_dim date_dim_1  (cost=0.29..0.32 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=13843675)
                                             Index Cond: (d_date_sk = catalog_sales.cs_sold_date_sk)
                                             Filter: ((d_year = 2001) AND (d_moy = 11))
                                             Rows Removed by Filter: 1
                                             Buffers: shared hit=41323164
                     ->  GroupAggregate  (cost=1259.72..1259.76 rows=1 width=84) (actual time=38830.361..39046.839 rows=10845 loops=1)
                           Group Key: item_2.i_brand_id, item_2.i_class_id, item_2.i_category_id
                           Filter: (sum(((web_sales.ws_quantity)::numeric * web_sales.ws_list_price)) > $4)
                           Rows Removed by Filter: 485
                           Buffers: shared hit=28235263
                           InitPlan 5 (returns $4)
                             ->  CTE Scan on avg_sales avg_sales_2  (cost=0.00..0.02 rows=1 width=32) (actual time=0.002..0.003 rows=1 loops=1)
                           ->  Sort  (cost=1259.70..1259.71 rows=1 width=22) (actual time=38830.196..38854.089 rows=229671 loops=1)
                                 Sort Key: item_2.i_brand_id, item_2.i_class_id, item_2.i_category_id
                                 Sort Method: quicksort  Memory: 24087kB
                                 Buffers: shared hit=28235263
                                 ->  Nested Loop  (cost=1.62..1259.69 rows=1 width=22) (actual time=66.908..38641.297 rows=229671 loops=1)
                                       Buffers: shared hit=28235263
                                       ->  Nested Loop  (cost=1.33..524.61 rows=2327 width=26) (actual time=66.873..14716.454 rows=6919517 loops=1)
                                             Buffers: shared hit=7481848
                                             ->  Nested Loop  (cost=0.90..221.25 rows=27 width=20) (actual time=66.830..639.720 rows=92954 loops=1)
                                                   Buffers: shared hit=278862
                                                   ->  HashAggregate  (cost=0.61..0.88 rows=27 width=4) (actual time=66.788..113.543 rows=92954 loops=1)
                                                         Group Key: cross_items_2.ss_item_sk
                                                         Batches: 1  Memory Usage: 11305kB
                                                         ->  CTE Scan on cross_items cross_items_2  (cost=0.00..0.54 rows=27 width=4) (actual time=0.002..13.181 rows=92954 loops=1)
                                                   ->  Index Scan using item_pkey on item item_2  (cost=0.29..8.16 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=92954)
                                                         Index Cond: (i_item_sk = cross_items_2.ss_item_sk)
                                                         Buffers: shared hit=278862
                                             ->  Index Scan using web_sales_pkey on web_sales  (cost=0.43..10.38 rows=86 width=18) (actual time=0.010..0.119 rows=74 loops=92954)
                                                   Index Cond: (ws_item_sk = item_2.i_item_sk)
                                                   Buffers: shared hit=7202986
                                       ->  Index Scan using date_dim_pkey on date_dim date_dim_2  (cost=0.29..0.32 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=6919517)
                                             Index Cond: (d_date_sk = web_sales.ws_sold_date_sk)
                                             Filter: ((d_year = 2001) AND (d_moy = 11))
                                             Rows Removed by Filter: 1
                                             Buffers: shared hit=20753415
 Planning:
   Buffers: shared hit=869
 Planning Time: 7.560 ms
 Execution Time: 299332.547 ms
(248 rows)

