RES: RES: 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)