                                                                                                     QUERY PLAN                                                                                                      
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1773006.46..1773006.49 rows=13 width=126) (actual time=83011.457..83011.469 rows=8 loops=1)
   Buffers: shared hit=2070561
   ->  Sort  (cost=1773006.46..1773006.49 rows=13 width=126) (actual time=83011.455..83011.465 rows=8 loops=1)
         Sort Key: ((GROUPING(store.s_state) + GROUPING(store.s_county))) DESC, (CASE WHEN (((GROUPING(store.s_state) + GROUPING(store.s_county))) = 0) THEN store.s_state ELSE NULL::bpchar END), (rank() OVER (?))
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=2070561
         ->  WindowAgg  (cost=1773005.79..1773006.21 rows=13 width=126) (actual time=83011.386..83011.411 rows=8 loops=1)
               Buffers: shared hit=2070552
               ->  Sort  (cost=1773005.79..1773005.82 rows=13 width=94) (actual time=83011.374..83011.384 rows=8 loops=1)
                     Sort Key: ((GROUPING(store.s_state) + GROUPING(store.s_county))), (CASE WHEN (GROUPING(store.s_county) = 0) THEN store.s_state ELSE NULL::bpchar END), (sum(store_sales.ss_net_profit)) DESC
                     Sort Method: quicksort  Memory: 25kB
                     Buffers: shared hit=2070552
                     ->  MixedAggregate  (cost=886602.77..1773005.55 rows=13 width=94) (actual time=83011.329..83011.347 rows=8 loops=1)
                           Hash Key: store.s_state, store.s_county
                           Hash Key: store.s_state
                           Group Key: ()
                           Batches: 1  Memory Usage: 32kB
                           Buffers: shared hit=2070549
                           ->  Nested Loop  (cost=886602.77..1772330.80 rows=44968 width=24) (actual time=19125.077..75571.834 rows=5382031 loops=1)
                                 Join Filter: (store.s_state = tmp1.s_state)
                                 Rows Removed by Join Filter: 10764062
                                 Buffers: shared hit=2070549
                                 ->  Subquery Scan on tmp1  (cost=884097.19..884097.29 rows=1 width=3) (actual time=19108.369..19108.407 rows=3 loops=1)
                                       Filter: (tmp1.ranking <= 5)
                                       Buffers: shared hit=518318
                                       ->  WindowAgg  (cost=884097.19..884097.25 rows=3 width=43) (actual time=19108.366..19108.400 rows=3 loops=1)
                                             Buffers: shared hit=518318
                                             ->  Sort  (cost=884097.19..884097.20 rows=3 width=35) (actual time=19108.348..19108.357 rows=3 loops=1)
                                                   Sort Key: store_1.s_state, (sum(store_sales_1.ss_net_profit)) DESC
                                                   Sort Method: quicksort  Memory: 25kB
                                                   Buffers: shared hit=518318
                                                   ->  HashAggregate  (cost=884097.13..884097.17 rows=3 width=35) (actual time=19108.331..19108.338 rows=3 loops=1)
                                                         Group Key: store_1.s_state
                                                         Batches: 1  Memory Usage: 24kB
                                                         Buffers: shared hit=518318
                                                         ->  Hash Join  (cost=2512.72..883422.61 rows=134905 width=9) (actual time=18.278..15504.363 rows=5382031 loops=1)
                                                               Hash Cond: (store_sales_1.ss_store_sk = store_1.s_store_sk)
                                                               Buffers: shared hit=518318
                                                               ->  Hash Join  (cost=2505.42..883030.13 rows=141129 width=10) (actual time=18.137..13205.526 rows=5512339 loops=1)
                                                                     Hash Cond: (store_sales_1.ss_sold_date_sk = date_dim.d_date_sk)
                                                                     Buffers: shared hit=518313
                                                                     ->  Seq Scan on store_sales store_sales_1  (cost=0.00..804917.92 rows=28800992 width=14) (actual time=0.005..3946.167 rows=28800991 loops=1)
                                                                           Buffers: shared hit=516908
                                                                     ->  Hash  (cost=2500.73..2500.73 rows=375 width=4) (actual time=18.076..18.077 rows=365 loops=1)
                                                                           Buckets: 1024  Batches: 1  Memory Usage: 21kB
                                                                           Buffers: shared hit=1405
                                                                           ->  Seq Scan on date_dim  (cost=0.00..2500.73 rows=375 width=4) (actual time=9.155..17.975 rows=365 loops=1)
                                                                                 Filter: ((d_month_seq >= 1180) AND (d_month_seq <= 1191))
                                                                                 Rows Removed by Filter: 72684
                                                                                 Buffers: shared hit=1405
                                                               ->  Hash  (cost=6.02..6.02 rows=102 width=7) (actual time=0.129..0.130 rows=102 loops=1)
                                                                     Buckets: 1024  Batches: 1  Memory Usage: 12kB
                                                                     Buffers: shared hit=5
                                                                     ->  Seq Scan on store store_1  (cost=0.00..6.02 rows=102 width=7) (actual time=0.008..0.093 rows=102 loops=1)
                                                                           Buffers: shared hit=5
                                 ->  Nested Loop  (cost=2505.57..886547.20 rows=134905 width=24) (actual time=5.603..17622.691 rows=5382031 loops=3)
                                       Buffers: shared hit=1552231
                                       ->  Hash Join  (cost=2505.42..883030.13 rows=141129 width=10) (actual time=5.591..13124.124 rows=5512339 loops=3)
                                             Hash Cond: (store_sales.ss_sold_date_sk = d1.d_date_sk)
                                             Buffers: shared hit=1552129
                                             ->  Seq Scan on store_sales  (cost=0.00..804917.92 rows=28800992 width=14) (actual time=0.005..3910.457 rows=28800991 loops=3)
                                                   Buffers: shared hit=1550724
                                             ->  Hash  (cost=2500.73..2500.73 rows=375 width=4) (actual time=16.586..16.586 rows=365 loops=1)
                                                   Buckets: 1024  Batches: 1  Memory Usage: 21kB
                                                   Buffers: shared hit=1405
                                                   ->  Seq Scan on date_dim d1  (cost=0.00..2500.73 rows=375 width=4) (actual time=7.768..16.469 rows=365 loops=1)
                                                         Filter: ((d_month_seq >= 1180) AND (d_month_seq <= 1191))
                                                         Rows Removed by Filter: 72684
                                                         Buffers: shared hit=1405
                                       ->  Result Cache  (cost=0.15..0.17 rows=1 width=22) (actual time=0.000..0.000 rows=1 loops=16537017)
                                             Cache Key: store_sales.ss_store_sk
                                             Hits: 16536965  Misses: 52  Evictions: 0  Overflows: 0  Memory Usage: 7kB
                                             Buffers: shared hit=102
                                             ->  Index Scan using store_pkey on store  (cost=0.14..0.16 rows=1 width=22) (actual time=0.003..0.003 rows=1 loops=52)
                                                   Index Cond: (s_store_sk = store_sales.ss_store_sk)
                                                   Buffers: shared hit=102
 Planning:
   Buffers: shared hit=405
 Planning Time: 2.677 ms
 Execution Time: 83018.940 ms
(80 rows)

