Olá Pessoal
Preciso de uma ajuda na análise desses 2 planos de execução.
Gostaria de descobrir, porque uma query sem índice executa mais rápido
(7 vezes+-) do que com índice, embora os custos sejam menores.
Tenho outros casos que aconteceram a mesma situação. Ajustei o
servidor conforme www.pgconfig.org.
Estou utilizando o postgresql versão 9.0.1 no SO Debian 8, sei que é
uma versão antiga, mas fiz o teste em uma versão 9.6.4 e também
ocorreu o mesmo.
Pelo que vi está utilizando o índice shipmodelineitem000 no 1º plano...
Query| Índices(sim/não) |Tempo gasto |Total Cost
=====================================================
12 com índice 00:08:58 2710805.51
12 sem índice 00:01:42 3365996.34
----------------- Explain Analyze Query 12 Utilizando índice
----------------------------
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-0100: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 SEM Utilizar índice
----------------------------
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
------- 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
[]`s Neto
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
