Re: performance problems with subselects

On Thu, 2002-04-25 at 03:27, John Taylor wrote:
>
> 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 ?

Possibly a small pl/pgsql function would be better:

CREATE OR REPLACE FUNCTION get_next_olnum( INT, TEXT )
RETURNS INT AS '
DECLARE
i_ordernum ALIAS FOR $1;
i_stockcode ALIAS FOR $2;
o_nextnum INT DEFAULT 1;
BEGIN
SELECT line INTO o_nextnum
FROM orderlinesupdates
WHERE theorder = i_ordernum
AND stock = i_stockcode;
IF NOT FOUND THEN
SELECT line + 1 INTO o_nextnum
FROM orderlinesupdates
WHERE theorder = i_ordernum;
IF NOT FOUND THEN
o_nextnum := 1;
END IF;
END IF;
RETURN o_nextnum;
END;
' LANGUAGE 'plpgsql';

This way the second select will not be executed in the event that the
first select is successful.

Then you can just use it in place of the coalesce:

..., get_next_olnum( oh.theorder, 'stock2' ), ...

Regards,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?