On Tuesday 23 April 2002 15:04, Tom Lane wrote:
> John Taylor <postgres(at)jtresponse(dot)co(dot)uk> writes:
> > I am aware that I am using almost the same select from orderheader 3
> > times, but I'm not sure how I can improve on this.
Thanks to advice from Tom Lane, and Andrew McMillan I have now restructured
my query to go twice as fast !
I now have:
INSERT INTO orderlinesupdates
FROM orderheaderupdates oh
LEFT OUTER JOIN orderlinesupdates ol
ON oh.theorder = ol.theorder AND ol.stock='stock2'
LEFT OUTER JOIN orderlinesupdates ol2
ON oh.theorder = ol2.theorder
WHERE oh.account='account1' AND oh.delivery=1 AND oh.thedate='2002-02-01' AND oh.ordertype='O'
ORDER BY oh.updated DESC, ol.line DESC, ol2.line DESC
The only way I can see to improve this any more is to reduce the JOINS and coalesce()
What I want to do is:
If there is a matching orderline, use the same line number
otherwise, find the largest line number in the orderlines with the same orderheader, and use 1 greater
otherwise, this is the first orderline for this orderheader, so use 1
Is there a way I can do this without two outer joins ?
As one JOIN is a subset of the other, is there a way to get postgres to use the results from the ol2 query
and then apply the additional ol restriction without performing the join again ?
Maybe I could use a subselect here instead ?
In response to
pgsql-novice by date
|Next:||From: Alain Chamoux||Date: 2002-04-24 16:13:44|
|Subject: PostgreSQL under Windows - Misc.|
|Previous:||From: Leandro Fanzone||Date: 2002-04-24 15:23:09|