From: Tom Lane <tgl@sss.pgh.pa.us>
To: Eric <emayo@pozicom.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Performance Ideas
Date: Wed, 26 Jun 2002 10:13:56 -0400
Message-ID: <2015.1025100836@sss.pgh.pa.us> (raw)
In-Reply-To: <afagq4$mcm$1@news.hub.org>
References: <afagq4$mcm$1@news.hub.org>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, emayo@pozicom.net
Subject: Re: Performance Ideas
In-Reply-To: <2015.1025100836@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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