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?