RES: RES: RES: Improving select peformance

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