                                                                                                                                                                                                                                                                                           QUERY PLAN                                                                                                                                                                                                                                                                                            
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=217386.34..217386.51 rows=68 width=68) (actual time=9428.947..9428.973 rows=100 loops=1)
   Buffers: shared hit=1240195
   ->  Sort  (cost=217386.34..217386.51 rows=68 width=68) (actual time=9428.945..9428.960 rows=100 loops=1)
         Sort Key: tmp1.i_manager_id, tmp1.avg_monthly_sales, tmp1.sum_sales
         Sort Method: top-N heapsort  Memory: 32kB
         Buffers: shared hit=1240195
         ->  Subquery Scan on tmp1  (cost=217372.09..217384.27 rows=68 width=68) (actual time=9258.129..9428.313 rows=1186 loops=1)
               Filter: (CASE WHEN (tmp1.avg_monthly_sales > '0'::numeric) THEN (abs((tmp1.sum_sales - tmp1.avg_monthly_sales)) / tmp1.avg_monthly_sales) ELSE NULL::numeric END > 0.1)
               Rows Removed by Filter: 14
               Buffers: shared hit=1240189
               ->  WindowAgg  (cost=217372.09..217379.70 rows=203 width=72) (actual time=9258.122..9426.508 rows=1200 loops=1)
                     Buffers: shared hit=1240189
                     ->  GroupAggregate  (cost=217372.09..217376.66 rows=203 width=40) (actual time=9255.272..9424.949 rows=1200 loops=1)
                           Group Key: item.i_manager_id, store_sales.ss_promo_sk
                           Buffers: shared hit=1240189
                           ->  Sort  (cost=217372.09..217372.60 rows=203 width=14) (actual time=9255.047..9290.462 rows=373031 loops=1)
                                 Sort Key: item.i_manager_id, store_sales.ss_promo_sk
                                 Sort Method: quicksort  Memory: 29774kB
                                 Buffers: shared hit=1240189
                                 ->  Nested Loop  (cost=7.42..217364.31 rows=203 width=14) (actual time=2.126..9018.716 rows=373031 loops=1)
                                       Join Filter: (store_sales.ss_store_sk = store.s_store_sk)
                                       Rows Removed by Join Filter: 19314861
                                       Buffers: shared hit=1240189
                                       ->  Nested Loop  (cost=7.42..217036.67 rows=212 width=18) (actual time=2.063..4728.736 rows=382022 loops=1)
                                             Buffers: shared hit=1240184
                                             ->  Nested Loop  (cost=7.09..215276.12 rows=44613 width=18) (actual time=0.114..3745.066 rows=1211710 loops=1)
                                                   Buffers: shared hit=1234715
                                                   ->  Seq Scan on item  (cost=0.00..10789.00 rows=158 width=8) (actual time=0.037..228.029 rows=5279 loops=1)
                                                         Filter: (((i_category = ANY ('{Books,Children,Electronics}'::bpchar[])) AND (i_class = ANY ('{personal,portable,reference,self-help}'::bpchar[])) AND (i_brand = ANY ('{"scholaramalgamalg #14","scholaramalgamalg #7","exportiunivamalg #9","scholaramalgamalg #9"}'::bpchar[]))) OR ((i_category = ANY ('{Women,Music,Men}'::bpchar[])) AND (i_class = ANY ('{accessories,classical,fragrances,pants}'::bpchar[])) AND (i_brand = ANY ('{"amalgimporto #1","edu packscholar #1","exportiimporto #1","importoamalg #1"}'::bpchar[]))))
                                                         Rows Removed by Filter: 96721
                                                         Buffers: shared hit=6964
                                                   ->  Bitmap Heap Scan on store_sales  (cost=7.09..1290.80 rows=342 width=18) (actual time=0.094..0.579 rows=230 loops=5279)
                                                         Recheck Cond: (ss_item_sk = item.i_item_sk)
                                                         Heap Blocks: exact=1211464
                                                         Buffers: shared hit=1227751
                                                         ->  Bitmap Index Scan on store_sales_ss_item_sk_idx  (cost=0.00..7.00 rows=342 width=0) (actual time=0.046..0.046 rows=230 loops=5279)
                                                               Index Cond: (ss_item_sk = item.i_item_sk)
                                                               Buffers: shared hit=16287
                                             ->  Result Cache  (cost=0.33..0.36 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1211710)
                                                   Cache Key: store_sales.ss_sold_date_sk
                                                   Hits: 1209886  Misses: 1824  Evictions: 0  Overflows: 0  Memory Usage: 136kB
                                                   Buffers: shared hit=5469
                                                   ->  Index Scan using date_dim_pkey on date_dim  (cost=0.32..0.35 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1824)
                                                         Index Cond: (d_date_sk = store_sales.ss_sold_date_sk)
                                                         Filter: (d_month_seq = ANY ('{1181,1182,1183,1184,1185,1186,1187,1188,1189,1190,1191,1192}'::integer[]))
                                                         Rows Removed by Filter: 1
                                                         Buffers: shared hit=5469
                                       ->  Materialize  (cost=0.00..6.53 rows=102 width=4) (actual time=0.000..0.004 rows=52 loops=382022)
                                             Buffers: shared hit=5
                                             ->  Seq Scan on store  (cost=0.00..6.02 rows=102 width=4) (actual time=0.004..0.047 rows=102 loops=1)
                                                   Buffers: shared hit=5
 Planning:
   Buffers: shared hit=431
 Planning Time: 2.597 ms
 Execution Time: 9431.062 ms
(55 rows)

