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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance