From: Tom Lane <tgl@sss.pgh.pa.us>
To: Preeti Ambardar <pambardar@networkprograms.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Optimization
Date: Mon, 22 Dec 2003 18:07:02 -0500
Message-ID: <19011.1072134422@sss.pgh.pa.us> (raw)
In-Reply-To: <000c01c3c466$c247f5b0$5acb09c0@pambardar>
References: <000c01c3c466$c247f5b0$5acb09c0@pambardar>
"Preeti Ambardar" <pambardar@networkprograms.com> writes:
> SELECT ...
> FROM mstuserprofiles cpuser RIGHT OUTER JOIN mstcompanies
> ON cpuser.fktrdid_usptradhouseid = mstcompanies.fktrdid_cmptradhouseid
> AND cpuser.pkuspid = mstcompanies.cmpcontact_userid
> LEFT OUTER JOIN mstcurrency
> ON mstcompanies.fktrdid_cmptradhouseid = mstcurrency.fktrdid_curtradhouseid
> AND mstcompanies.fkcurid_cmpcurid = mstcurrency.pkcurid
> LEFT OUTER JOIN mstproducts
> ON mstcompanies.fktrdid_cmptradhouseid = mstproducts.fktrdid_prdtradhouseid
> AND mstcompanies.cmpmain_product = mstproducts.pkprdid
> RIGHT OUTER JOIN mstuserprofiles
> LEFT OUTER JOIN mstfacilityviews
> ON mstuserprofiles.uspdefaultfacility = mstfacilityviews.pkfacid
> ON mstcompanies.pkcmpid = mstuserprofiles.uspdefaultcounterparty
> AND mstcompanies.fktrdid_cmptradhouseid = mstuserprofiles.fktrdid_usptradhouseid
> WHERE mstuserprofiles.pkuspid = 101 AND mstuserprofiles.fktrdid_usptradhouseid = 1
Ugh :-(
7.4 will do a better job with this than previous releases, but you are
still going to be up against the fact that outer joins constrain the
join order. See
http://www.postgresql.org/docs/7.4/static/explicit-joins.html
You may need to revise the query to do the joins in a more appropriate
order. One trick to try is to reduce all the outer joins to plain inner
joins, then EXPLAIN that form of the query to see what order the planner
thinks the tables should be joined in. Then see if you can revise the
query to do the outer joins in that order.
One thing I find particularly odd is that the query is phrased to cause
the entire join of mstuserprofiles and mstfacilityviews to be formed,
because the last few lines will be parsed as
RIGHT OUTER JOIN
(mstuserprofiles LEFT OUTER JOIN mstfacilityviews
ON mstuserprofiles.uspdefaultfacility = mstfacilityviews.pkfacid)
ON mstcompanies.pkcmpid = mstuserprofiles.uspdefaultcounterparty
AND mstcompanies.fktrdid_cmptradhouseid = mstuserprofiles.fktrdid_usptradhouseid
Surely that ordering of the ON clauses was a thinko and should be
reconsidered. And do you really want a RIGHT JOIN to the second
occurrence of mstuserprofiles? Why?
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, pambardar@networkprograms.com
Subject: Re: SQL Optimization
In-Reply-To: <19011.1072134422@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