pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL Optimization
2+ messages / 2 participants
[nested] [flat]
* SQL Optimization
@ 2003-12-17 06:26 Preeti Ambardar <pambardar@networkprograms.com>
0 siblings, 1 reply; 2+ messages in thread
From: Preeti Ambardar @ 2003-12-17 06:26 UTC (permalink / raw)
To: pgsql-sql
I am working on a query qhich has lot of RIGHT and LEFT Outer joins. This takes almost 2 hours to execute.
SELECT mstcurrency.pkcurid, mstcurrency.curswift_code,
mstproducts.pkprdid, mstproducts.prdname,
mstproducts.fkcnvid_prdbaseuom,
cpuser.uspname || ' ' || cpuser.uspsurname as uspname,
cpuser.pkuspid, cpuser.uspnotify_number AS
cpusercontactdetails, cpuser.fktrdid_usptradhouseid as cpthid,
mstcompanies.pkcmpid, mstcompanies.cmpname,
mstcompanies.cmpaccount_type,
mstfacilityviews.facname, mstfacilityviews.pkfacid
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: SQL Optimization
@ 2003-12-22 23:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Preeti Ambardar <pambardar@networkprograms.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2003-12-22 23:07 UTC (permalink / raw)
To: Preeti Ambardar <pambardar@networkprograms.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-12-22 23:07 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-17 06:26 SQL Optimization Preeti Ambardar <pambardar@networkprograms.com>
2003-12-22 23:07 ` 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