Thread: Improving select peformance
Hi,
One of our end users was complaining about a report that was taking too much time to execute and I´ve discovered that the following SQL statement was the responsible for it.
I would appreciate any suggestions to improve performance of it.
Thank you very much in advance!
_____________________________________________________________________________________________________________________________
explain analyze select (VEN.DOCUME)::varchar(13) as COLUNA0,
(VENCODPGT.APEPGT)::varchar(9) as COLUNA1,
(COALESCE(COALESCE(VEN.VLRLIQ,0) * (CASE VEN.VLRNOT WHEN 0 THEN 0 ELSE IVE.VLRMOV / VEN.VLRNOT END),0)) as COLUNA2,
(COALESCE(IVE.QTDMOV,0)) as COLUNA3,
(VIPR.NOMPRO)::varchar(83) as COLUNA4,
(VIPR.REFPRO)::varchar(20) as COLUNA5
from TV_VEN VEN
inner join TT_IVE IVE ON IVE.SEQUEN = VEN.SEQUEN and
IVE.CODFIL = VEN.CODFIL
inner join TV_IPR VIPR ON VIPR.FILMAT = IVE.FILMAT and
VIPR.CODMAT = IVE.CODMAT and
VIPR.CODCOR = IVE.CODCOR and
VIPR.CODTAM = IVE.CODTAM
left join TT_PLA VENCODPGT ON VEN.FILPGT = VENCODPGT.FILPGT AND VEN.CODPGT = VENCODPGT.CODPGT
where ('001' = VEN.CODFIL)
and VEN.DATHOR between '07/12/2007 00:00:00' and '07/12/2007 23:59:59'
and (VEN.CODNAT = '-3')
and IVE.SITMOV <> 'C'
and ('1' = VIPR.DEPART) ;
(VENCODPGT.APEPGT)::varchar(9) as COLUNA1,
(COALESCE(COALESCE(VEN.VLRLIQ,0) * (CASE VEN.VLRNOT WHEN 0 THEN 0 ELSE IVE.VLRMOV / VEN.VLRNOT END),0)) as COLUNA2,
(COALESCE(IVE.QTDMOV,0)) as COLUNA3,
(VIPR.NOMPRO)::varchar(83) as COLUNA4,
(VIPR.REFPRO)::varchar(20) as COLUNA5
from TV_VEN VEN
inner join TT_IVE IVE ON IVE.SEQUEN = VEN.SEQUEN and
IVE.CODFIL = VEN.CODFIL
inner join TV_IPR VIPR ON VIPR.FILMAT = IVE.FILMAT and
VIPR.CODMAT = IVE.CODMAT and
VIPR.CODCOR = IVE.CODCOR and
VIPR.CODTAM = IVE.CODTAM
left join TT_PLA VENCODPGT ON VEN.FILPGT = VENCODPGT.FILPGT AND VEN.CODPGT = VENCODPGT.CODPGT
where ('001' = VEN.CODFIL)
and VEN.DATHOR between '07/12/2007 00:00:00' and '07/12/2007 23:59:59'
and (VEN.CODNAT = '-3')
and IVE.SITMOV <> 'C'
and ('1' = VIPR.DEPART) ;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=995.52..75661.01 rows=1 width=195) (actual time=4488.166..1747121.374 rows=256 loops=1)
-> Nested Loop (cost=995.52..75660.62 rows=1 width=199) (actual time=4481.323..1747105.903 rows=256 loops=1)
Join Filter: ((gra.filmat = ive.filmat) AND (gra.codmat = ive.codmat) AND (gra.codcor = ive.codcor) AND (gra.codtam = ive.codtam))
-> Nested Loop (cost=1.11..3906.12 rows=1 width=151) (actual time=15.626..128.934 rows=414 loops=1)
Join Filter: (div.coddiv = ddiv.codtab)
-> Nested Loop (cost=1.11..3905.05 rows=1 width=160) (actual time=15.611..121.455 rows=414 loops=1)
Join Filter: (sub.codsub = dsub.codtab)
-> Nested Loop (cost=1.11..3903.99 rows=1 width=169) (actual time=15.593..113.866 rows=414 loops=1)
Join Filter: ((gra.codcor)::text = ((div.codite)::text || ''::text))
-> Hash Join (cost=1.11..3888.04 rows=11 width=146) (actual time=15.560..85.376 rows=414 loops=1)
Hash Cond: ((gra.codtam)::text = ((sub.codite)::text || ''::text))
-> Nested Loop (cost=0.00..3883.64 rows=423 width=123) (actual time=15.376..81.482 rows=414 loops=1)
-> Index Scan using i_fk_pro_ddep on tt_pro pro (cost=0.00..149.65 rows=516 width=77) (actual time=15.244..30.586 rows=414 loops=1)
Index Cond: (1::numeric = depart)
-> Index Scan using pk_gra on tt_gra gra (cost=0.00..7.22 rows=1 width=46) (actual time=0.104..0.110 rows=1 loops=414)
Index Cond: ((pro.filmat = gra.filmat) AND (pro.codmat = gra.codmat))
-> Hash (cost=1.05..1.05 rows=5 width=32) (actual time=0.048..0.048 rows=5 loops=1)
-> Seq Scan on tt_sub sub (cost=0.00..1.05 rows=5 width=32) (actual time=0.016..0.024 rows=5 loops=1)
-> Seq Scan on tt_div div (cost=0.00..1.15 rows=15 width=32) (actual time=0.004..0.022 rows=15 loops=414)
-> Seq Scan on td_sub dsub (cost=0.00..1.03 rows=3 width=9) (actual time=0.003..0.007 rows=3 loops=414)
-> Seq Scan on td_div ddiv (cost=0.00..1.03 rows=3 width=9) (actual time=0.002..0.007 rows=3 loops=414)
-> Hash Join (cost=994.41..71746.74 rows=388 width=114) (actual time=5.298..4218.486 rows=857 loops=414)
Hash Cond: (ive.sequen = ven.sequen)
-> Nested Loop (cost=0.00..68318.52 rows=647982 width=85) (actual time=0.026..3406.170 rows=643739 loops=414)
-> Seq Scan on td_nat nat (cost=0.00..1.24 rows=1 width=9) (actual time=0.004..0.014 rows=1 loops=414)
Filter: (-3::numeric = codtab)
-> Seq Scan on tt_ive ive (cost=0.00..61837.46 rows=647982 width=76) (actual time=0.017..1926.983 rows=643739 loops=414)
Filter: ((sitmov <> 'C'::bpchar) AND ('001'::bpchar = codfil))
-> Hash (cost=992.08..992.08 rows=186 width=89) (actual time=33.234..33.234 rows=394 loops=1)
-> Hash Left Join (cost=3.48..992.08 rows=186 width=89) (actual time=13.163..32.343 rows=394 loops=1)
Hash Cond: ((ven.filcli = cfg.vc_filcli) AND (ven.codcli = cfg.vc_codcli))
-> Hash Join (cost=2.45..989.65 rows=186 width=106) (actual time=13.131..31.060 rows=394 loops=1)
Hash Cond: ((ven.filpgt = pla.filpgt) AND (ven.codpgt = pla.codpgt))
-> Index Scan using i_lc_ven_dathor on tt_ven ven (cost=0.00..983.95 rows=186 width=106) (actual time=13.026..29.634 rows=394 loops=1)
Index Cond: ((dathor >= '2007-07-12 00:00:00'::timestamp without time zone) AND (dathor <= '2007-07-12 23:59:59'::timestamp without time zone))
Filter: (('001'::bpchar = codfil) AND (codnat = -3::numeric))
-> Hash (cost=2.18..2.18 rows=18 width=14) (actual time=0.081..0.081 rows=18 loops=1)
-> Seq Scan on tt_pla pla (cost=0.00..2.18 rows=18 width=14) (actual time=0.013..0.043 rows=18 loops=1)
-> Hash (cost=1.01..1.01 rows=1 width=17) (actual time=0.017..0.017 rows=1 loops=1)
-> Seq Scan on tt_cfg cfg (cost=0.00..1.01 rows=1 width=17) (actual time=0.010..0.011 rows=1 loops=1)
-> Index Scan using pk_pla on tt_pla vencodpgt (cost=0.00..0.31 rows=1 width=24) (actual time=0.037..0.040 rows=1 loops=256)
Index Cond: ((ven.filpgt = vencodpgt.filpgt) AND (ven.codpgt = vencodpgt.codpgt))
Total runtime: 1747122.219 ms
(43 rows)
Nested Loop Left Join (cost=995.52..75661.01 rows=1 width=195) (actual time=4488.166..1747121.374 rows=256 loops=1)
-> Nested Loop (cost=995.52..75660.62 rows=1 width=199) (actual time=4481.323..1747105.903 rows=256 loops=1)
Join Filter: ((gra.filmat = ive.filmat) AND (gra.codmat = ive.codmat) AND (gra.codcor = ive.codcor) AND (gra.codtam = ive.codtam))
-> Nested Loop (cost=1.11..3906.12 rows=1 width=151) (actual time=15.626..128.934 rows=414 loops=1)
Join Filter: (div.coddiv = ddiv.codtab)
-> Nested Loop (cost=1.11..3905.05 rows=1 width=160) (actual time=15.611..121.455 rows=414 loops=1)
Join Filter: (sub.codsub = dsub.codtab)
-> Nested Loop (cost=1.11..3903.99 rows=1 width=169) (actual time=15.593..113.866 rows=414 loops=1)
Join Filter: ((gra.codcor)::text = ((div.codite)::text || ''::text))
-> Hash Join (cost=1.11..3888.04 rows=11 width=146) (actual time=15.560..85.376 rows=414 loops=1)
Hash Cond: ((gra.codtam)::text = ((sub.codite)::text || ''::text))
-> Nested Loop (cost=0.00..3883.64 rows=423 width=123) (actual time=15.376..81.482 rows=414 loops=1)
-> Index Scan using i_fk_pro_ddep on tt_pro pro (cost=0.00..149.65 rows=516 width=77) (actual time=15.244..30.586 rows=414 loops=1)
Index Cond: (1::numeric = depart)
-> Index Scan using pk_gra on tt_gra gra (cost=0.00..7.22 rows=1 width=46) (actual time=0.104..0.110 rows=1 loops=414)
Index Cond: ((pro.filmat = gra.filmat) AND (pro.codmat = gra.codmat))
-> Hash (cost=1.05..1.05 rows=5 width=32) (actual time=0.048..0.048 rows=5 loops=1)
-> Seq Scan on tt_sub sub (cost=0.00..1.05 rows=5 width=32) (actual time=0.016..0.024 rows=5 loops=1)
-> Seq Scan on tt_div div (cost=0.00..1.15 rows=15 width=32) (actual time=0.004..0.022 rows=15 loops=414)
-> Seq Scan on td_sub dsub (cost=0.00..1.03 rows=3 width=9) (actual time=0.003..0.007 rows=3 loops=414)
-> Seq Scan on td_div ddiv (cost=0.00..1.03 rows=3 width=9) (actual time=0.002..0.007 rows=3 loops=414)
-> Hash Join (cost=994.41..71746.74 rows=388 width=114) (actual time=5.298..4218.486 rows=857 loops=414)
Hash Cond: (ive.sequen = ven.sequen)
-> Nested Loop (cost=0.00..68318.52 rows=647982 width=85) (actual time=0.026..3406.170 rows=643739 loops=414)
-> Seq Scan on td_nat nat (cost=0.00..1.24 rows=1 width=9) (actual time=0.004..0.014 rows=1 loops=414)
Filter: (-3::numeric = codtab)
-> Seq Scan on tt_ive ive (cost=0.00..61837.46 rows=647982 width=76) (actual time=0.017..1926.983 rows=643739 loops=414)
Filter: ((sitmov <> 'C'::bpchar) AND ('001'::bpchar = codfil))
-> Hash (cost=992.08..992.08 rows=186 width=89) (actual time=33.234..33.234 rows=394 loops=1)
-> Hash Left Join (cost=3.48..992.08 rows=186 width=89) (actual time=13.163..32.343 rows=394 loops=1)
Hash Cond: ((ven.filcli = cfg.vc_filcli) AND (ven.codcli = cfg.vc_codcli))
-> Hash Join (cost=2.45..989.65 rows=186 width=106) (actual time=13.131..31.060 rows=394 loops=1)
Hash Cond: ((ven.filpgt = pla.filpgt) AND (ven.codpgt = pla.codpgt))
-> Index Scan using i_lc_ven_dathor on tt_ven ven (cost=0.00..983.95 rows=186 width=106) (actual time=13.026..29.634 rows=394 loops=1)
Index Cond: ((dathor >= '2007-07-12 00:00:00'::timestamp without time zone) AND (dathor <= '2007-07-12 23:59:59'::timestamp without time zone))
Filter: (('001'::bpchar = codfil) AND (codnat = -3::numeric))
-> Hash (cost=2.18..2.18 rows=18 width=14) (actual time=0.081..0.081 rows=18 loops=1)
-> Seq Scan on tt_pla pla (cost=0.00..2.18 rows=18 width=14) (actual time=0.013..0.043 rows=18 loops=1)
-> Hash (cost=1.01..1.01 rows=1 width=17) (actual time=0.017..0.017 rows=1 loops=1)
-> Seq Scan on tt_cfg cfg (cost=0.00..1.01 rows=1 width=17) (actual time=0.010..0.011 rows=1 loops=1)
-> Index Scan using pk_pla on tt_pla vencodpgt (cost=0.00..0.31 rows=1 width=24) (actual time=0.037..0.040 rows=1 loops=256)
Index Cond: ((ven.filpgt = vencodpgt.filpgt) AND (ven.codpgt = vencodpgt.codpgt))
Total runtime: 1747122.219 ms
(43 rows)
_____________________________________________________________________________________________________________________________________
Table and view definitions can be accessed at: http://www.opendb.com.br/v1/problem0707.txt
Reimer
"Carlos H. Reimer" <carlos.reimer@opendb.com.br> writes:
> One of our end users was complaining about a report that was taking too much
> time to execute and I�ve discovered that the following SQL statement was the
> responsible for it.
Here's part of the problem:
> Join Filter: ((gra.codcor)::text =
> ((div.codite)::text || ''::text))
> -> Hash Join (cost=1.11..3888.04 rows=11
> width=146) (actual time=15.560..85.376 rows=414 loops=1)
> Hash Cond: ((gra.codtam)::text =
> ((sub.codite)::text || ''::text))
Why such bizarre join conditions? Why don't you lose the useless
concatenations of empty strings and have just a plain equality
comparison? This technique completely destroys any chance of the
planner making good estimates of the join result sizes (and the bad
estimates it's coming out with are part of the problem).
> -> Nested Loop (cost=0.00..68318.52 rows=647982 width=85)
> (actual time=0.026..3406.170 rows=643739 loops=414)
> -> Seq Scan on td_nat nat (cost=0.00..1.24 rows=1
> width=9) (actual time=0.004..0.014 rows=1 loops=414)
> Filter: (-3::numeric = codtab)
> -> Seq Scan on tt_ive ive (cost=0.00..61837.46
> rows=647982 width=76) (actual time=0.017..1926.983 rows=643739 loops=414)
> Filter: ((sitmov <> 'C'::bpchar) AND
> ('001'::bpchar = codfil))
The other big problem seems to be that it's choosing to do this
unconstrained join first. I'm not sure about the cause of that,
but maybe you need to increase join_collapse_limit. What PG version
is this anyway?
A more general comment, if you are open to schema changes, is that you
should change all the "numeric(n,0)" fields to integer (or possibly
smallint or bigint as needed). Particularly the ones that are used as
join keys, primary keys, foreign keys.
regards, tom lane
Hi,
I have changed the view to eliminate the bizarre concatenation conditions
but even so the response time did not change.
Changing the join_collapse_limit from 8 to 1 caused the decrease in response
time.
Here is the explain analyze with the join_collapse_limit set to 1:
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------------
Nested Loop Left Join (cost=969.53..20638.03 rows=1 width=194) (actual
time=10.309..5405.701 rows=256 loops=1)
Join Filter: ((ven.filpgt = vencodpgt.filpgt) AND (ven.codpgt =
vencodpgt.codpgt))
-> Nested Loop (cost=969.53..20635.51 rows=1 width=198) (actual
time=10.211..5391.358 rows=256 loops=1)
Join Filter: ((gra.filmat = ive.filmat) AND (gra.codmat =
ive.codmat) AND (gra.codcor = ive.codcor) AND (gra.codtam = ive.codtam))
-> Nested Loop (cost=1.34..3410.10 rows=1 width=150) (actual
time=0.248..38.966 rows=414 loops=1)
Join Filter: (sub.codsub = dsub.codtab)
-> Nested Loop (cost=1.34..3409.04 rows=1 width=159)
(actual time=0.237..32.520 rows=414 loops=1)
Join Filter: ((gra.codtam)::text = ((sub.codite)::text
|| ''::text))
-> Nested Loop (cost=1.34..3376.84 rows=28 width=136)
(actual time=0.226..20.978 rows=414 loops=1)
-> Hash Join (cost=1.34..3356.99 rows=28
width=145) (actual time=0.215..15.225 rows=414 loops=1)
Hash Cond: ((gra.codcor)::text =
((div.codite)::text || ''::text))
-> Nested Loop (cost=0.00..3352.55
rows=377 width=122) (actual time=0.139..12.115 rows=414 loops=1)
-> Index Scan using i_fk_pro_ddep on
tt_pro pro (cost=0.00..123.83 rows=437 width=76) (actual time=0.092..1.212
rows=414 loops=1)
Index Cond: (1::numeric =
depart)
-> Index Scan using pk_gra on tt_gra
gra (cost=0.00..7.37 rows=1 width=46) (actual time=0.016..0.018 rows=1
loops=414)
Index Cond: ((pro.filmat =
gra.filmat) AND (pro.codmat = gra.codmat))
-> Hash (cost=1.15..1.15 rows=15
width=32) (actual time=0.060..0.060 rows=15 loops=1)
-> Seq Scan on tt_div div
(cost=0.00..1.15 rows=15 width=32) (actual time=0.005..0.021 rows=15
loops=1)
-> Index Scan using pk_ddiv on td_div ddiv
(cost=0.00..0.70 rows=1 width=9) (actual time=0.006..0.009 rows=1 loops=414)
Index Cond: (div.coddiv = ddiv.codtab)
-> Seq Scan on tt_sub sub (cost=0.00..1.05 rows=5
width=32) (actual time=0.003..0.007 rows=5 loops=414)
-> Seq Scan on td_sub dsub (cost=0.00..1.03 rows=3 width=9)
(actual time=0.002..0.006 rows=3 loops=414)
-> Nested Loop (cost=968.19..17218.15 rows=363 width=114) (actual
time=0.040..12.019 rows=857 loops=414)
-> Nested Loop (cost=968.19..974.85 rows=174 width=80)
(actual time=0.022..3.149 rows=394 loops=414)
-> Merge Join (cost=966.95..970.13 rows=174 width=89)
(actual time=0.019..1.317 rows=394 loops=414)
Merge Cond: ((pla.codpgt = ven.codpgt) AND
(pla.filpgt = ven.filpgt))
-> Sort (cost=2.56..2.60 rows=18 width=14)
(actual time=0.001..0.007 rows=8 loops=414)
Sort Key: pla.codpgt, pla.filpgt
-> Seq Scan on tt_pla pla
(cost=0.00..2.18 rows=18 width=14) (actual time=0.005..0.031 rows=18
loops=1)
-> Sort (cost=964.39..964.83 rows=174 width=89)
(actual time=0.013..0.328 rows=394 loops=414)
Sort Key: ven.codpgt, ven.filpgt
-> Nested Loop Left Join
(cost=1.01..957.92 rows=174 width=89) (actual time=0.068..4.212 rows=394
loops=1)
Join Filter: ((ven.filcli =
cfg.vc_filcli) AND (ven.codcli = cfg.vc_codcli))
-> Index Scan using i_lc_ven_dathor
on tt_ven ven (cost=0.00..952.56 rows=174 width=106) (actual
time=0.054..2.079 rows=394 loops=1)
Index Cond: ((dathor >=
'2007-07-12 00:00:00'::timestamp without time zone) AND (dathor <=
'2007-07-12 23:59:59'::timestamp without time zone))
Filter: (('001'::bpchar =
codfil) AND (codnat = -3::numeric))
-> Materialize (cost=1.01..1.02
rows=1 width=17) (actual time=0.001..0.002 rows=1 loops=394)
-> Seq Scan on tt_cfg cfg
(cost=0.00..1.01 rows=1 width=17) (actual time=0.004..0.006 rows=1 loops=1)
-> Materialize (cost=1.24..1.25 rows=1 width=9)
(actual time=0.001..0.002 rows=1 loops=163116)
-> Seq Scan on td_nat nat (cost=0.00..1.24
rows=1 width=9) (actual time=0.010..0.015 rows=1 loops=1)
Filter: (-3::numeric = codtab)
-> Index Scan using pk_ive on tt_ive ive (cost=0.00..93.04
rows=25 width=76) (actual time=0.012..0.017 rows=2 loops=163116)
Index Cond: (('001'::bpchar = ive.codfil) AND
(ive.sequen = ven.sequen))
Filter: (sitmov <> 'C'::bpchar)
-> Seq Scan on tt_pla vencodpgt (cost=0.00..2.18 rows=18 width=24)
(actual time=0.003..0.018 rows=18 loops=256)
Total runtime: 5406.470 ms
(46 rows)
When the join_collapse_limit is set to 8:
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------------
Nested Loop Left Join (cost=995.52..75661.01 rows=1 width=195) (actual
time=4488.166..1747121.374 rows=256 loops=1)
-> Nested Loop (cost=995.52..75660.62 rows=1 width=199) (actual
time=4481.323..1747105.903 rows=256 loops=1)
Join Filter: ((gra.filmat = ive.filmat) AND (gra.codmat =
ive.codmat) AND (gra.codcor = ive.codcor) AND (gra.codtam = ive.codtam))
-> Nested Loop (cost=1.11..3906.12 rows=1 width=151) (actual
time=15.626..128.934 rows=414 loops=1)
Join Filter: (div.coddiv = ddiv.codtab)
-> Nested Loop (cost=1.11..3905.05 rows=1 width=160)
(actual time=15.611..121.455 rows=414 loops=1)
Join Filter: (sub.codsub = dsub.codtab)
-> Nested Loop (cost=1.11..3903.99 rows=1 width=169)
(actual time=15.593..113.866 rows=414 loops=1)
Join Filter: ((gra.codcor)::text =
((div.codite)::text || ''::text))
-> Hash Join (cost=1.11..3888.04 rows=11
width=146) (actual time=15.560..85.376 rows=414 loops=1)
Hash Cond: ((gra.codtam)::text =
((sub.codite)::text || ''::text))
-> Nested Loop (cost=0.00..3883.64
rows=423 width=123) (actual time=15.376..81.482 rows=414 loops=1)
-> Index Scan using i_fk_pro_ddep on
tt_pro pro (cost=0.00..149.65 rows=516 width=77) (actual
time=15.244..30.586 rows=414 loops=1)
Index Cond: (1::numeric =
depart)
-> Index Scan using pk_gra on tt_gra
gra (cost=0.00..7.22 rows=1 width=46) (actual time=0.104..0.110 rows=1
loops=414)
Index Cond: ((pro.filmat =
gra.filmat) AND (pro.codmat = gra.codmat))
-> Hash (cost=1.05..1.05 rows=5 width=32)
(actual time=0.048..0.048 rows=5 loops=1)
-> Seq Scan on tt_sub sub
(cost=0.00..1.05 rows=5 width=32) (actual time=0.016..0.024 rows=5 loops=1)
-> Seq Scan on tt_div div (cost=0.00..1.15
rows=15 width=32) (actual time=0.004..0.022 rows=15 loops=414)
-> Seq Scan on td_sub dsub (cost=0.00..1.03 rows=3
width=9) (actual time=0.003..0.007 rows=3 loops=414)
-> Seq Scan on td_div ddiv (cost=0.00..1.03 rows=3 width=9)
(actual time=0.002..0.007 rows=3 loops=414)
-> Hash Join (cost=994.41..71746.74 rows=388 width=114) (actual
time=5.298..4218.486 rows=857 loops=414)
Hash Cond: (ive.sequen = ven.sequen)
-> Nested Loop (cost=0.00..68318.52 rows=647982 width=85)
(actual time=0.026..3406.170 rows=643739 loops=414)
-> Seq Scan on td_nat nat (cost=0.00..1.24 rows=1
width=9) (actual time=0.004..0.014 rows=1 loops=414)
Filter: (-3::numeric = codtab)
-> Seq Scan on tt_ive ive (cost=0.00..61837.46
rows=647982 width=76) (actual time=0.017..1926.983 rows=643739 loops=414)
Filter: ((sitmov <> 'C'::bpchar) AND
('001'::bpchar = codfil))
-> Hash (cost=992.08..992.08 rows=186 width=89) (actual
time=33.234..33.234 rows=394 loops=1)
-> Hash Left Join (cost=3.48..992.08 rows=186
width=89) (actual time=13.163..32.343 rows=394 loops=1)
Hash Cond: ((ven.filcli = cfg.vc_filcli) AND
(ven.codcli = cfg.vc_codcli))
-> Hash Join (cost=2.45..989.65 rows=186
width=106) (actual time=13.131..31.060 rows=394 loops=1)
Hash Cond: ((ven.filpgt = pla.filpgt) AND
(ven.codpgt = pla.codpgt))
-> Index Scan using i_lc_ven_dathor on
tt_ven ven (cost=0.00..983.95 rows=186 width=106) (actual
time=13.026..29.634 rows=394 loops=1)
Index Cond: ((dathor >= '2007-07-12
00:00:00'::timestamp without time zone) AND (dathor <= '2007-07-12
23:59:59'::timestamp without time zone))
Filter: (('001'::bpchar = codfil) AND
(codnat = -3::numeric))
-> Hash (cost=2.18..2.18 rows=18
width=14) (actual time=0.081..0.081 rows=18 loops=1)
-> Seq Scan on tt_pla pla
(cost=0.00..2.18 rows=18 width=14) (actual time=0.013..0.043 rows=18
loops=1)
-> Hash (cost=1.01..1.01 rows=1 width=17)
(actual time=0.017..0.017 rows=1 loops=1)
-> Seq Scan on tt_cfg cfg
(cost=0.00..1.01 rows=1 width=17) (actual time=0.010..0.011 rows=1 loops=1)
-> Index Scan using pk_pla on tt_pla vencodpgt (cost=0.00..0.31 rows=1
width=24) (actual time=0.037..0.040 rows=1 loops=256)
Index Cond: ((ven.filpgt = vencodpgt.filpgt) AND (ven.codpgt =
vencodpgt.codpgt))
Total runtime: 1747122.219 ms
(43 rows)
The PG version is the 8.2.3.
Apparently the planner is not doing the correct choice by default, correct?
I could change the application and insert the set join_collapse_limit to 1
before the select, but can this solution be considered or the problem is in
another place?
Thank you in advance!
Reimer
> -----Mensagem original-----
> De: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]Em nome de Tom Lane
> Enviada em: quinta-feira, 19 de julho de 2007 22:31
> Para: carlos.reimer@opendb.com.br
> Cc: pgsql-performance@postgresql.org
> Assunto: Re: [PERFORM] Improving select peformance
>
>
> "Carlos H. Reimer" <carlos.reimer@opendb.com.br> writes:
> > One of our end users was complaining about a report that was
> taking too much
> > time to execute and I´ve discovered that the following SQL
> statement was the
> > responsible for it.
>
> Here's part of the problem:
>
> > Join Filter: ((gra.codcor)::text =
> > ((div.codite)::text || ''::text))
> > -> Hash Join (cost=1.11..3888.04 rows=11
> > width=146) (actual time=15.560..85.376 rows=414 loops=1)
> > Hash Cond: ((gra.codtam)::text =
> > ((sub.codite)::text || ''::text))
>
> Why such bizarre join conditions? Why don't you lose the useless
> concatenations of empty strings and have just a plain equality
> comparison? This technique completely destroys any chance of the
> planner making good estimates of the join result sizes (and the bad
> estimates it's coming out with are part of the problem).
>
> > -> Nested Loop (cost=0.00..68318.52
> rows=647982 width=85)
> > (actual time=0.026..3406.170 rows=643739 loops=414)
> > -> Seq Scan on td_nat nat (cost=0.00..1.24 rows=1
> > width=9) (actual time=0.004..0.014 rows=1 loops=414)
> > Filter: (-3::numeric = codtab)
> > -> Seq Scan on tt_ive ive (cost=0.00..61837.46
> > rows=647982 width=76) (actual time=0.017..1926.983 rows=643739
> loops=414)
> > Filter: ((sitmov <> 'C'::bpchar) AND
> > ('001'::bpchar = codfil))
>
> The other big problem seems to be that it's choosing to do this
> unconstrained join first. I'm not sure about the cause of that,
> but maybe you need to increase join_collapse_limit. What PG version
> is this anyway?
>
> A more general comment, if you are open to schema changes, is that you
> should change all the "numeric(n,0)" fields to integer (or possibly
> smallint or bigint as needed). Particularly the ones that are used as
> join keys, primary keys, foreign keys.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
Carlos H. Reimer wrote: > Hi, > > I have changed the view to eliminate the bizarre concatenation conditions > but even so the response time did not change. Are you sure you did that? In the EXPLAIN it's still possible to see them, for example > -> Nested Loop (cost=1.34..3409.04 rows=1 width=159) > (actual time=0.237..32.520 rows=414 loops=1) > Join Filter: ((gra.codtam)::text = ((sub.codite)::text > || ''::text)) > -> Nested Loop (cost=1.34..3376.84 rows=28 width=136) > (actual time=0.226..20.978 rows=414 loops=1) > -> Hash Join (cost=1.34..3356.99 rows=28 > width=145) (actual time=0.215..15.225 rows=414 loops=1) > Hash Cond: ((gra.codcor)::text = > ((div.codite)::text || ''::text)) -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 "Uno combate cuando es necesario... ¡no cuando está de humor! El humor es para el ganado, o para hacer el amor, o para tocar el baliset. No para combatir." (Gurney Halleck)
Yes, but as the change did not alter the response time I used the original
view.
Anyway here are the response times using the changed view (without the
concatenation conditions):
with join_collapse_limit set to 8:
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------
Nested Loop Left Join (cost=963.68..76116.63 rows=1 width=194) (actual
time=8219.028..1316669.201 rows=256 loops=1)
-> Nested Loop (cost=963.68..76116.23 rows=1 width=198) (actual
time=8196.502..1316638.186 rows=256 loops=1)
Join Filter: ((gra.filmat = ive.filmat) AND (gra.codmat =
ive.codmat) AND (gra.codcor = ive.codcor) AND (gra.codtam = ive.codtam))
-> Nested Loop (cost=1.11..3370.95 rows=1 width=150) (actual
time=33.058..255.428 rows=414 loops=1)
Join Filter: (div.coddiv = ddiv.codtab)
-> Nested Loop (cost=1.11..3369.89 rows=1 width=159)
(actual time=33.043..249.609 rows=414 loops=1)
Join Filter: (sub.codsub = dsub.codtab)
-> Nested Loop (cost=1.11..3368.82 rows=1 width=168)
(actual time=33.026..243.603 rows=414 loops=1)
Join Filter: ((gra.codcor)::text =
(div.codite)::text)
-> Hash Join (cost=1.11..3356.11 rows=9
width=145) (actual time=33.004..222.375 rows=414 loops=1)
Hash Cond: ((gra.codtam)::text =
(sub.codite)::text)
-> Nested Loop (cost=0.00..3352.55
rows=377 width=122) (actual time=32.810..219.046 rows=414 loops=1)
-> Index Scan using i_fk_pro_ddep on
tt_pro pro (cost=0.00..123.83 rows=437 width=76) (actual
time=25.199..118.851 rows=414 loops=1)
Index Cond: (1::numeric =
depart)
-> Index Scan using pk_gra on tt_gra
gra (cost=0.00..7.37 rows=1 width=46) (actual time=0.225..0.231 rows=1
loops=414)
Index Cond: ((pro.filmat =
gra.filmat) AND (pro.codmat = gra.codmat))
-> Hash (cost=1.05..1.05 rows=5 width=32)
(actual time=0.039..0.039 rows=5 loops=1)
-> Seq Scan on tt_sub sub
(cost=0.00..1.05 rows=5 width=32) (actual time=0.009..0.015 rows=5 loops=1)
-> Seq Scan on tt_div div (cost=0.00..1.15
rows=15 width=32) (actual time=0.003..0.015 rows=15 loops=414)
-> Seq Scan on td_sub dsub (cost=0.00..1.03 rows=3
width=9) (actual time=0.002..0.005 rows=3 loops=414)
-> Seq Scan on td_div ddiv (cost=0.00..1.03 rows=3 width=9)
(actual time=0.002..0.005 rows=3 loops=414)
-> Hash Join (cost=962.57..72738.01 rows=363 width=114) (actual
time=0.588..3178.606 rows=857 loops=414)
Hash Cond: (ive.sequen = ven.sequen)
-> Nested Loop (cost=0.00..69305.21 rows=657761 width=85)
(actual time=0.041..2623.627 rows=656152 loops=414)
-> Seq Scan on td_nat nat (cost=0.00..1.24 rows=1
width=9) (actual time=0.004..0.012 rows=1 loops=414)
Filter: (-3::numeric = codtab)
-> Seq Scan on tt_ive ive (cost=0.00..62726.36
rows=657761 width=76) (actual time=0.034..1685.506 rows=656152 loops=414)
Filter: ((sitmov <> 'C'::bpchar) AND
('001'::bpchar = codfil))
-> Hash (cost=960.39..960.39 rows=174 width=89) (actual
time=41.542..41.542 rows=394 loops=1)
-> Hash Left Join (cost=3.48..960.39 rows=174
width=89) (actual time=16.936..40.693 rows=394 loops=1)
Hash Cond: ((ven.filcli = cfg.vc_filcli) AND
(ven.codcli = cfg.vc_codcli))
-> Hash Join (cost=2.45..958.05 rows=174
width=106) (actual time=16.895..39.747 rows=394 loops=1)
Hash Cond: ((ven.filpgt = pla.filpgt) AND
(ven.codpgt = pla.codpgt))
-> Index Scan using i_lc_ven_dathor on
tt_ven ven (cost=0.00..952.56 rows=174 width=106) (actual
time=16.797..38.626 rows=394 loops=1)
Index Cond: ((dathor >= '2007-07-12
00:00:00'::timestamp without time zone) AND (dathor <= '2007-07-12
23:59:59'::timestamp without time zone))
Filter: (('001'::bpchar = codfil) AND
(codnat = -3::numeric))
-> Hash (cost=2.18..2.18 rows=18
width=14) (actual time=0.073..0.073 rows=18 loops=1)
-> Seq Scan on tt_pla pla
(cost=0.00..2.18 rows=18 width=14) (actual time=0.017..0.039 rows=18
loops=1)
-> Hash (cost=1.01..1.01 rows=1 width=17)
(actual time=0.020..0.020 rows=1 loops=1)
-> Seq Scan on tt_cfg cfg
(cost=0.00..1.01 rows=1 width=17) (actual time=0.010..0.011 rows=1 loops=1)
-> Index Scan using pk_pla on tt_pla vencodpgt (cost=0.00..0.31 rows=1
width=24) (actual time=0.099..0.101 rows=1 loops=256)
Index Cond: ((ven.filpgt = vencodpgt.filpgt) AND (ven.codpgt =
vencodpgt.codpgt))
Total runtime: 1316670.331 ms
(43 rows)
with join_collapse_limit set to 1:
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------------
Nested Loop Left Join (cost=1106.16..25547.95 rows=1 width=195) (actual
time=2363.202..9534.955 rows=256 loops=1)
Join Filter: ((ven.filpgt = vencodpgt.filpgt) AND (ven.codpgt =
vencodpgt.codpgt))
-> Nested Loop (cost=1106.16..25545.43 rows=1 width=199) (actual
time=2363.117..9521.704 rows=256 loops=1)
Join Filter: ((gra.filmat = ive.filmat) AND (gra.codmat =
ive.codmat) AND (gra.codcor = ive.codcor) AND (gra.codtam = ive.codtam))
-> Nested Loop (cost=1.34..2576.72 rows=1 width=151) (actual
time=154.268..1054.391 rows=414 loops=1)
Join Filter: (sub.codsub = dsub.codtab)
-> Nested Loop (cost=1.34..2575.65 rows=1 width=160)
(actual time=138.588..1032.830 rows=414 loops=1)
Join Filter: ((gra.codtam)::text = (sub.codite)::text)
-> Nested Loop (cost=1.34..2551.77 rows=21 width=137)
(actual time=134.262..1018.756 rows=414 loops=1)
-> Hash Join (cost=1.34..2533.88 rows=21
width=146) (actual time=116.724..996.297 rows=414 loops=1)
Hash Cond: ((gra.codcor)::text =
(div.codite)::text)
-> Nested Loop (cost=0.00..2530.60
rows=278 width=123) (actual time=106.879..983.761 rows=414 loops=1)
-> Index Scan using i_fk_pro_ddep on
tt_pro pro (cost=0.00..108.20 rows=318 width=77) (actual
time=44.303..286.618 rows=414 loops=1)
Index Cond: (1::numeric =
depart)
-> Index Scan using pk_gra on tt_gra
gra (cost=0.00..7.60 rows=1 width=46) (actual time=1.674..1.676 rows=1
loops=414)
Index Cond: ((pro.filmat =
gra.filmat) AND (pro.codmat = gra.codmat))
-> Hash (cost=1.15..1.15 rows=15
width=32) (actual time=9.824..9.824 rows=15 loops=1)
-> Seq Scan on tt_div div
(cost=0.00..1.15 rows=15 width=32) (actual time=9.774..9.788 rows=15
loops=1)
-> Index Scan using pk_ddiv on td_div ddiv
(cost=0.00..0.84 rows=1 width=9) (actual time=0.047..0.049 rows=1 loops=414)
Index Cond: (div.coddiv = ddiv.codtab)
-> Seq Scan on tt_sub sub (cost=0.00..1.05 rows=5
width=32) (actual time=0.013..0.017 rows=5 loops=414)
-> Seq Scan on td_sub dsub (cost=0.00..1.03 rows=3 width=9)
(actual time=0.040..0.043 rows=3 loops=414)
-> Nested Loop (cost=1104.83..22960.29 rows=421 width=114)
(actual time=0.727..19.609 rows=857 loops=414)
-> Nested Loop (cost=1104.83..1112.46 rows=200 width=80)
(actual time=0.559..3.497 rows=394 loops=414)
-> Merge Join (cost=1103.59..1107.22 rows=200
width=89) (actual time=0.532..1.751 rows=394 loops=414)
Merge Cond: ((pla.codpgt = ven.codpgt) AND
(pla.filpgt = ven.filpgt))
-> Sort (cost=2.56..2.60 rows=18 width=14)
(actual time=0.019..0.025 rows=8 loops=414)
Sort Key: pla.codpgt, pla.filpgt
-> Seq Scan on tt_pla pla
(cost=0.00..2.18 rows=18 width=14) (actual time=7.430..7.613 rows=18
loops=1)
-> Sort (cost=1101.03..1101.53 rows=200
width=89) (actual time=0.508..0.805 rows=394 loops=414)
Sort Key: ven.codpgt, ven.filpgt
-> Nested Loop Left Join
(cost=1.01..1093.39 rows=200 width=89) (actual time=39.399..209.096 rows=394
loops=1)
Join Filter: ((ven.filcli =
cfg.vc_filcli) AND (ven.codcli = cfg.vc_codcli))
-> Index Scan using i_lc_ven_dathor
on tt_ven ven (cost=0.00..1087.38 rows=200 width=106) (actual
time=39.378..207.111 rows=394 loops=1)
Index Cond: ((dathor >=
'2007-07-12 00:00:00'::timestamp without time zone) AND (dathor <=
'2007-07-12 23:59:59'::timestamp without time zone))
Filter: (('001'::bpchar =
codfil) AND (codnat = -3::numeric))
-> Materialize (cost=1.01..1.02
rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=394)
-> Seq Scan on tt_cfg cfg
(cost=0.00..1.01 rows=1 width=17) (actual time=0.006..0.008 rows=1 loops=1)
-> Materialize (cost=1.24..1.25 rows=1 width=9)
(actual time=0.001..0.002 rows=1 loops=163116)
-> Seq Scan on td_nat nat (cost=0.00..1.24
rows=1 width=9) (actual time=9.994..10.001 rows=1 loops=1)
Filter: (-3::numeric = codtab)
-> Index Scan using pk_ive on tt_ive ive (cost=0.00..108.86
rows=30 width=76) (actual time=0.020..0.036 rows=2 loops=163116)
Index Cond: (('001'::bpchar = ive.codfil) AND
(ive.sequen = ven.sequen))
Filter: (sitmov <> 'C'::bpchar)
-> Seq Scan on tt_pla vencodpgt (cost=0.00..2.18 rows=18 width=24)
(actual time=0.002..0.017 rows=18 loops=256)
Total runtime: 9546.971 ms
(46 rows)
> -----Mensagem original-----
> De: Alvaro Herrera [mailto:alvherre@commandprompt.com]
> Enviada em: quarta-feira, 1 de agosto de 2007 13:53
> Para: Carlos H. Reimer
> Cc: Tom Lane; pgsql-performance@postgresql.org
> Assunto: Re: RES: [PERFORM] Improving select peformance
>
>
> Carlos H. Reimer wrote:
> > Hi,
> >
> > I have changed the view to eliminate the bizarre concatenation
> conditions
> > but even so the response time did not change.
>
> Are you sure you did that? In the EXPLAIN it's still possible to see
> them, for example
>
> > -> Nested Loop (cost=1.34..3409.04 rows=1 width=159)
> > (actual time=0.237..32.520 rows=414 loops=1)
> > Join Filter: ((gra.codtam)::text =
> ((sub.codite)::text
> > || ''::text))
> > -> Nested Loop (cost=1.34..3376.84
> rows=28 width=136)
> > (actual time=0.226..20.978 rows=414 loops=1)
> > -> Hash Join (cost=1.34..3356.99 rows=28
> > width=145) (actual time=0.215..15.225 rows=414 loops=1)
> > Hash Cond: ((gra.codcor)::text =
> > ((div.codite)::text || ''::text))
>
>
> --
> Alvaro Herrera
> http://www.amazon.com/gp/registry/CTMLCN8V17R4
> "Uno combate cuando es necesario... ¡no cuando está de humor!
> El humor es para el ganado, o para hacer el amor, o para tocar el
> baliset. No para combatir." (Gurney Halleck)
Hi,
In this case, I believe the best choice to improve the performance of this
particular SQL statement is adding the 'set join_collapse_limit = 1;' just
before the join statement, correct?
It there anything else we could do to, in this case, make the planner choose
better paths using the default join_collapse_limit?
Thank you in advance!
Reimer
> -----Mensagem original-----
> De: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]Em nome de Carlos H.
> Reimer
> Enviada em: quarta-feira, 1 de agosto de 2007 21:26
> Para: Alvaro Herrera
> Cc: Tom Lane; pgsql-performance@postgresql.org
> Assunto: RES: RES: [PERFORM] Improving select peformance
>
>
> Yes, but as the change did not alter the response time I used the original
> view.
>
> Anyway here are the response times using the changed view (without the
> concatenation conditions):
>
> with join_collapse_limit set to 8:
> ------------------------------------------------------------------
> ----------
> ------------------------------------------------------------------
> ----------
> -------------------------------
> Nested Loop Left Join (cost=963.68..76116.63 rows=1 width=194) (actual
> time=8219.028..1316669.201 rows=256 loops=1)
> -> Nested Loop (cost=963.68..76116.23 rows=1 width=198) (actual
> time=8196.502..1316638.186 rows=256 loops=1)
> Join Filter: ((gra.filmat = ive.filmat) AND (gra.codmat =
> ive.codmat) AND (gra.codcor = ive.codcor) AND (gra.codtam = ive.codtam))
> -> Nested Loop (cost=1.11..3370.95 rows=1 width=150) (actual
> time=33.058..255.428 rows=414 loops=1)
> Join Filter: (div.coddiv = ddiv.codtab)
> -> Nested Loop (cost=1.11..3369.89 rows=1 width=159)
> (actual time=33.043..249.609 rows=414 loops=1)
> Join Filter: (sub.codsub = dsub.codtab)
> -> Nested Loop (cost=1.11..3368.82 rows=1
> width=168)
> (actual time=33.026..243.603 rows=414 loops=1)
> Join Filter: ((gra.codcor)::text =
> (div.codite)::text)
> -> Hash Join (cost=1.11..3356.11 rows=9
> width=145) (actual time=33.004..222.375 rows=414 loops=1)
> Hash Cond: ((gra.codtam)::text =
> (sub.codite)::text)
> -> Nested Loop (cost=0.00..3352.55
> rows=377 width=122) (actual time=32.810..219.046 rows=414 loops=1)
> -> Index Scan using
> i_fk_pro_ddep on
> tt_pro pro (cost=0.00..123.83 rows=437 width=76) (actual
> time=25.199..118.851 rows=414 loops=1)
> Index Cond: (1::numeric =
> depart)
> -> Index Scan using
> pk_gra on tt_gra
> gra (cost=0.00..7.37 rows=1 width=46) (actual time=0.225..0.231 rows=1
> loops=414)
> Index Cond: ((pro.filmat =
> gra.filmat) AND (pro.codmat = gra.codmat))
> -> Hash (cost=1.05..1.05
> rows=5 width=32)
> (actual time=0.039..0.039 rows=5 loops=1)
> -> Seq Scan on tt_sub sub
> (cost=0.00..1.05 rows=5 width=32) (actual time=0.009..0.015
> rows=5 loops=1)
> -> Seq Scan on tt_div div (cost=0.00..1.15
> rows=15 width=32) (actual time=0.003..0.015 rows=15 loops=414)
> -> Seq Scan on td_sub dsub (cost=0.00..1.03 rows=3
> width=9) (actual time=0.002..0.005 rows=3 loops=414)
> -> Seq Scan on td_div ddiv (cost=0.00..1.03
> rows=3 width=9)
> (actual time=0.002..0.005 rows=3 loops=414)
> -> Hash Join (cost=962.57..72738.01 rows=363 width=114) (actual
> time=0.588..3178.606 rows=857 loops=414)
> Hash Cond: (ive.sequen = ven.sequen)
> -> Nested Loop (cost=0.00..69305.21 rows=657761 width=85)
> (actual time=0.041..2623.627 rows=656152 loops=414)
> -> Seq Scan on td_nat nat (cost=0.00..1.24 rows=1
> width=9) (actual time=0.004..0.012 rows=1 loops=414)
> Filter: (-3::numeric = codtab)
> -> Seq Scan on tt_ive ive (cost=0.00..62726.36
> rows=657761 width=76) (actual time=0.034..1685.506 rows=656152 loops=414)
> Filter: ((sitmov <> 'C'::bpchar) AND
> ('001'::bpchar = codfil))
> -> Hash (cost=960.39..960.39 rows=174 width=89) (actual
> time=41.542..41.542 rows=394 loops=1)
> -> Hash Left Join (cost=3.48..960.39 rows=174
> width=89) (actual time=16.936..40.693 rows=394 loops=1)
> Hash Cond: ((ven.filcli = cfg.vc_filcli) AND
> (ven.codcli = cfg.vc_codcli))
> -> Hash Join (cost=2.45..958.05 rows=174
> width=106) (actual time=16.895..39.747 rows=394 loops=1)
> Hash Cond: ((ven.filpgt = pla.filpgt) AND
> (ven.codpgt = pla.codpgt))
> -> Index Scan using i_lc_ven_dathor on
> tt_ven ven (cost=0.00..952.56 rows=174 width=106) (actual
> time=16.797..38.626 rows=394 loops=1)
> Index Cond: ((dathor >= '2007-07-12
> 00:00:00'::timestamp without time zone) AND (dathor <= '2007-07-12
> 23:59:59'::timestamp without time zone))
> Filter: (('001'::bpchar =
> codfil) AND
> (codnat = -3::numeric))
> -> Hash (cost=2.18..2.18 rows=18
> width=14) (actual time=0.073..0.073 rows=18 loops=1)
> -> Seq Scan on tt_pla pla
> (cost=0.00..2.18 rows=18 width=14) (actual time=0.017..0.039 rows=18
> loops=1)
> -> Hash (cost=1.01..1.01 rows=1 width=17)
> (actual time=0.020..0.020 rows=1 loops=1)
> -> Seq Scan on tt_cfg cfg
> (cost=0.00..1.01 rows=1 width=17) (actual time=0.010..0.011
> rows=1 loops=1)
> -> Index Scan using pk_pla on tt_pla vencodpgt
> (cost=0.00..0.31 rows=1
> width=24) (actual time=0.099..0.101 rows=1 loops=256)
> Index Cond: ((ven.filpgt = vencodpgt.filpgt) AND (ven.codpgt =
> vencodpgt.codpgt))
> Total runtime: 1316670.331 ms
> (43 rows)
>
>
>
> with join_collapse_limit set to 1:
>
>
> QUERY PLAN
> ------------------------------------------------------------------
> ----------
> ------------------------------------------------------------------
> ----------
> -------------------------------------
> Nested Loop Left Join (cost=1106.16..25547.95 rows=1 width=195) (actual
> time=2363.202..9534.955 rows=256 loops=1)
> Join Filter: ((ven.filpgt = vencodpgt.filpgt) AND (ven.codpgt =
> vencodpgt.codpgt))
> -> Nested Loop (cost=1106.16..25545.43 rows=1 width=199) (actual
> time=2363.117..9521.704 rows=256 loops=1)
> Join Filter: ((gra.filmat = ive.filmat) AND (gra.codmat =
> ive.codmat) AND (gra.codcor = ive.codcor) AND (gra.codtam = ive.codtam))
> -> Nested Loop (cost=1.34..2576.72 rows=1 width=151) (actual
> time=154.268..1054.391 rows=414 loops=1)
> Join Filter: (sub.codsub = dsub.codtab)
> -> Nested Loop (cost=1.34..2575.65 rows=1 width=160)
> (actual time=138.588..1032.830 rows=414 loops=1)
> Join Filter: ((gra.codtam)::text =
> (sub.codite)::text)
> -> Nested Loop (cost=1.34..2551.77 rows=21
> width=137)
> (actual time=134.262..1018.756 rows=414 loops=1)
> -> Hash Join (cost=1.34..2533.88 rows=21
> width=146) (actual time=116.724..996.297 rows=414 loops=1)
> Hash Cond: ((gra.codcor)::text =
> (div.codite)::text)
> -> Nested Loop (cost=0.00..2530.60
> rows=278 width=123) (actual time=106.879..983.761 rows=414 loops=1)
> -> Index Scan using
> i_fk_pro_ddep on
> tt_pro pro (cost=0.00..108.20 rows=318 width=77) (actual
> time=44.303..286.618 rows=414 loops=1)
> Index Cond: (1::numeric =
> depart)
> -> Index Scan using
> pk_gra on tt_gra
> gra (cost=0.00..7.60 rows=1 width=46) (actual time=1.674..1.676 rows=1
> loops=414)
> Index Cond: ((pro.filmat =
> gra.filmat) AND (pro.codmat = gra.codmat))
> -> Hash (cost=1.15..1.15 rows=15
> width=32) (actual time=9.824..9.824 rows=15 loops=1)
> -> Seq Scan on tt_div div
> (cost=0.00..1.15 rows=15 width=32) (actual time=9.774..9.788 rows=15
> loops=1)
> -> Index Scan using pk_ddiv on td_div ddiv
> (cost=0.00..0.84 rows=1 width=9) (actual time=0.047..0.049 rows=1
> loops=414)
> Index Cond: (div.coddiv = ddiv.codtab)
> -> Seq Scan on tt_sub sub (cost=0.00..1.05 rows=5
> width=32) (actual time=0.013..0.017 rows=5 loops=414)
> -> Seq Scan on td_sub dsub (cost=0.00..1.03
> rows=3 width=9)
> (actual time=0.040..0.043 rows=3 loops=414)
> -> Nested Loop (cost=1104.83..22960.29 rows=421 width=114)
> (actual time=0.727..19.609 rows=857 loops=414)
> -> Nested Loop (cost=1104.83..1112.46 rows=200 width=80)
> (actual time=0.559..3.497 rows=394 loops=414)
> -> Merge Join (cost=1103.59..1107.22 rows=200
> width=89) (actual time=0.532..1.751 rows=394 loops=414)
> Merge Cond: ((pla.codpgt = ven.codpgt) AND
> (pla.filpgt = ven.filpgt))
> -> Sort (cost=2.56..2.60 rows=18 width=14)
> (actual time=0.019..0.025 rows=8 loops=414)
> Sort Key: pla.codpgt, pla.filpgt
> -> Seq Scan on tt_pla pla
> (cost=0.00..2.18 rows=18 width=14) (actual time=7.430..7.613 rows=18
> loops=1)
> -> Sort (cost=1101.03..1101.53 rows=200
> width=89) (actual time=0.508..0.805 rows=394 loops=414)
> Sort Key: ven.codpgt, ven.filpgt
> -> Nested Loop Left Join
> (cost=1.01..1093.39 rows=200 width=89) (actual
> time=39.399..209.096 rows=394
> loops=1)
> Join Filter: ((ven.filcli =
> cfg.vc_filcli) AND (ven.codcli = cfg.vc_codcli))
> -> Index Scan using
> i_lc_ven_dathor
> on tt_ven ven (cost=0.00..1087.38 rows=200 width=106) (actual
> time=39.378..207.111 rows=394 loops=1)
> Index Cond: ((dathor >=
> '2007-07-12 00:00:00'::timestamp without time zone) AND (dathor <=
> '2007-07-12 23:59:59'::timestamp without time zone))
> Filter: (('001'::bpchar =
> codfil) AND (codnat = -3::numeric))
> -> Materialize (cost=1.01..1.02
> rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=394)
> -> Seq Scan on tt_cfg cfg
> (cost=0.00..1.01 rows=1 width=17) (actual time=0.006..0.008
> rows=1 loops=1)
> -> Materialize (cost=1.24..1.25 rows=1 width=9)
> (actual time=0.001..0.002 rows=1 loops=163116)
> -> Seq Scan on td_nat nat (cost=0.00..1.24
> rows=1 width=9) (actual time=9.994..10.001 rows=1 loops=1)
> Filter: (-3::numeric = codtab)
> -> Index Scan using pk_ive on tt_ive ive
> (cost=0.00..108.86
> rows=30 width=76) (actual time=0.020..0.036 rows=2 loops=163116)
> Index Cond: (('001'::bpchar = ive.codfil) AND
> (ive.sequen = ven.sequen))
> Filter: (sitmov <> 'C'::bpchar)
> -> Seq Scan on tt_pla vencodpgt (cost=0.00..2.18 rows=18 width=24)
> (actual time=0.002..0.017 rows=18 loops=256)
> Total runtime: 9546.971 ms
> (46 rows)
>
>
> > -----Mensagem original-----
> > De: Alvaro Herrera [mailto:alvherre@commandprompt.com]
> > Enviada em: quarta-feira, 1 de agosto de 2007 13:53
> > Para: Carlos H. Reimer
> > Cc: Tom Lane; pgsql-performance@postgresql.org
> > Assunto: Re: RES: [PERFORM] Improving select peformance
> >
> >
> > Carlos H. Reimer wrote:
> > > Hi,
> > >
> > > I have changed the view to eliminate the bizarre concatenation
> > conditions
> > > but even so the response time did not change.
> >
> > Are you sure you did that? In the EXPLAIN it's still possible to see
> > them, for example
> >
> > > -> Nested Loop (cost=1.34..3409.04 rows=1 width=159)
> > > (actual time=0.237..32.520 rows=414 loops=1)
> > > Join Filter: ((gra.codtam)::text =
> > ((sub.codite)::text
> > > || ''::text))
> > > -> Nested Loop (cost=1.34..3376.84
> > rows=28 width=136)
> > > (actual time=0.226..20.978 rows=414 loops=1)
> > > -> Hash Join (cost=1.34..3356.99 rows=28
> > > width=145) (actual time=0.215..15.225 rows=414 loops=1)
> > > Hash Cond: ((gra.codcor)::text =
> > > ((div.codite)::text || ''::text))
> >
> >
> > --
> > Alvaro Herrera
> > http://www.amazon.com/gp/registry/CTMLCN8V17R4
> > "Uno combate cuando es necesario... ¡no cuando está de humor!
> > El humor es para el ganado, o para hacer el amor, o para tocar el
> > baliset. No para combatir." (Gurney Halleck)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
"Carlos H. Reimer" <carlos.reimer@opendb.com.br> writes:
> In this case, I believe the best choice to improve the performance of this
> particular SQL statement is adding the 'set join_collapse_limit = 1;' just
> before the join statement, correct?
That's a mighty blunt instrument. The real problem with your query is
the misestimation of the join sizes --- are you sure the table
statistics are up to date? Maybe you'd get better estimates with more
statistics (ie, increase the stats target for these tables).
regards, tom lane
Hi,
Thanks for the suggestions but apparently the problem in another place.
I have changed the default_statistics_target from to 1000 but the result is
pretty much the same as with when it was 10.
After the change the database was vacuumed and analyzed.
Let me know if I miss anything.
Is there anything else we could try to identify why the planner is making
this choice?
Thank you in advance!
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------
Nested Loop Left Join (cost=1293.06..77117.75 rows=1 width=193) (actual
time=8623.464..1317305.299 rows=256 loops=1)
-> Nested Loop (cost=1293.06..77117.36 rows=1 width=197) (actual
time=8607.108..1317280.517 rows=256 loops=1)
Join Filter: ((gra.filmat = ive.filmat) AND (gra.codmat =
ive.codmat) AND (gra.codcor = ive.codcor) AND (gra.codtam = ive.codtam))
-> Nested Loop (cost=1.11..3223.73 rows=1 width=149) (actual
time=127.296..1592.118 rows=414 loops=1)
Join Filter: (div.coddiv = ddiv.codtab)
-> Nested Loop (cost=1.11..3222.67 rows=1 width=158)
(actual time=113.482..1572.752 rows=414 loops=1)
Join Filter: (sub.codsub = dsub.codtab)
-> Nested Loop (cost=1.11..3221.60 rows=1 width=167)
(actual time=108.122..1561.692 rows=414 loops=1)
Join Filter: ((gra.codcor)::text =
(div.codite)::text)
-> Hash Join (cost=1.11..3208.89 rows=9
width=144) (actual time=99.794..1532.498 rows=414 loops=1)
Hash Cond: ((gra.codtam)::text =
(sub.codite)::text)
-> Nested Loop (cost=0.00..3205.49
rows=351 width=121) (actual time=80.811..1510.179 rows=414 loops=1)
-> Index Scan using i_fk_pro_ddep on
tt_pro pro (cost=0.00..128.18 rows=414 width=75) (actual
time=35.525..353.854 rows=414 loops=1)
Index Cond: (1::numeric =
depart)
-> Index Scan using pk_gra on tt_gra
gra (cost=0.00..7.42 rows=1 width=46) (actual time=2.776..2.782 rows=1
loops=414)
Index Cond: ((pro.filmat =
gra.filmat) AND (pro.codmat = gra.codmat))
-> Hash (cost=1.05..1.05 rows=5 width=32)
(actual time=6.510..6.510 rows=5 loops=1)
-> Seq Scan on tt_sub sub
(cost=0.00..1.05 rows=5 width=32) (actual time=6.479..6.485 rows=5 loops=1)
-> Seq Scan on tt_div div (cost=0.00..1.15
rows=15 width=32) (actual time=0.023..0.034 rows=15 loops=414)
-> Seq Scan on td_sub dsub (cost=0.00..1.03 rows=3
width=9) (actual time=0.015..0.018 rows=3 loops=414)
-> Seq Scan on td_div ddiv (cost=0.00..1.03 rows=3 width=9)
(actual time=0.035..0.038 rows=3 loops=414)
-> Hash Join (cost=1291.94..73883.89 rows=487 width=114) (actual
time=1.241..3176.965 rows=857 loops=414)
Hash Cond: (ive.sequen = ven.sequen)
-> Nested Loop (cost=0.00..70110.52 rows=660415 width=85)
(actual time=0.038..2621.327 rows=658236 loops=414)
-> Seq Scan on td_nat nat (cost=0.00..1.24 rows=1
width=9) (actual time=0.006..0.015 rows=1 loops=414)
Filter: (-3::numeric = codtab)
-> Seq Scan on tt_ive ive (cost=0.00..63505.13
rows=660415 width=76) (actual time=0.029..1681.597 rows=658236 loops=414)
Filter: ((sitmov <> 'C'::bpchar) AND
('001'::bpchar = codfil))
-> Hash (cost=1289.03..1289.03 rows=233 width=89) (actual
time=307.760..307.760 rows=394 loops=1)
-> Hash Left Join (cost=3.48..1289.03 rows=233
width=89) (actual time=61.851..306.897 rows=394 loops=1)
Hash Cond: ((ven.filcli = cfg.vc_filcli) AND
(ven.codcli = cfg.vc_codcli))
-> Hash Join (cost=2.45..1286.25 rows=233
width=106) (actual time=61.802..305.928 rows=394 loops=1)
Hash Cond: ((ven.filpgt = pla.filpgt) AND
(ven.codpgt = pla.codpgt))
-> Index Scan using i_lc_ven_dathor on
tt_ven ven (cost=0.00..1279.72 rows=233 width=106) (actual
time=53.539..296.648 rows=394 loops=1)
Index Cond: ((dathor >= '2007-07-12
00:00:00'::timestamp without time zone) AND (dathor <= '2007-07-12
23:59:59'::timestamp without time zone))
Filter: (('001'::bpchar = codfil) AND
(codnat = -3::numeric))
-> Hash (cost=2.18..2.18 rows=18
width=14) (actual time=8.237..8.237 rows=18 loops=1)
-> Seq Scan on tt_pla pla
(cost=0.00..2.18 rows=18 width=14) (actual time=8.162..8.205 rows=18
loops=1)
-> Hash (cost=1.01..1.01 rows=1 width=17)
(actual time=0.029..0.029 rows=1 loops=1)
-> Seq Scan on tt_cfg cfg
(cost=0.00..1.01 rows=1 width=17) (actual time=0.018..0.019 rows=1 loops=1)
-> Index Scan using pk_pla on tt_pla vencodpgt (cost=0.00..0.30 rows=1
width=24) (actual time=0.075..0.076 rows=1 loops=256)
Index Cond: ((ven.filpgt = vencodpgt.filpgt) AND (ven.codpgt =
vencodpgt.codpgt))
Total runtime: 1317306.468 ms
(43 rows)
Reimer
> -----Mensagem original-----
> De: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]Em nome de Tom Lane
> Enviada em: quinta-feira, 2 de agosto de 2007 23:13
> Para: carlos.reimer@opendb.com.br
> Cc: Alvaro Herrera; pgsql-performance@postgresql.org
> Assunto: Re: RES: RES: [PERFORM] Improving select peformance
>
>
> "Carlos H. Reimer" <carlos.reimer@opendb.com.br> writes:
> > In this case, I believe the best choice to improve the
> performance of this
> > particular SQL statement is adding the 'set join_collapse_limit
> = 1;' just
> > before the join statement, correct?
>
> That's a mighty blunt instrument. The real problem with your query is
> the misestimation of the join sizes --- are you sure the table
> statistics are up to date? Maybe you'd get better estimates with more
> statistics (ie, increase the stats target for these tables).
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend