                                                                                                      QUERY PLAN                                                                                                       
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3521659.62..3521659.87 rows=13 width=108) (actual time=260735.207..260735.500 rows=100 loops=1)
   Buffers: shared hit=52343725
   CTE cross_items
     ->  Hash Join  (cost=1768414.34..1777201.59 rows=27 width=4) (actual time=70711.314..70833.841 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.022..23.259 rows=102000 loops=1)
                 Buffers: shared hit=6964
           ->  Hash  (cost=1768235.84..1768235.84 rows=10200 width=12) (actual time=70711.218..70711.236 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=70702.501..70706.329 rows=11676 loops=1)
                       Buffers: shared hit=1103446
                       ->  HashSetOp Intersect  (cost=11773.03..1768133.84 rows=10200 width=16) (actual time=70702.498..70703.626 rows=11676 loops=1)
                             Buffers: shared hit=1103446
                             ->  Append  (cost=11773.03..1767271.35 rows=114999 width=16) (actual time=60260.575..68411.517 rows=4330904 loops=1)
                                   Buffers: shared hit=1103446
                                   ->  Result  (cost=11773.03..1474977.78 rows=10200 width=16) (actual time=60260.574..60265.208 rows=11676 loops=1)
                                         Buffers: shared hit=907329
                                         ->  HashSetOp Intersect  (cost=11773.03..1474875.78 rows=10200 width=16) (actual time=60260.569..60262.483 rows=11676 loops=1)
                                               Buffers: shared hit=907329
                                               ->  Append  (cost=11773.03..1470307.47 rows=609108 width=16) (actual time=118.231..46677.287 rows=25067754 loops=1)
                                                     Buffers: shared hit=907329
                                                     ->  Subquery Scan on "*SELECT* 1"  (cost=11773.03..897353.20 rows=400429 width=16) (actual time=118.230..27472.515 rows=16496728 loops=1)
                                                           Buffers: shared hit=525277
                                                           ->  Hash Join  (cost=11773.03..893348.91 rows=400429 width=12) (actual time=118.227..23812.504 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=20.745..15394.263 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..4330.040 rows=28800991 loops=1)
                                                                             Buffers: shared hit=516908
                                                                       ->  Hash  (cost=2500.73..2500.73 rows=1064 width=4) (actual time=20.728..20.729 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=10.052..20.426 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=97.140..97.141 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.007..62.668 rows=102000 loops=1)
                                                                             Buffers: shared hit=6964
                                                     ->  Subquery Scan on "*SELECT* 2"  (cost=11773.03..569908.73 rows=208679 width=16) (actual time=1245.024..15709.461 rows=8571026 loops=1)
                                                           Buffers: shared hit=382052
                                                           ->  Hash Join  (cost=11773.03..567821.94 rows=208679 width=12) (actual time=1245.023..13798.454 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=1150.873..9511.868 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..2254.791 rows=14401261 loops=1)
                                                                             Buffers: shared hit=373683
                                                                       ->  Hash  (cost=2500.73..2500.73 rows=1064 width=4) (actual time=18.359..18.360 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.517..18.051 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=93.633..93.634 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.013..58.836 rows=102000 loops=1)
                                                                             Buffers: shared hit=6964
                                   ->  Subquery Scan on "*SELECT* 3"  (cost=11773.03..291718.57 rows=104799 width=16) (actual time=112.175..7548.306 rows=4319228 loops=1)
                                         Buffers: shared hit=196117
                                         ->  Hash Join  (cost=11773.03..290670.58 rows=104799 width=12) (actual time=112.173..6589.580 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.346..4319.541 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.019..1146.970 rows=7197566 loops=1)
                                                           Buffers: shared hit=187748
                                                     ->  Hash  (cost=2500.73..2500.73 rows=1064 width=4) (actual time=18.304..18.305 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.489..18.002 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=93.397..93.397 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.009..58.789 rows=102000 loops=1)
                                                           Buffers: shared hit=6964
   CTE avg_sales
     ->  Aggregate  (cost=1738253.26..1738253.27 rows=1 width=32) (actual time=52773.886..52773.895 rows=1 loops=1)
           Buffers: shared hit=1082554
           ->  Append  (cost=2514.03..1732898.96 rows=713907 width=10) (actual time=18.393..34273.667 rows=29386982 loops=1)
                 Buffers: shared hit=1082554
                 ->  Hash Join  (cost=2514.03..883038.74 rows=400429 width=10) (actual time=18.390..16498.513 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..4138.535 rows=28800991 loops=1)
                             Buffers: shared hit=516908
                       ->  Hash  (cost=2500.73..2500.73 rows=1064 width=4) (actual time=18.356..18.356 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=8.918..18.067 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=1123.358..9132.084 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.009..2171.646 rows=14401261 loops=1)
                             Buffers: shared hit=373683
                       ->  Hash  (cost=2500.73..2500.73 rows=1064 width=4) (actual time=18.347..18.347 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.645..18.062 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.347..4599.939 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.011..1098.124 rows=7197566 loops=1)
                             Buffers: shared hit=187748
                       ->  Hash  (cost=2500.73..2500.73 rows=1064 width=4) (actual time=18.319..18.320 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.563..18.028 rows=1096 loops=1)
                                   Filter: ((d_year >= 1999) AND (d_year <= 2001))
                                   Rows Removed by Filter: 71953
                                   Buffers: shared hit=1405
   ->  GroupAggregate  (cost=6204.76..6205.00 rows=13 width=108) (actual time=260735.204..260735.453 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=52343725
         ->  Sort  (cost=6204.76..6204.76 rows=3 width=84) (actual time=260735.189..260735.217 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=52343725
               ->  Append  (cost=3577.26..6204.73 rows=3 width=84) (actual time=200145.497..260685.815 rows=33616 loops=1)
                     Buffers: shared hit=52343719
                     ->  GroupAggregate  (cost=3577.26..3577.40 rows=1 width=84) (actual time=200145.496..200907.906 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=30449864
                           InitPlan 3 (returns $2)
                             ->  CTE Scan on avg_sales  (cost=0.00..0.02 rows=1 width=32) (actual time=52773.891..52773.892 rows=1 loops=1)
                                   Buffers: shared hit=1082554
                           ->  Sort  (cost=3577.24..3577.25 rows=4 width=22) (actual time=147371.188..147459.358 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=29367310
                                 ->  Nested Loop  (cost=1.64..3577.20 rows=4 width=22) (actual time=70935.907..146721.987 rows=874217 loops=1)
                                       Buffers: shared hit=29367310
                                       ->  Nested Loop  (cost=1.34..2755.68 rows=9242 width=26) (actual time=70935.761..125613.547 rows=27690393 loops=1)
                                             Buffers: shared hit=29361841
                                             ->  Nested Loop  (cost=0.90..221.25 rows=27 width=20) (actual time=70935.726..71613.073 rows=92954 loops=1)
                                                   Buffers: shared hit=1389272
                                                   ->  HashAggregate  (cost=0.61..0.88 rows=27 width=4) (actual time=70935.689..70990.690 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=70711.318..70875.446 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.006..0.006 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.475 rows=298 loops=92954)
                                                   Index Cond: (ss_item_sk = item.i_item_sk)
                                                   Buffers: shared hit=27972569
                                       ->  Result Cache  (cost=0.30..0.33 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=27690393)
                                             Cache Key: store_sales.ss_sold_date_sk
                                             Hits: 27688569  Misses: 1824  Evictions: 0  Overflows: 0  Memory Usage: 123kB
                                             Buffers: shared hit=5469
                                             ->  Index Scan using date_dim_pkey on date_dim  (cost=0.29..0.32 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1824)
                                                   Index Cond: (d_date_sk = store_sales.ss_sold_date_sk)
                                                   Filter: ((d_year = 2001) AND (d_moy = 11))
                                                   Rows Removed by Filter: 1
                                                   Buffers: shared hit=5469
                     ->  GroupAggregate  (cost=1468.43..1468.50 rows=1 width=84) (actual time=38493.367..38911.628 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=14406538
                           InitPlan 4 (returns $3)
                             ->  CTE Scan on avg_sales avg_sales_1  (cost=0.00..0.02 rows=1 width=32) (actual time=0.002..0.003 rows=1 loops=1)
                           ->  Sort  (cost=1468.41..1468.41 rows=2 width=22) (actual time=38493.140..38542.310 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=14406538
                                 ->  Nested Loop  (cost=1.64..1468.40 rows=2 width=22) (actual time=68.562..38150.233 rows=462066 loops=1)
                                       Buffers: shared hit=14406538
                                       ->  Nested Loop  (cost=1.33..771.73 rows=4718 width=26) (actual time=67.084..28248.128 rows=13843675 loops=1)
                                             Buffers: shared hit=14401033
                                             ->  Nested Loop  (cost=0.90..221.25 rows=27 width=20) (actual time=67.046..691.205 rows=92954 loops=1)
                                                   Buffers: shared hit=278862
                                                   ->  HashAggregate  (cost=0.61..0.88 rows=27 width=4) (actual time=67.005..113.723 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.151 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.234 rows=149 loops=92954)
                                                   Index Cond: (cs_item_sk = item_1.i_item_sk)
                                                   Buffers: shared hit=14122171
                                       ->  Result Cache  (cost=0.30..0.33 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=13843675)
                                             Cache Key: catalog_sales.cs_sold_date_sk
                                             Hits: 13841839  Misses: 1836  Evictions: 0  Overflows: 0  Memory Usage: 123kB
                                             Buffers: shared hit=5505
                                             ->  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=1836)
                                                   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=5505
                     ->  GroupAggregate  (cost=1158.75..1158.79 rows=1 width=84) (actual time=20646.039..20861.212 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=7487317
                           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=1158.73..1158.74 rows=1 width=22) (actual time=20645.888..20669.746 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=7487317
                                 ->  Nested Loop  (cost=1.64..1158.72 rows=1 width=22) (actual time=66.845..20467.627 rows=229671 loops=1)
                                       Buffers: shared hit=7487317
                                       ->  Nested Loop  (cost=1.33..524.61 rows=2327 width=26) (actual time=66.800..15209.837 rows=6919517 loops=1)
                                             Buffers: shared hit=7481848
                                             ->  Nested Loop  (cost=0.90..221.25 rows=27 width=20) (actual time=66.761..645.249 rows=92954 loops=1)
                                                   Buffers: shared hit=278862
                                                   ->  HashAggregate  (cost=0.61..0.88 rows=27 width=4) (actual time=66.722..109.328 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.226 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.126 rows=74 loops=92954)
                                                   Index Cond: (ws_item_sk = item_2.i_item_sk)
                                                   Buffers: shared hit=7202986
                                       ->  Result Cache  (cost=0.30..0.33 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=6919517)
                                             Cache Key: web_sales.ws_sold_date_sk
                                             Hits: 6917693  Misses: 1824  Evictions: 0  Overflows: 0  Memory Usage: 123kB
                                             Buffers: shared hit=5469
                                             ->  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=1824)
                                                   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=5469
 Planning:
   Buffers: shared hit=869
 Planning Time: 8.104 ms
 Execution Time: 260745.357 ms
(257 rows)

