From: Tom Lane <tgl@sss.pgh.pa.us>
To: alexandre :: aldeia digital <alepaes@aldeiadigital.com.br>
Cc: pgsql-performance@postgresql.org
Subject: Re: 30-70 seconds query...
Date: Mon, 31 Mar 2003 17:17:20 -0500
Message-ID: <25610.1049149040@sss.pgh.pa.us> (raw)
In-Reply-To: <10846.200.225.202.15.1049145207.squirrel@webmail.ad2.com.br>
References: <10846.200.225.202.15.1049145207.squirrel@webmail.ad2.com.br>
"alexandre :: aldeia digital" <alepaes@aldeiadigital.com.br> writes:
> I use a case tool and we generate the querys automatically.
> explain analyze SELECT T2.fi08ufemp, T4.es10almtra, T3.fi08MovEst,
> T1.es10qtdgra, T1.es10Tamanh, T1.es10item, T1.es10numdoc, T1.fi08codigo,
> T1.es10tipdoc, T1.es10codemp, T4.es10codalm, T4.es10empa, T1.es10datlan,
> T4.co13CodPro, T4.co13Emp06, T1.es10EmpTam FROM (((ES10T2 T1 LEFT JOIN
> ES10T T2 ON T2.es10codemp = T1.es10codemp AND T2.es10datlan =
> T1.es10datlan AND T2.es10tipdoc = T1.es10tipdoc AND T2.fi08codigo =
> T1.fi08codigo AND T2.es10numdoc = T1.es10numdoc) LEFT JOIN FI08T T3 ON
> T3.fi08ufemp = T2.fi08ufemp AND T3.fi08codigo =T1.fi08codigo) LEFT JOIN
> ES10T1 T4 ON T4.es10codemp = T1.es10codemp AND T4.es10datlan =
> T1.es10datlan AND T4.es10tipdoc = T1.es10tipdoc AND T4.fi08codigo =
> T1.fi08codigo AND T4.es10numdoc = T1.es10numdoc AND T4.es10item =
> T1.es10item) WHERE ( T4.co13Emp06 = '1' AND T4.co13CodPro = '16998' AND
> T1.es10datlan >= '2003-02-01'::date ) AND ( T1.es10datlan >=
> '2003-02-01'::date) AND ( T3.fi08MovEst = 'S' ) AND ( T4.es10empa = '1' OR
> ( '1' = 0 ) ) AND ( T4.es10codalm = '0' OR T4.es10almtra = '0' OR ( '0'
> = 0 ) ) AND ( T1.es10datlan <= '2003-02-28'::date ) ORDER BY
> T4.co13Emp06, T4.co13CodPro, T1.es10datlan, T4.es10empa, T4.es10codalm,
> T4.es10almtra, T1.es10codemp, T1.es10tipdoc, T1.fi08codigo,
> T1.es10numdoc, T1.es10item;
Your CASE tool isn't doing you any favors, is it :-(.
Mostly you need to rearrange the JOIN order into something more efficient.
I'd guess that joining T1 to T4, then to T3, then to T2 would be the
way to go here. Also, some study of the WHERE conditions proves that
all the LEFT JOINs could be reduced to plain joins, because any
null-extended row will get discarded by WHERE anyway. That would be a
good thing to do to give the planner more flexibility.
PG 7.4 will be better prepared to handle this sort of query, but I don't
think it will realize that the T1/T2 left join could be reduced to a
plain join given these conditions (that requires observing that null T2
will lead to null T3 because of the join condition... hmmm, I wonder how
practical that would be...). Without that deduction, the key step of
deciding to join T1/T4 first isn't reachable.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, alepaes@aldeiadigital.com.br
Subject: Re: 30-70 seconds query...
In-Reply-To: <25610.1049149040@sss.pgh.pa.us>
References: <10846.200.225.202.15.1049145207.squirrel@webmail.ad2.com.br> <25610.1049149040@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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