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=2180903.31..2180903.31 rows=1 width=27)
   Sort Key: lineitem.l_shipmode
   ->  HashAggregate  (cost=2180903.29..2180903.30 rows=1 width=27)
         Group Key: lineitem.l_shipmode
         ->  Merge Join  (cost=1489725.83..2175835.76 rows=289573 width=27)
               Merge Cond: (orders.o_orderkey = lineitem.l_orderkey)
               ->  Index Scan using orders_pkey on orders  (cost=0.43..643605.01 rows=15014838 width=20)
               ->  Materialize  (cost=1489725.25..1491173.12 rows=289573 width=15)
                     ->  Sort  (cost=1489725.25..1490449.19 rows=289573 width=15)
                           Sort Key: lineitem.l_orderkey
                           ->  Bitmap Heap Scan on lineitem  (cost=189081.84..1458506.82 rows=289573 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..189009.45 rows=9018888 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)