Euler
Luiz Rafael Culik Guimaraes wrote:
Em segundo lugar, é problema de performance? EXPLAIN. Não mande a
consulta ou estrutura de tabelas a não ser que alguém peça. Para análise
de performance, é essencial a versão, pois várias otimizações são feitas
durante as versões.
postgres 64bits versao 8.1.6 em redhat enterprise 4
E o EXPLAIN?
abaixo a query e seu explain
select b.inrcmv, b.inrndc, b.inrpur, a.cnrfor, c.movfor, b.inrcme,
a.cnrure, a.cnrutr, a.cnrdrm, a.cnrnnf, a.cnrenf, d.concpg, a.cnrvnf,
b.inrvlr, a.cnricm, b.inripi, a.cnrpis, a.cnrcofin, b.inrqtg,
'' as romplc, a.cnrbfu, a.cnrvim, a.cnrcf2, c.movpli,
q.flagsit, a.cnrsnf, a.cnrtim, c.movdoc from
qr_dbf_sie3000_log as q join granol_sie3000_dbf as a on q.cnrnum
= a.cnrnum join granol_sie3200_dbf as b on a.cnrnum = b.inrnum
join sif00_con1000_dbf as d on b.inrndc =
d.connum||trim(to_char(d.conuni,'00')) join sif50_mov1000_dbf as c on
b.inrnum = c.movnnr where a.cnrsis = 1 and b.inrdco = 1 and
b.inrcmv in ( 1,4,5,20,29,44,45,49,67,68,70,71,73 ) order by q.cnrnum
LIMIT 1
resultado do plan
"Limit (cost=37.47..37.47 rows=1 width=326) (actual time=0.213..0.213
rows=0 loops=1)"
" -> Sort (cost=37.47..37.47 rows=1 width=326) (actual time=0.202..0.202
rows=0 loops=1)"
" Sort Key: q.cnrnum"
" -> Nested Loop (cost=5.88..37.46 rows=1 width=326) (actual
time=0.030..0.030 rows=0 loops=1)"
" -> Nested Loop (cost=5.88..32.62 rows=1 width=316) (actual
time=0.024..0.024 rows=0 loops=1)"
" -> Nested Loop (cost=4.86..21.90 rows=1 width=297)
(actual time=0.020..0.020 rows=0 loops=1)"
" Join Filter: ("inner".cnrnum = "outer".inrnum)"
" -> Hash Join (cost=4.86..17.07 rows=1
width=117) (actual time=0.015..0.015 rows=0 loops=1)"
" Hash Cond: ((("outer".connum)::text ||
btrim(to_char("outer".conuni, '00'::text))) = ("inner".inrndc)::text)"
" -> Seq Scan on sif00_con1000_dbf d
(cost=0.00..10.80 rows=80 width=29) (actual time=0.003..0.003 rows=0
loops=1)"
" -> Hash (cost=4.86..4.86 rows=1
width=108) (never executed)"
" -> Index Scan using
granol_sie3200_dbf_12346 on granol_sie3200_dbf b (cost=0.00..4.86 rows=1
width=108) (never executed)"
" Index Cond: (inrdco =
1::numeric)"
" Filter: ((inrcmv = 1::numeric)
OR (inrcmv = 4::numeric) OR (inrcmv = 5::numeric) OR (inrcmv = 20::numeric)
OR (inrcmv = 29::numeric) OR (inrcmv = 44::numeric) OR (inrcmv =
45::numeric) OR (inrcmv = 49::numeric) OR (inrcmv = 67::numeric) OR (inrcmv
= 68::numeric) OR (inrcmv = 70::numeric) OR (inrcmv = 71::numeric) OR
(inrcmv = 73::numeric))"
" -> Index Scan using granol_sie3000_dbf_12346 on
granol_sie3000_dbf a (cost=0.00..4.82 rows=1 width=180) (never executed)"
" Index Cond: (cnrsis = 1::numeric)"
" -> Bitmap Heap Scan on qr_dbf_sie3000_log q
(cost=1.02..10.63 rows=7 width=19) (never executed)"
" Recheck Cond: (q.cnrnum = "outer".cnrnum)"
" -> Bitmap Index Scan on qr_dbf_sie3000_log_pkey
(cost=0.00..1.02 rows=7 width=0) (never executed)"
" Index Cond: (q.cnrnum = "outer".cnrnum)"
" -> Index Scan using sif50_mov100c_000006 on
sif50_mov1000_dbf c (cost=0.00..4.82 rows=1 width=52) (never executed)"
" Index Cond: ("outer".inrnum = c.movnnr)"
"Total runtime: 1.210 ms"
query
select b.inrcmv, b.inrndc, b.inrpur, a.cnrfor, d.movfor, b.inrcme,
a.cnrure, a.cnrutr, a.cnrdrm, a.cnrnnf, a.cnrenf, '' as concpg, a.cnrvnf,
b.inrvlr, a.cnricm, b.inripi, a.cnrpis, a.cnrcofin, b.inrqtg, c.romplc,
a.cnrbfu, a.cnrvim, a.cnrcf2, d.movpli, q.flagsit, a.cnrsnf, a.cnrtim,
d.movdoc from qr_dbf_sie3000_log as q join granol_sie3000_dbf as a on
q.cnrnum = a.cnrnum join granol_sie3200_dbf as b on a.cnrnum = b.inrnum
join granol_sie3300_dbf as c on q.cnrnum = c.romnum join
sif50_mov1000_dbf as d on a.cnrcon = d.movcon and a.cnruni = d.movunc
and a.cnrnfr = d.movnpr where ( b.inrdco = 1 or a.cnrlrt >= 70 ) and
b.inrcmv in (21,49,72) and c.romtip = 'D' order by q.cnrnum
LIMIT 1
resultado do plano
"Limit (cost=36.50..36.51 rows=1 width=331) (actual time=0.053..0.053
rows=0 loops=1)"
" -> Sort (cost=36.50..36.51 rows=1 width=331) (actual time=0.046..0.046
rows=0 loops=1)"
" Sort Key: q.cnrnum"
" -> Nested Loop (cost=6.20..36.49 rows=1 width=331) (actual
time=0.025..0.025 rows=0 loops=1)"
" Join Filter: (("outer".cnrcon = "inner".movcon) AND
("outer".cnruni = "inner".movunc))"
" -> Nested Loop (cost=6.20..31.65 rows=1 width=328) (actual
time=0.020..0.020 rows=0 loops=1)"
" -> Nested Loop (cost=5.17..20.93 rows=1 width=351)
(actual time=0.016..0.016 rows=0 loops=1)"
" Join Filter: (("inner".inrdco = 1::numeric) OR
("outer".cnrlrt >= 70::numeric))"
" -> Hash Join (cost=5.17..16.08 rows=1
width=252) (actual time=0.011..0.011 rows=0 loops=1)"
" Hash Cond: ("outer".cnrnum =
"inner".romnum)"
" -> Seq Scan on granol_sie3000_dbf a
(cost=0.00..10.60 rows=60 width=224) (actual time=0.003..0.003 rows=0
loops=1)"
" -> Hash (cost=5.17..5.17 rows=1 width=28)
(never executed)"
" -> Index Scan using
granol_sie3300_dbf_12345 on granol_sie3300_dbf c (cost=0.00..5.17 rows=1
width=28) (never executed)"
" Index Cond: (romtip =
'D'::bpchar)"
" -> Index Scan using granol_sie3200_dbf_12345 on
granol_sie3200_dbf b (cost=0.00..4.83 rows=1 width=117) (never executed)"
" Index Cond: ("outer".cnrnum = b.inrnum)"
" Filter: ((inrcmv = 21::numeric) OR (inrcmv
= 49::numeric) OR (inrcmv = 72::numeric))"
" -> Bitmap Heap Scan on qr_dbf_sie3000_log q
(cost=1.02..10.63 rows=7 width=19) (never executed)"
" Recheck Cond: (q.cnrnum = "outer".cnrnum)"
" -> Bitmap Index Scan on qr_dbf_sie3000_log_pkey
(cost=0.00..1.02 rows=7 width=0) (never executed)"
" Index Cond: (q.cnrnum = "outer".cnrnum)"
" -> Index Scan using sif50_mov1000_dbf_12345 on
sif50_mov1000_dbf d (cost=0.00..4.82 rows=1 width=73) (never executed)"
" Index Cond: ("outer".cnrnfr = d.movnpr)"
"Total runtime: 1.067 ms"
query
select q.cnrnum, b.inrcmv, b.inrndc, b.inrpur, a.cnrfor, '' as movfor,
b.inrcme, a.cnrure, a.cnrutr, a.cnrdrm, a.cnrnnf, a.cnrenf, '' as concpg,
a.cnrvnf, b.inrvlr, a.cnricm, b.inripi, a.cnrpis, a.cnrcofin, b.inrqtg,
c.romplc, a.cnrbfu, a.cnrvim, a.cnrcf2, '' as movpli, q.flagsit, a.cnrsnf,
a.cnrtim, '' as movdoc from qr_dbf_sie3000_log as q join
granol_sie3000_dbf as a on q.cnrnum = a.cnrnum join granol_sie3200_dbf
as b on a.cnrnum = b.inrnum left join granol_sie3300_dbf as c on
q.cnrnum = c.romnum and c.romtip = 'D' where a.cnrlrt < 70 and
b.inrcmv in (21,22,33,34,40,42,43,47,48,98,99) order by q.cnrnum LIMIT 1
resultado do plano
"Limit (cost=12.21..22.24 rows=1 width=293) (actual time=0.111..0.111
rows=0 loops=1)"
" -> Merge Left Join (cost=12.21..82.43 rows=7 width=293) (actual
time=0.104..0.104 rows=0 loops=1)"
" Merge Cond: ("outer".cnrnum = "inner".romnum)"
" -> Nested Loop (cost=12.21..77.23 rows=7 width=279) (actual
time=0.099..0.099 rows=0 loops=1)"
" -> Merge Join (cost=11.18..55.79 rows=2 width=288) (actual
time=0.094..0.094 rows=0 loops=1)"
" Merge Cond: ("outer".inrnum = "inner".cnrnum)"
" -> Index Scan using granol_sie3200_dbf_12345 on
granol_sie3200_dbf b (cost=0.00..44.47 rows=10 width=108) (actual
time=0.089..0.089 rows=0 loops=1)"
" Filter: ((inrcmv = 21::numeric) OR (inrcmv =
22::numeric) OR (inrcmv = 33::numeric) OR (inrcmv = 34::numeric) OR (inrcmv
= 40::numeric) OR (inrcmv = 42::numeric) OR (inrcmv = 43::numeric) OR
(inrcmv = 47::numeric) OR (inrcmv = 48::numeric) OR (inrcmv = 98::numeric)
OR (inrcmv = 99::numeric))"
" -> Sort (cost=11.18..11.23 rows=20 width=180) (never
executed)"
" Sort Key: a.cnrnum"
" -> Seq Scan on granol_sie3000_dbf a
(cost=0.00..10.75 rows=20 width=180) (never executed)"
" Filter: (cnrlrt < 70::numeric)"
" -> Bitmap Heap Scan on qr_dbf_sie3000_log q
(cost=1.02..10.63 rows=7 width=19) (never executed)"
" Recheck Cond: (q.cnrnum = "outer".cnrnum)"
" -> Bitmap Index Scan on qr_dbf_sie3000_log_pkey
(cost=0.00..1.02 rows=7 width=0) (never executed)"
" Index Cond: (q.cnrnum = "outer".cnrnum)"
" -> Index Scan using granol_sie3300_dbf_12345 on granol_sie3300_dbf
c (cost=0.00..5.17 rows=1 width=28) (never executed)"
" Index Cond: (romtip = 'D'::bpchar)"
"Total runtime: 0.682 ms"
[]s
Luiz
_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
