Re: Performance Ideas

"Eric" <emayo(at)pozicom(dot)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