Re: performance problems with subselects

On Tuesday 23 April 2002 15:04, Tom Lane wrote:
> John Taylor <postgres@jtresponse.co.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
(theorder,type,stock,line,ordercurrent,sellingquant,price,discount,vatrate,comment,updated,utype,origin)
SELECT oh.theorder,'P','stock2',coalesce(ol.line,ol2.line+1,1),12,2,1.2,0.2,1,'testing',0,'+','C'
  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
  LIMIT 1;

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 ?

Thanks
JohnT