                                                                                                                                                                                                                                                                                           QUERY PLAN                                                                                                                                                                                                                                                                                            
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=217928.45..217928.62 rows=68 width=68) (actual time=4497.587..4497.614 rows=100 loops=1)
   Buffers: shared hit=1236131
   ->  Sort  (cost=217928.45..217928.62 rows=68 width=68) (actual time=4497.585..4497.600 rows=100 loops=1)
         Sort Key: tmp1.avg_quarterly_sales, tmp1.sum_sales, tmp1.i_manufact_id
         Sort Method: top-N heapsort  Memory: 37kB
         Buffers: shared hit=1236131
         ->  Subquery Scan on tmp1  (cost=217914.20..217926.38 rows=68 width=68) (actual time=4357.934..4495.013 rows=3045 loops=1)
               Filter: (CASE WHEN (tmp1.avg_quarterly_sales > '0'::numeric) THEN (abs((tmp1.sum_sales - tmp1.avg_quarterly_sales)) / tmp1.avg_quarterly_sales) ELSE NULL::numeric END > 0.1)
               Rows Removed by Filter: 222
               Buffers: shared hit=1236125
               ->  WindowAgg  (cost=217914.20..217921.81 rows=203 width=72) (actual time=4357.927..4490.914 rows=3267 loops=1)
                     Buffers: shared hit=1236125
                     ->  GroupAggregate  (cost=217914.20..217918.77 rows=203 width=40) (actual time=4357.661..4485.756 rows=3267 loops=1)
                           Group Key: item.i_manufact_id, date_dim.d_qoy
                           Buffers: shared hit=1236125
                           ->  Sort  (cost=217914.20..217914.71 rows=203 width=14) (actual time=4357.601..4380.763 rows=276504 loops=1)
                                 Sort Key: item.i_manufact_id, date_dim.d_qoy
                                 Sort Method: quicksort  Memory: 25250kB
                                 Buffers: shared hit=1236125
                                 ->  Hash Join  (cost=2519.69..217906.42 rows=203 width=14) (actual time=22.980..4193.997 rows=276504 loops=1)
                                       Hash Cond: (store_sales.ss_store_sk = store.s_store_sk)
                                       Buffers: shared hit=1236125
                                       ->  Hash Join  (cost=2512.39..217898.54 rows=212 width=18) (actual time=22.875..4072.210 rows=283162 loops=1)
                                             Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk)
                                             Buffers: shared hit=1236120
                                             ->  Nested Loop  (cost=7.09..215276.12 rows=44613 width=18) (actual time=0.119..3701.367 rows=1211710 loops=1)
                                                   Buffers: shared hit=1234715
                                                   ->  Seq Scan on item  (cost=0.00..10789.00 rows=158 width=8) (actual time=0.036..223.657 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.093..0.573 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
                                             ->  Hash  (cost=2500.76..2500.76 rows=363 width=8) (actual time=21.384..21.385 rows=366 loops=1)
                                                   Buckets: 1024  Batches: 1  Memory Usage: 23kB
                                                   Buffers: shared hit=1405
                                                   ->  Seq Scan on date_dim  (cost=0.03..2500.76 rows=363 width=8) (actual time=11.152..21.267 rows=366 loops=1)
                                                         Filter: (d_month_seq = ANY ('{1197,1198,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208}'::integer[]))
                                                         Rows Removed by Filter: 72683
                                                         Buffers: shared hit=1405
                                       ->  Hash  (cost=6.02..6.02 rows=102 width=4) (actual time=0.090..0.091 rows=102 loops=1)
                                             Buckets: 1024  Batches: 1  Memory Usage: 12kB
                                             Buffers: shared hit=5
                                             ->  Seq Scan on store  (cost=0.00..6.02 rows=102 width=4) (actual time=0.008..0.056 rows=102 loops=1)
                                                   Buffers: shared hit=5
 Planning:
   Buffers: shared hit=431
 Planning Time: 2.858 ms
 Execution Time: 4499.627 ms
(54 rows)

