MIME-Version: 1.0
From: Neto pr <netopr9@gmail.com>
Date: Fri, 25 Aug 2017 05:31:44 -0300
Message-ID: 
 <CA+wPC0MRMhF_8fD9dc8+QWZQzUvHahPRSv=xMtCmsVLSsy-p0w@mail.gmail.com>
Subject: Execution plan analysis
To: pgsql-performance@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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