RES: RES: Improving select peformance - Mailing list pgsql-performance

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