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@xxxxxxxxxxxxxx
> [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx]Em nome de Tom Lane
> Enviada em: quinta-feira, 19 de julho de 2007 22:31
> Para: carlos.reimer@xxxxxxxxxxxxx
> Cc: pgsql-performance@xxxxxxxxxxxxxx
> Assunto: Re: Improving select peformance
>
>
> "Carlos H. Reimer" <carlos.reimer@xxxxxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
