Saudacões,
Estou tentando interpretar um Explain Analyze, mas travei nisso:
--> Segundo a documentacão do Postgresql em:
http://pgdocptbr.sourceforge.net/pg80/performance-tips.html
"o valor de "loops" (laços) expressa o número total de execuções do nó, e
os valores de "actual time" (tempo real) e "rows" (linhas) mostrados são
valores médios por execução.... Deve ser multiplicado pelo valor de "loops"
para obter o tempo total realmente gasto no nó."
Mas veja esse caso, em que o tempo total da query foi de 66 minutos.
( Explain Analyze completo e Query nesse link: https://goo.gl/Kp45fu ou
anexo )
O que me interessa é este trecho:
#####################################################################################
-> Index Scan using idx_l_partkeylineitem000x on lineitem
(cost=0.57..97.65 rows=26 width=36)
(actual time=23.615..419.113 rows=30 loops=26469)
Index Cond: (l_partkey = part.p_partkey)
#####################################################################################
Segundo a documentacão, deveria-se multiplicar o Actual Time pelo número de
Loops.
Ou seja: 419113 ms --> 419113/1000/60 = 6.9 minutos * 26469 (loops) =
182,6 minutos.
Mas como esse trecho demora 182,6 minutos, se a query inteira executou em
66 minutos?
Claro que estou cometendo algum erro de cálculo, mas se alguém puder me dar
uma dica de como faria esse cálculo de tempo.
O que preciso é saber qual o tempo gasto para percorrer o índice
idx_l_partkeylineitem000x, lembrando que fiz um Explain Analyze que
teoricamente é o tempo real gasto e não uma estimativa como no Explain.
[]'s Neto
Finalize GroupAggregate (cost=2360092.54..2361201.87 rows=2406 width=40)
(actual time=4011611.760..4011611.772 rows=2 loops=1) Group Key:
(date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone))
Buffers: shared
hit=468227 read=495151, temp read=45083 written=45001
-> Gather Merge (cost=2360092.54..2361087.59 rows=4812 width=72) (actual
time=4011601.629..4011611.739 rows=6 loops=1)
Workers Planned: 2 Workers Launched: 2 Buffers: shared
hit=468227
read=495151, temp read=45083 written=45001
-> Partial GroupAggregate (cost=2359092.52..2359532.14 rows=2406
width=72) (actual time=4011126.201..4011136.382 rows=2 loops=3)
Group Key: (date_part(_year_::text, (orders.o_orderdate)::timestamp
without time zone))
Buffers: shared hit=1395591 read=1498268 written=3, temp read=135274
written=135028
-> Sort (cost=2359092.52..2359136.02 rows=17400 width=46)
(actual time=4011115.986..4011117.657 rows=16138 loops=3)
Sort Key: (date_part(_year_::text,
(orders.o_orderdate)::timestamp without time zone))
Sort Method: quicksort Memory: 1631kB
Buffers: shared hit=1395591 read=1498268 written=3, temp
read=135274 written=135028
-> Hash Join (cost=1200128.12..2357866.97 rows=17400
width=46) (actual time=140888.039..4011107.564 rows=16138 loops=3)
Hash Cond: (supplier.s_nationkey = n2.n_nationkey)
Buffers: shared hit=1395578 read=1498267 written=3, temp
read=135274 written=135028
-> Hash Join (cost=1200126.56..2357564.91 rows=17400
width=24) (actual time=140887.677..4011095.102 rows=16138 loops=3)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
Buffers: shared hit=1395544 read=1498264
written=3, temp read=135274 written=135028
-> Hash Join (cost=1190051.56..2346086.71
rows=17441 width=24) (actual time=87857.582..4006208.057 rows=16138 loops=3)
Hash Cond: (lineitem.l_orderkey =
orders.o_orderkey)
Buffers: shared hit=1386717 read=1493249
written=3, temp read=133058 written=132830
-> Nested Loop (cost=747.67..1144119.43
rows=285995 width=28) (actual time=42.010..3730700.624 rows=264822 loops=3)
Buffers: shared hit=84397 read=841016
-> Parallel Bitmap Heap Scan on part
(cost=747.10..56230.60 rows=11111 width=4) (actual time=36.759..32068.811
rows=8823 loops=3)
Recheck Cond: ((p_type)::text = _LARGE BRUSHED
NICKEL_::text)
Heap Blocks: exact=7554
Buffers:shared read=22871
-> Bitmap Index Scan on idx_p_typepart000x
(cost=0.00..740.43 rows=26667 width=0) (actual time=27.152..27.152 rows=26469
loops=1)
Index Cond: ((p_type)::text = _LARGE BRUSHED
NICKEL_::text)
Buffers: shared read=134
#####################################################################################
-> Index Scan using idx_l_partkeylineitem000x on
lineitem (cost=0.57..97.65 rows=26 width=36)
(actual time=23.615..419.113 rows=30 loops=26469)
Index Cond: (l_partkey = part.p_partkey)
#####################################################################################
Buffers: shared hit=84397 read=818145
-> Hash (cost=1159287.67..1159287.67
rows=1829538 width=8) (actual time=75075.200..75075.200 rows=1823566 loops=3)
Buckets: 131072 Batches: 32 Memory Usage:
3259kB
Buffers: shared hit=1302315 read=652232
written=3, temp read=109776 written=127803
-> Hash Join (cost=325323.06..1159287.67 rows=1829538
width=8) (actual time=6556.433..74452.217 rows=1823566 loops=3)
Hash Cond: (orders.o_custkey = customer.c_custkey)
Buffers: shared hit=1302315 read=652232 written=3, temp
read=109776 written=109734
-> Bitmap Heap Scan on orders
(cost=194100.41..883787.79 rows=9147692 width=16) (actual
time=776.005..59464.022 rows=9116496 loops=3)
Recheck Cond: ((o_orderdate >= _1995-01-01_::date) AND (o_orderdate <=
_1996-12-31_::date)) Rows Removed by Index Recheck: 18647500
Heap Blocks: exact=56869 lossy=495603
Buffers: shared hit=1154771 read=577386 written=3
-> Bitmap Index Scan on idx_o_orderdateorders000x (cost=0.00..191813.48
rows=9147692 width=0) (actual time=763.001..763.001 rows=9116496 loops=3)
Index Cond: ((o_orderdate >= _1995-01-01_::date) AND (o_orderdate <=
_1996-12-31_::date))
Buffers: shared hit=49827 read=24914 written=3
-> Hash (cost=121378.66..121378.66 rows=600000 width=4) (actual
time=5762.619..5762.619 rows=600016 loops=3)
Buckets: 131072 Batches: 8 Memory Usage: 3649kB
Buffers: shared hit=147539 read=74845, temp written=4602
-> Hash Join (cost=2.66..121378.66
rows=600000 width=4)
(actual time=13.344..5496.139
rows=600016 loops=3)
Hash Cond: (customer.c_nationkey = n1.n_nationkey)
Buffers: shared hit=147539 read=74845
-> Seq Scan on customer (cost=0.00..104126.00 rows=3000000 width=12)
(actual time=13.258..4647.256 rows =3000000 loops=3)
Buffers: shared hit=147533 read=74845
-> Hash (cost=2.60..2.60 rows=5 width=4) (actual time=0.067..0.067 rows=5
loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=6
-> Hash Join (cost=1.07..2.60 rows=5 width=4) (actual time=0.055..0.062
rows=5 loops=3)
Hash Cond: (n1.n_regionkey
=region.r_regionkey)
Buffers: shared hit=6
-> Seq Scan on nation n1
(cost=0.00..1.25 rows=25 width=12) (actual time=0.008..0.011 rows=25 loops=3)
Buffers: shared hit=3
-> Hash (cost=1.06..1.06 rows=1
width=4) (actual time=0.027..0.027 rows=1 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=3
-> Seq Scan on region (cost=0.00..1.06 rows=1
width=4) (actual time=0.021..0.022 rows=1 loops=3)
Filter: (r_name = _ASIA_::bpchar) Rows Removed by Filter: 4
Buffers: shared hit=3
-> Hash (cost=6598.00..6598.00 rows=200000 width=12) (actual
time=403.283..403.283 rows=200000 loops=3)
Buckets: 131072 Batches: 4 Memory Usage: 3383kB
Buffers: shared hit=8782 read=5012, temp written=1971
-> Seq Scan on supplier (cost=0.00..6598.00 rows=200000 width=12)
(actual time=1.499..320.673 rows=200000 loops=3)
Buffers: shared hit=8782 read=5012
-> Hash (cost=1.25..1.25 rows=25 width=30) (actual
time=0.043..0.043 rows=25 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
Buffers: shared hit=3
-> Seq Scan on nation n2 (cost=0.00..1.25 rows=25 width=30)
(actual time=0.020..0.026 rows=25 loops=3)
Buffers: shared hit=3Planning time: 278.652 msExecution time:
4011612.751 ms
(1 row)
========================= QUERY 8 TPC=H
==========================================
select
o_year,
sum(case
when nation = 'INDIA' then volume
else 0
end) / sum(volume) as mkt_share
from
(
select
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
part,
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2,
region
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = 'ASIA'
and s_nationkey = n2.n_nationkey
and o_orderdate between date '1995-01-01' and date
'1996-12-31'
and p_type = 'LARGE BRUSHED NICKEL'
) as all_nations
group by
o_year
order by
o_year
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
