Execution plan analysis

Dear all

Someone help me analyze the execution plans below, is the query 12 of
TPC-H benchmark [1].
I need to find out why the query without index runs faster (7 times)
than with index, although the costs are smaller (see table).
I have other cases that happened in the same situation. The server
parameters have been set with PGTUNE. I use postgresql version 9.6.4
on Debian 8 OS with 4 GB memory.

Query|Index(yes/no) |Time Spend |Cost Total
===================================
12 Yes 00:08:58 2710805.51
12 No 00:01:42 3365996.34

----------------- Explain Analyze Query 12 WITH INDEX
----------------------------
Sort (cost=2710805.51..2710805.51 rows=1 width=27) (actual
time=537713.672..537713.672 rows=2 loops=1)
Sort Key: lineitem.l_shipmode
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=2710805.47..2710805.50 rows=1 width=27)
(actual time=537713.597..537713.598 rows=2 loops=1)
-> Merge Join (cost=1994471.69..2708777.28 rows=270426
width=27) (actual time=510717.977..536818.802 rows=311208 loops=1)
Merge Cond: (orders.o_orderkey = lineitem.l_orderkey)
-> Index Scan using orders_pkey on orders
(cost=0.00..672772.57 rows=15000045 width=20) (actual
time=0.019..20898.325 rows=14999972 loops=1)
-> Sort (cost=1994455.40..1995131.47
rows=270426 width=19) (actual time=510690.114..510915.678 rows=311208
loops=1)
Sort Key: lineitem.l_orderkey
Sort Method: external sort Disk: 11568kB
-> Bitmap Heap Scan on
lineitem (cost=336295.10..1970056.39 rows=270426 width=19) (actual
time=419620.817..509685.421 rows=311208 loops=1)
Recheck Cond:
(l_shipmode = ANY (_{TRUCK,AIR}_::bpchar[]))
Filter:
((l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND
(l_receiptdate >= _1997-01-01_::date) AND (l_receiptdate < _1998-01-01
00:00:00_::timestamp without time zone))
-> Bitmap
Index Scan on idx_l_shipmodelineitem000 (cost=0.00..336227.49
rows=15942635 width=0) (actual time=419437.172..419437.172
rows=17133713 loops=1)
Index
Cond: (l_shipmode = ANY (_{TRUCK,AIR}_::bpchar[]))

Total runtime: 537728.848 ms

----------------- Explain Analyze Query 12 WITHOUT INDEX
----------------------------
Sort (cost=3365996.33..3365996.34 rows=1 width=27) (actual
time=101850.883..101850.884 rows=2 loops=1)
Sort Key: lineitem.l_shipmode Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=3365996.30..3365996.32 rows=1 width=27)
(actual time=101850.798..101850.800 rows=2 loops=1)
-> Merge Join (cost=2649608.28..3363936.68 rows=274616
width=27) (actual time=75497.181..100938.830 rows=311208 loops=1)
Merge Cond: (orders.o_orderkey = lineitem.l_orderkey)
-> Index Scan using orders_pkey on orders
(cost=0.00..672771.90 rows=15000000 width=20) (actual
time=0.020..20272.828 rows=14999972 loops=1)
-> Sort (cost=2649545.68..2650232.22
rows=274616 width=19) (actual time=75364.450..75618.772 rows=311208
loops=1)
Sort Key: lineitem.l_orderkey
Sort Method: external sort
Disk: 11568kB
-> Seq Scan on lineitem
(cost=0.00..2624738.17 rows=274616 width=19) (actual
time=0.839..74391.087 rows=311208 loops=1)
Filter: ((l_shipmode
= ANY (_{TRUCK,AIR}_::bpchar[])) AND (l_commitdate < l_receiptdate)
AND (l_shipdate < l_commitdate) AND (l_receiptdate >=
_1997-01-01_::date) AND (l_receiptdate < _1998-01-01
00:00:00_::timestamp without time zone))
Total runtime:
101865.253 ms

-=========------ SQL query 12 ----------------------
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 ('TRUCK', 'AIR')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= date '1997-01-01'
and l_receiptdate < date '1997-01-01' + interval '1' year
group by
l_shipmode
order by
l_shipmode