EXPLAIN select l_shipmode, sum(case when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1 else 0 end) as high_line_count, sum(case when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH' then 1 else 0 end) as low_line_count from orders, lineitem where o_orderkey = l_orderkey and l_shipmode in ('MAIL', 'FOB') and l_commitdate < l_receiptdate and l_shipdate < l_commitdate and l_receiptdate >= date '1994-01-01' and l_receiptdate < date '1994-01-01' + interval '1 year' group by l_shipmode order by l_shipmode;
                                                                             QUERY PLAN                                                                             
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=2178627.75..2178627.75 rows=1 width=27)
   Sort Key: lineitem.l_shipmode
   ->  HashAggregate  (cost=2178627.73..2178627.74 rows=1 width=27)
         Group Key: lineitem.l_shipmode
         ->  Merge Join  (cost=1488303.72..2173565.38 rows=289277 width=27)
               Merge Cond: (orders.o_orderkey = lineitem.l_orderkey)
               ->  Index Scan using orders_pkey on orders  (cost=0.43..642799.26 rows=14999855 width=20)
               ->  Materialize  (cost=1488303.14..1489749.53 rows=289277 width=15)
                     ->  Sort  (cost=1488303.14..1489026.33 rows=289277 width=15)
                           Sort Key: lineitem.l_orderkey
                           ->  Bitmap Heap Scan on lineitem  (cost=188989.59..1457117.19 rows=289277 width=15)
                                 Recheck Cond: ((l_receiptdate >= '1994-01-01'::date) AND (l_receiptdate < '1995-01-01 00:00:00'::timestamp without time zone))
                                 Filter: ((l_shipmode = ANY ('{MAIL,FOB}'::bpchar[])) AND (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate))
                                 ->  Bitmap Index Scan on i_l_receiptdate  (cost=0.00..188917.28 rows=9009671 width=0)
                                       Index Cond: ((l_receiptdate >= '1994-01-01'::date) AND (l_receiptdate < '1995-01-01 00:00:00'::timestamp without time zone))
(15 rows)