Re: [PERFORM] Execution plan analysis - Mailing list pgsql-performance

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
> ===================================
> 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

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ória RAM: 4GB DDR4
Disco Rígido: 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 = 1.0
#random_page_cost = 4.0
#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.005
#cpu_operator_cost = 0.0025
shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 26214kB
maintenance_work_mem = 512MB
checkpoint_segments = 128
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500

Best Regards
Neto Br