RES: Improving select peformance

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