                                                                                   QUERY PLAN                                                                                    
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=27293603.90..27293603.91 rows=1 width=72) (actual time=1082036.155..1082036.166 rows=1 loops=1)
   Buffers: shared hit=832832, temp read=6460709 written=225485
   CTE ws_wh
     ->  Hash Join  (cost=377817.50..7260666.23 rows=426765799 width=12) (actual time=4980.320..35204.893 rows=74793180 loops=1)
           Hash Cond: (ws1_1.ws_order_number = ws2.ws_order_number)
           Join Filter: (ws1_1.ws_warehouse_sk <> ws2.ws_warehouse_sk)
           Rows Removed by Join Filter: 15550502
           Buffers: shared hit=375496, temp read=24624 written=24624
           ->  Seq Scan on web_sales ws1_1  (cost=0.00..259726.89 rows=7197889 width=8) (actual time=0.004..3407.349 rows=7197566 loops=1)
                 Buffers: shared hit=187748
           ->  Hash  (cost=259726.89..259726.89 rows=7197889 width=8) (actual time=4962.178..4962.179 rows=7197566 loops=1)
                 Buckets: 8388608  Batches: 2  Memory Usage: 205975kB
                 Buffers: shared hit=187748, temp written=12311
                 ->  Seq Scan on web_sales ws2  (cost=0.00..259726.89 rows=7197889 width=8) (actual time=0.022..2734.603 rows=7197566 loops=1)
                       Buffers: shared hit=187748
   ->  Sort  (cost=20032937.67..20032937.68 rows=1 width=72) (actual time=1082036.152..1082036.159 rows=1 loops=1)
         Sort Key: (count(DISTINCT ws1.ws_order_number))
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=832832, temp read=6460709 written=225485
         ->  Aggregate  (cost=20032937.65..20032937.66 rows=1 width=72) (actual time=1082036.115..1082036.122 rows=1 loops=1)
               Buffers: shared hit=832829, temp read=6460709 written=225485
               ->  Nested Loop Semi Join  (cost=9610131.56..20032937.63 rows=2 width=16) (actual time=114167.590..1082035.598 rows=61 loops=1)
                     Join Filter: (ws1.ws_order_number = web_returns.wr_order_number)
                     Buffers: shared hit=832826, temp read=6460709 written=225485
                     ->  Nested Loop  (cost=9604736.96..9884073.17 rows=2 width=20) (actual time=113824.613..117238.910 rows=79 loops=1)
                           Buffers: shared hit=642672, temp read=24624 written=225484
                           ->  Nested Loop  (cost=9604736.54..9883798.70 rows=619 width=24) (actual time=113752.488..117117.052 rows=19506 loops=1)
                                 Join Filter: (ws1.ws_web_site_sk = web_site.web_site_sk)
                                 Rows Removed by Join Filter: 1010974
                                 Buffers: shared hit=564652, temp read=24624 written=225484
                                 ->  Hash Join  (cost=9604736.54..9883412.22 rows=3251 width=28) (actual time=113752.384..116852.758 rows=136963 loops=1)
                                       Hash Cond: (ws1.ws_order_number = ws_wh.ws_order_number)
                                       Buffers: shared hit=564649, temp read=24624 written=225484
                                       ->  Hash Join  (cost=2501.56..281124.01 rows=6502 width=24) (actual time=17.154..3041.773 rows=136963 loops=1)
                                             Hash Cond: (ws1.ws_ship_date_sk = date_dim.d_date_sk)
                                             Buffers: shared hit=189153
                                             ->  Seq Scan on web_sales ws1  (cost=0.00..259726.89 rows=7197889 width=28) (actual time=0.016..1015.133 rows=7197566 loops=1)
                                                   Buffers: shared hit=187748
                                             ->  Hash  (cost=2500.73..2500.73 rows=66 width=4) (actual time=17.047..17.048 rows=61 loops=1)
                                                   Buckets: 1024  Batches: 1  Memory Usage: 11kB
                                                   Buffers: shared hit=1405
                                                   ->  Seq Scan on date_dim  (cost=0.00..2500.73 rows=66 width=4) (actual time=8.556..17.023 rows=61 loops=1)
                                                         Filter: ((d_date >= '2002-05-01'::date) AND (d_date <= '2002-06-30 00:00:00'::timestamp without time zone))
                                                         Rows Removed by Filter: 72988
                                                         Buffers: shared hit=1405
                                       ->  Hash  (cost=9602232.48..9602232.48 rows=200 width=4) (actual time=113735.207..113735.208 rows=600000 loops=1)
                                             Buckets: 1048576 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 29286kB
                                             Buffers: shared hit=375496, temp read=24624 written=225484
                                             ->  HashAggregate  (cost=9602230.48..9602232.48 rows=200 width=4) (actual time=113370.344..113559.912 rows=600000 loops=1)
                                                   Group Key: ws_wh.ws_order_number
                                                   Batches: 1  Memory Usage: 57377kB
                                                   Buffers: shared hit=375496, temp read=24624 written=225484
                                                   ->  CTE Scan on ws_wh  (cost=0.00..8535315.98 rows=426765799 width=4) (actual time=4980.325..80993.941 rows=74793180 loops=1)
                                                         Buffers: shared hit=375496, temp read=24624 written=225484
                                 ->  Materialize  (cost=0.00..3.56 rows=8 width=4) (actual time=0.000..0.001 rows=8 loops=136963)
                                       Buffers: shared hit=3
                                       ->  Seq Scan on web_site  (cost=0.00..3.52 rows=8 width=4) (actual time=0.038..0.057 rows=8 loops=1)
                                             Filter: (web_company_name = 'pri'::bpchar)
                                             Rows Removed by Filter: 34
                                             Buffers: shared hit=3
                           ->  Index Scan using customer_address_pkey on customer_address  (cost=0.42..0.44 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=19506)
                                 Index Cond: (ca_address_sk = ws1.ws_ship_addr_sk)
                                 Filter: (ca_state = 'MA'::bpchar)
                                 Rows Removed by Filter: 1
                                 Buffers: shared hit=78020
                     ->  Hash Join  (cost=5394.60..10141113.46 rows=2491 width=8) (actual time=12212.609..12212.609 rows=1 loops=79)
                           Hash Cond: (ws_wh_1.ws_order_number = web_returns.wr_order_number)
                           Join Filter: (ws_wh.ws_order_number = ws_wh_1.ws_order_number)
                           Buffers: shared hit=190154, temp read=6436085 written=1
                           ->  CTE Scan on ws_wh ws_wh_1  (cost=0.00..8535315.98 rows=426765799 width=4) (actual time=0.011..7231.553 rows=39287770 loops=61)
                                 Buffers: temp read=6436085 written=1
                           ->  Hash  (cost=5394.57..5394.57 rows=2 width=4) (actual time=30.925..30.925 rows=1 loops=79)
                                 Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                 Buffers: shared hit=190154
                                 ->  Index Only Scan using web_returns_pkey on web_returns  (cost=0.42..5394.57 rows=2 width=4) (actual time=17.975..30.920 rows=1 loops=79)
                                       Index Cond: (wr_order_number = ws_wh.ws_order_number)
                                       Heap Fetches: 0
                                       Buffers: shared hit=190154
 Planning:
   Buffers: shared hit=279
 Planning Time: 2.847 ms
 Execution Time: 1082207.166 ms
(82 rows)

