From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Eric" <emayo(at)pozicom(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Performance Ideas |
Date: | 2002-06-26 14:13:56 |
Message-ID: | 2015.1025100836@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
"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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2002-06-26 14:46:01 | Re: sequence chages after firing update |
Previous Message | Subhashini Karthikeyan | 2002-06-26 11:47:24 | sequence chages after firing update |