explain select * from(select w_warehouse_name ,i_item_id ,sum(case when (cast(d_date as date) < cast ('1999-04-27' as date)) then inv_quantity_on_hand else 0 end) as inv_before ,sum(case when (cast(d_date as date) >= cast ('1999-04-27' as date)) then inv_quantity_on_hand else 0 end) as inv_after from inventory ,warehouse ,item ,date_dim where i_current_price between 0.99 and 1.49 and i_item_sk = inv_item_sk and inv_warehouse_sk = w_warehouse_sk and inv_date_sk = d_date_sk and d_date between (cast ('1999-04-27' as date) - interval '30 days') and (cast ('1999-04-27' as date) + interval '30 days') group by w_warehouse_name, i_item_id) x where (case when inv_before > 0 then inv_after / inv_before else null end) between 2.0/3.0 and 3.0/2.0 order by w_warehouse_name ,i_item_id limit 100; -- master QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=159172.71..159172.96 rows=100 width=51) (actual time=2847.720..2847.951 rows=100 loops=1) -> Sort (cost=159172.71..159174.17 rows=583 width=51) (actual time=2847.718..2847.799 rows=100 loops=1) Sort Key: warehouse.w_warehouse_name, item.i_item_id Sort Method: top-N heapsort Memory: 39kB -> HashAggregate (cost=159127.11..159144.60 rows=583 width=43) (actual time=2843.583..2846.122 rows=1496 loops=1) Group Key: warehouse.w_warehouse_name, item.i_item_id Filter: (((CASE WHEN (sum(CASE WHEN (date_dim.d_date < '1999-04-27'::date) THEN inventory.inv_quantity_on_hand ELSE 0 END) > 0) THEN (sum(CASE WHEN (date_dim.d_date >= '1999-04-27'::date) THEN inv entory.inv_quantity_on_hand ELSE 0 END) / sum(CASE WHEN (date_dim.d_date < '1999-04-27'::date) THEN inventory.inv_quantity_on_hand ELSE 0 END)) ELSE NULL::bigint END)::numeric >= 0.66666666666666666667) AND ((CA SE WHEN (sum(CASE WHEN (date_dim.d_date < '1999-04-27'::date) THEN inventory.inv_quantity_on_hand ELSE 0 END) > 0) THEN (sum(CASE WHEN (date_dim.d_date >= '1999-04-27'::date) THEN inventory.inv_quantity_on_hand ELSE 0 END) / sum(CASE WHEN (date_dim.d_date < '1999-04-27'::date) THEN inventory.inv_quantity_on_hand ELSE 0 END)) ELSE NULL::bigint END)::numeric <= 1.5000000000000000)) Rows Removed by Filter: 1254 -> Nested Loop (cost=0.72..159100.87 rows=583 width=43) (actual time=3.182..2810.884 rows=24750 loops=1) Join Filter: (inventory.inv_warehouse_sk = warehouse.w_warehouse_sk) Rows Removed by Join Filter: 99000 -> Nested Loop (cost=0.72..159056.09 rows=583 width=29) (actual time=3.174..2530.719 rows=24750 loops=1) -> Nested Loop (cost=0.43..155964.84 rows=9647 width=16) (actual time=3.138..1029.837 rows=405000 loops=1) -> Seq Scan on date_dim (cost=0.00..2500.73 rows=60 width=8) (actual time=3.107..6.889 rows=61 loops=1) Filter: ((d_date >= '1999-03-28 00:00:00'::timestamp without time zone) AND (d_date <= '1999-05-27 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 72988 -> Index Scan using idx_inv_date_sk on inventory (cost=0.43..2107.74 rows=45000 width=16) (actual time=0.003..6.035 rows=6639 loops=61) Index Cond: (inv_date_sk = date_dim.d_date_sk) -> Index Scan using item_pkey on item (cost=0.29..0.31 rows=1 width=21) (actual time=0.002..0.002 rows=0 loops=405000) Index Cond: (i_item_sk = inventory.inv_item_sk) Filter: ((i_current_price >= 0.99) AND (i_current_price <= 1.49)) Rows Removed by Filter: 1 -> Materialize (cost=0.00..1.07 rows=5 width=22) (actual time=0.001..0.005 rows=5 loops=24750) -> Seq Scan on warehouse (cost=0.00..1.05 rows=5 width=22) (actual time=0.001..0.006 rows=5 loops=1) Planning time: 0.652 ms Execution time: 2848.154 ms (26 rows) Actual Time: 560.585 ms -- patched QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=157601.41..157601.66 rows=100 width=51) (actual time=2219.284..2219.512 rows=100 loops=1) -> Sort (cost=157601.41..157602.86 rows=583 width=51) (actual time=2219.278..2219.355 rows=100 loops=1) Sort Key: warehouse.w_warehouse_name, item.i_item_id Sort Method: top-N heapsort Memory: 39kB -> HashAggregate (cost=157555.80..157573.29 rows=583 width=43) (actual time=2215.056..2217.581 rows=1496 loops=1) Group Key: warehouse.w_warehouse_name, item.i_item_id Filter: (((CASE WHEN (sum(CASE WHEN (date_dim.d_date < '1999-04-27'::date) THEN inventory.inv_quantity_on_hand ELSE 0 END) > 0) THEN (sum(CASE WHEN (date_dim.d_date >= '1999-04-27'::date) THEN inventory.inv_quantity_on_hand ELSE 0 END) / sum(CASE WHEN (date_dim.d_date < '1999-04-27'::date) THEN inventory.inv_quantity_on_hand ELSE 0 END)) ELSE NULL::bigint END)::numeric >= 0.66666666666666666667) AND ((CASE WHEN (sum(CASE WHEN (date_dim.d_date < '1999-04-27'::date) THEN inventory.inv_quantity_on_hand ELSE 0 END) > 0) THEN (sum(CASE WHEN (date_dim.d_date >= '1999-04-27'::date) THEN inventory.inv_quantity_on_hand ELSE 0 END) / sum(CASE WHEN (date_dim.d_date < '1999-04-27'::date) THEN inventory.inv_quantity_on_hand ELSE 0 END)) ELSE NULL::bigint END)::numeric <= 1.5000000000000000)) Rows Removed by Filter: 1254 -> Hash Join (cost=1515.15..157529.57 rows=583 width=43) (actual time=652.882..2181.656 rows=24750 loops=1) Hash Cond: (inventory.inv_warehouse_sk = warehouse.w_warehouse_sk) -> Hash Join (cost=1514.03..157520.44 rows=583 width=29) (actual time=652.795..2137.072 rows=24750 loops=1) Hash Cond: (inventory.inv_item_sk = item.i_item_sk) -> Nested Loop (cost=0.43..155964.84 rows=9647 width=16) (actual time=334.137..1444.801 rows=405000 loops=1) -> Seq Scan on date_dim (cost=0.00..2500.73 rows=60 width=8) (actual time=12.499..16.253 rows=61 loops=1) Filter: ((d_date >= '1999-03-28 00:00:00'::timestamp without time zone) AND (d_date <= '1999-05-27 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 72988 -> Index Scan using idx_inv_date_sk on inventory (cost=0.43..2107.74 rows=45000 width=16) (actual time=5.274..12.186 rows=6639 loops=61) Index Cond: (inv_date_sk = date_dim.d_date_sk) -> Hash (cost=1500.00..1500.00 rows=1088 width=21) (actual time=318.616..318.616 rows=1104 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 77kB -> Seq Scan on item (cost=0.00..1500.00 rows=1088 width=21) (actual time=0.036..313.870 rows=1104 loops=1) Filter: ((i_current_price >= 0.99) AND (i_current_price <= 1.49)) Rows Removed by Filter: 16896 -> Hash (cost=1.05..1.05 rows=5 width=22) (actual time=0.067..0.067 rows=5 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on warehouse (cost=0.00..1.05 rows=5 width=22) (actual time=0.011..0.034 rows=5 loops=1) Planning time: 2.173 ms Execution time: 2219.887 ms (28 rows) Actual Time: 122.072 ms