pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Performance Ideas
2+ messages / 2 participants
[nested] [flat]

* Performance Ideas
@ 2002-06-25 19:37 Eric <emayo@pozicom.net>
  2002-06-26 14:13 ` Re: Performance Ideas Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Eric @ 2002-06-25 19:37 UTC (permalink / raw)
  To: pgsql-sql

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







^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Performance Ideas
  2002-06-25 19:37 Performance Ideas Eric <emayo@pozicom.net>
@ 2002-06-26 14:13 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2002-06-26 14:13 UTC (permalink / raw)
  To: Eric <emayo@pozicom.net>; +Cc: pgsql-sql

"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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2002-06-26 14:13 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-25 19:37 Performance Ideas Eric <emayo@pozicom.net>
2002-06-26 14:13 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox