Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 3A903476795 for ; Wed, 26 Jun 2002 11:21:19 -0400 (EDT) Mailbox-Line: From tgl@sss.pgh.pa.us Wed Jun 26 11:21:19 2002 Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 2BB19476C13 for ; Wed, 26 Jun 2002 10:13:54 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g5QEDuv02018; Wed, 26 Jun 2002 10:13:56 -0400 (EDT) To: "Eric" Cc: pgsql-sql@postgresql.org Subject: Re: Performance Ideas In-reply-to: References: Comments: In-reply-to "Eric" message dated "Tue, 25 Jun 2002 15:37:39 -0400" Date: Wed, 26 Jun 2002 10:13:56 -0400 Message-ID: <2015.1025100836@sss.pgh.pa.us> From: Tom Lane X-Spam-Status: No, hits=-5.3 required=5.0 tests=IN_REP_TO,X_NOT_PRESENT version=2.30 X-Spam-Level: X-Archive-Number: 200206/349 X-Sequence-Number: 8314 "Eric" 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