Re: Performance Ideas

"Eric" <emayo@pozicom.net> writes:
> select
>   o.orderid,
>  ol.itemcode,
>  ol.itemname,
>  ol.uom,
>  qty_available( ol.itemcode, ol.uom ) as "Qty On Hand"
> from
>   orders o,
>   orderlines ol,
> where
>   o.status = 'OPEN' and
>   ol.orderid = o.orderid and
>   qty_onhand( ol.itemcode, ol.uom ) > 0;

> It's as if the qty_onhand is evaluating ALL records in the orderlines
> (ol) table.

Yeah, it probably is.  Given that WHERE condition the planner will try to
use the "qty_onhand( ol.itemcode, ol.uom ) > 0" clause as a restriction
on "ol" in advance of the join.  Since the planner has no idea that
qty_onhand() is an expensive function, this is a reasonable choice.

Can you restructure things so that the qty_onhand clause uses some value
from "o" as well as "ol"?  A really grotty way would be to just give
qty_onhand a dummy third parameter and write
qty_onhand( ol.itemcode, ol.uom, o.whatever ) > 0;

but maybe you have a less obscure alternative available.
        regards, tom lane