pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedImproving select peformance
2+ messages / 2 participants
[nested] [flat]
* Improving select peformance
@ 2007-07-20 00:41 Carlos H. Reimer <carlos.reimer@opendb.com.br>
0 siblings, 1 reply; 2+ messages in thread
From: Carlos H. Reimer @ 2007-07-20 00:41 UTC (permalink / raw)
To: pgsql-performance
Hi,
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.
I would appreciate any suggestions to improve performance of it.
Thank you very much in advance!
____________________________________________________________________________
_________________________________________________
explain analyze select (VEN.DOCUME)::varchar(13) as COLUNA0,
(VENCODPGT.APEPGT)::varchar(9) as COLUNA1,
(COALESCE(COALESCE(VEN.VLRLIQ,0) * (CASE VEN.VLRNOT WHEN 0
THEN 0 ELSE IVE.VLRMOV / VEN.VLRNOT END),0)) as COLUNA2,
(COALESCE(IVE.QTDMOV,0)) as COLUNA3,
(VIPR.NOMPRO)::varchar(83) as COLUNA4,
(VIPR.REFPRO)::varchar(20) as COLUNA5
from TV_VEN VEN
inner join TT_IVE IVE ON IVE.SEQUEN = VEN.SEQUEN and
IVE.CODFIL = VEN.CODFIL
inner join TV_IPR VIPR ON VIPR.FILMAT = IVE.FILMAT and
VIPR.CODMAT = IVE.CODMAT and
VIPR.CODCOR = IVE.CODCOR and
VIPR.CODTAM = IVE.CODTAM
left join TT_PLA VENCODPGT ON VEN.FILPGT = VENCODPGT.FILPGT AND
VEN.CODPGT = VENCODPGT.CODPGT
where ('001' = VEN.CODFIL)
and VEN.DATHOR between '07/12/2007 00:00:00' and '07/12/2007
23:59:59'
and (VEN.CODNAT = '-3')
and IVE.SITMOV <> 'C'
and ('1' = VIPR.DEPART) ;
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------
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)
____________________________________________________________________________
_________________________________________________________
Table and view definitions can be accessed at:
http://www.opendb.com.br/v1/problem0707.txt
Reimer
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Improving select peformance
@ 2007-07-20 01:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Carlos H. Reimer <carlos.reimer@opendb.com.br>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2007-07-20 01:31 UTC (permalink / raw)
To: carlos.reimer@opendb.com.br; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-07-20 01:31 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-20 00:41 Improving select peformance Carlos H. Reimer <carlos.reimer@opendb.com.br>
2007-07-20 01:31 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox