MIME-Version: 1.0
In-Reply-To: 
 <CA+wPC0MRMhF_8fD9dc8+QWZQzUvHahPRSv=xMtCmsVLSsy-p0w@mail.gmail.com>
References: 
 <CA+wPC0MRMhF_8fD9dc8+QWZQzUvHahPRSv=xMtCmsVLSsy-p0w@mail.gmail.com>
From: Neto pr <netopr9@gmail.com>
Date: Fri, 25 Aug 2017 10:06:40 -0300
Message-ID: 
 <CA+wPC0NVN=-sg6Ts9o+2ZvJRAOEE5G37PPcGUJUhoR2rVbSQQw@mail.gmail.com>
Subject: Re: Execution plan analysis
To: pgsql-performance@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

2017-08-25 5:31 GMT-03:00 Neto pr <netopr9@gmail.com>:
> 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
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> 12       Yes               00:08:58          2710805.51
> 12        No                00:01:42         3365996.34
>
>
> -----------------   Explain Analyze  Query 12  WITH INDEX
> ----------------------------
> Sort  (cost=3D2710805.51..2710805.51 rows=3D1 width=3D27) (actual
> time=3D537713.672..537713.672 rows=3D2 loops=3D1)
>   Sort Key: lineitem.l_shipmode
>     Sort Method:  quicksort  Memory: 25kB
>       ->  HashAggregate  (cost=3D2710805.47..2710805.50 rows=3D1 width=3D=
27)
> (actual time=3D537713.597..537713.598 rows=3D2 loops=3D1)
>               ->  Merge Join  (cost=3D1994471.69..2708777.28 rows=3D270426
> width=3D27) (actual time=3D510717.977..536818.802 rows=3D311208 loops=3D1)
>                   Merge Cond: (orders.o_orderkey =3D lineitem.l_orderkey)
>                     ->  Index Scan using orders_pkey on orders
> (cost=3D0.00..672772.57 rows=3D15000045 width=3D20) (actual
> time=3D0.019..20898.325 rows=3D14999972 loops=3D1)
>                           ->  Sort  (cost=3D1994455.40..1995131.47
> rows=3D270426 width=3D19) (actual time=3D510690.114..510915.678 rows=3D31=
1208
> loops=3D1)
>                                  Sort Key: lineitem.l_orderkey
>                                     Sort Method:  external sort  Disk: 11=
568kB
>                                          ->  Bitmap Heap Scan on
> lineitem  (cost=3D336295.10..1970056.39 rows=3D270426 width=3D19) (actual
> time=3D419620.817..509685.421 rows=3D311208 loops=3D1)
>                                                Recheck Cond:
> (l_shipmode =3D ANY (_{TRUCK,AIR}_::bpchar[]))
>                                                     Filter:
> ((l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND
> (l_receiptdate >=3D _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=3D0.00..336227.49
> rows=3D15942635 width=3D0) (actual time=3D419437.172..419437.172
> rows=3D17133713 loops=3D1)
>                                                               Index
> Cond: (l_shipmode =3D ANY (_{TRUCK,AIR}_::bpchar[]))
>
> Total runtime: 537728.848 ms
>
>
> -----------------  Explain Analyze Query 12  WITHOUT INDEX
> ----------------------------
> Sort  (cost=3D3365996.33..3365996.34 rows=3D1 width=3D27) (actual
> time=3D101850.883..101850.884 rows=3D2 loops=3D1)
>   Sort Key: lineitem.l_shipmode  Sort Method:  quicksort  Memory: 25kB
>     ->  HashAggregate  (cost=3D3365996.30..3365996.32 rows=3D1 width=3D27)
> (actual time=3D101850.798..101850.800 rows=3D2 loops=3D1)
>             ->  Merge Join  (cost=3D2649608.28..3363936.68 rows=3D274616
> width=3D27) (actual time=3D75497.181..100938.830 rows=3D311208 loops=3D1)
>                  Merge Cond: (orders.o_orderkey =3D lineitem.l_orderkey)
>                      ->  Index Scan using orders_pkey on orders
> (cost=3D0.00..672771.90 rows=3D15000000 width=3D20) (actual
> time=3D0.020..20272.828 rows=3D14999972 loops=3D1)
>                               ->  Sort  (cost=3D2649545.68..2650232.22
> rows=3D274616 width=3D19) (actual time=3D75364.450..75618.772 rows=3D3112=
08
> loops=3D1)
>                                     Sort Key: lineitem.l_orderkey
>                                         Sort Method:  external sort
> Disk: 11568kB
>                                            ->  Seq Scan on lineitem
> (cost=3D0.00..2624738.17 rows=3D274616 width=3D19) (actual
> time=3D0.839..74391.087 rows=3D311208 loops=3D1)
>                                                  Filter: ((l_shipmode
> =3D ANY (_{TRUCK,AIR}_::bpchar[])) AND (l_commitdate < l_receiptdate)
> AND (l_shipdate < l_commitdate) AND (l_receiptdate >=3D
> _1997-01-01_::date) AND (l_receiptdate < _1998-01-01
> 00:00:00_::timestamp without time zone))
>                                                        Total runtime:
> 101865.253 ms
>
>  -=3D=3D=3D=3D=3D=3D=3D=3D=3D------ SQL query 12 ----------------------
>   select
>     l_shipmode,
>     sum(case
>         when o_orderpriority =3D '1-URGENT'
>             or o_orderpriority =3D '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 =3D l_orderkey
>     and l_shipmode in ('TRUCK', 'AIR')
>     and l_commitdate < l_receiptdate
>     and l_shipdate < l_commitdate
>     and l_receiptdate >=3D date '1997-01-01'
>     and l_receiptdate < date '1997-01-01' + interval '1' year
> group by
>     l_shipmode
> order by
>     l_shipmode

Complementing the question I'm using a server HP proliant Ml110-G9:
Processador: (1) Intel Xeon E5-1603v3 (2.8GHz/4-core/10MB/140W)
Mem=C3=B3ria RAM: 4GB DDR4
Disco R=C3=ADgido: SATA 1TB 7.2K rpm LFF
More specifications
here:https://www.hpe.com/us/en/product-catalog/servers/proliant-servers/pip=
.specifications.hpe-proliant-ml110-gen9-server.7796454.html
154/5000

See Below parameters presents in postgresql.conf. You would indicate
which value for example: cpu_index_tuple_cost and other CPU_*, based
on this
Server.

#seq_page_cost =3D 1.0
#random_page_cost =3D 4.0
#cpu_tuple_cost =3D 0.01
#cpu_index_tuple_cost =3D 0.005
#cpu_operator_cost =3D 0.0025
shared_buffers =3D 1GB
effective_cache_size =3D 3GB
work_mem =3D 26214kB
maintenance_work_mem =3D 512MB
checkpoint_segments =3D 128
checkpoint_completion_target =3D 0.9
wal_buffers =3D 16MB
default_statistics_target =3D 500

Best Regards
Neto Br


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance