Обсуждение: Performance Ideas

I have a SQL which uses a function for one of the returned rows.  This
stored function does calculations that are expensive & slow.  I am looking
for ways to speed up this query but having no luck.

Any SQL geniuses out there help me with this?

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;

The function, qty_onhand, calculates the Qty on hand and returns a value in
units of measure passed (ol.uom).  This function is an expensive function to
use -- degrades performance.  With out the function in the WHERE or SELECT
clause, performances is acceptable.

I get marginally better performance if I "select into temporary table"
without the function and then run a query on the temporary table which
includes the qty_onhand function.

I am trying to present the user with a list of open orders that are "READY"
to be fulfilled which requires me to do a "stock level check."

My fall back solution is to make the user enter some pre-query information
like the orderid she is trying to ship against but my customer really likes
the current view they have which shows all open orders that are READY to be
fulfilled.


Any ideas??!?!?!  Tricks of the trade?!?!?!

Also, side note, I tried creating views assuming PostgreSQL would optimize
the view after a vacuum but it does not.  Also, the function seems faster in
the temporary table, why?  Why wouldn't the funciton only evaluate values
that match the first 2 criteria (OPEN and ol.orderid = o.orderid)?  It's as
if the qty_onhand is evaluating ALL records in the orderlines (ol) table.

Thanks , Eric

"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