"Preeti Ambardar" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
