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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alain Chamoux | 2002-04-24 16:13:44 | PostgreSQL under Windows - Misc. |
Previous Message | Leandro Fanzone | 2002-04-24 15:23:09 | Multithreading |