Re: SQL Optimization

"Preeti Ambardar" <pambardar(at)networkprograms(dot)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