Skip site navigation (1) Skip section navigation (2)

Re: performance problems with subselects

From: John Taylor <postgres(at)jtresponse(dot)co(dot)uk>
To: pgsql-novice(at)postgresql(dot)org
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: performance problems with subselects
Date: 2002-04-24 15:27:47
Message-ID: 02042416274703.02452@splash.hq.jtresponse.co.uk (view raw or flat)
Thread:
Lists: pgsql-novice
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
(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

In response to

Responses

pgsql-novice by date

Next:From: Alain ChamouxDate: 2002-04-24 16:13:44
Subject: PostgreSQL under Windows - Misc.
Previous:From: Leandro FanzoneDate: 2002-04-24 15:23:09
Subject: Multithreading

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group