                                                                                                     QUERY PLAN                                                                                                      
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1769881.86..1769881.89 rows=13 width=126) (actual time=77418.412..77418.424 rows=8 loops=1)
   Buffers: shared hit=2070464
   ->  Sort  (cost=1769881.86..1769881.89 rows=13 width=126) (actual time=77418.409..77418.420 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=2070464
         ->  WindowAgg  (cost=1769881.20..1769881.62 rows=13 width=126) (actual time=77418.339..77418.364 rows=8 loops=1)
               Buffers: shared hit=2070455
               ->  Sort  (cost=1769881.20..1769881.23 rows=13 width=94) (actual time=77418.326..77418.337 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=2070455
                     ->  MixedAggregate  (cost=886609.91..1769880.96 rows=13 width=94) (actual time=77418.276..77418.295 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=2070452
                           ->  Nested Loop  (cost=886609.91..1769206.21 rows=44968 width=24) (actual time=19312.613..70153.486 rows=5382031 loops=1)
                                 Join Filter: (store.s_state = tmp1.s_state)
                                 Rows Removed by Join Filter: 10764062
                                 Buffers: shared hit=2070452
                                 ->  Subquery Scan on tmp1  (cost=884097.19..884097.29 rows=1 width=3) (actual time=19295.747..19295.800 rows=3 loops=1)
                                       Filter: (tmp1.ranking <= 5)
                                       Buffers: shared hit=518318
                                       ->  WindowAgg  (cost=884097.19..884097.25 rows=3 width=43) (actual time=19295.744..19295.793 rows=3 loops=1)
                                             Buffers: shared hit=518318
                                             ->  Sort  (cost=884097.19..884097.20 rows=3 width=35) (actual time=19295.727..19295.735 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=19295.708..19295.715 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.036..15740.234 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=17.881..13438.353 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.006..3957.646 rows=28800991 loops=1)
                                                                           Buffers: shared hit=516908
                                                                     ->  Hash  (cost=2500.73..2500.73 rows=375 width=4) (actual time=17.816..17.818 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=8.890..17.713 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.141..0.141 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.009..0.103 rows=102 loops=1)
                                                                           Buffers: shared hit=5
                                 ->  Hash Join  (cost=2512.72..883422.61 rows=134905 width=24) (actual time=5.657..15769.683 rows=5382031 loops=3)
                                       Hash Cond: (store_sales.ss_store_sk = store.s_store_sk)
                                       Buffers: shared hit=1552134
                                       ->  Hash Join  (cost=2505.42..883030.13 rows=141129 width=10) (actual time=5.599..13427.984 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.003..3874.110 rows=28800991 loops=3)
                                                   Buffers: shared hit=1550724
                                             ->  Hash  (cost=2500.73..2500.73 rows=375 width=4) (actual time=16.613..16.614 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.808..16.507 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=22) (actual time=0.137..0.138 rows=102 loops=1)
                                             Buckets: 1024  Batches: 1  Memory Usage: 14kB
                                             Buffers: shared hit=5
                                             ->  Seq Scan on store  (cost=0.00..6.02 rows=102 width=22) (actual time=0.018..0.090 rows=102 loops=1)
                                                   Buffers: shared hit=5
 Planning:
   Buffers: shared hit=405
 Planning Time: 2.667 ms
 Execution Time: 77418.854 ms
(79 rows)

