Message-ID: <44D8F0B8.2030804@avior.ca>
Date: Tue, 08 Aug 2006 16:14:48 -0400
From: Patrice Beliveau <pbeliveau@avior.ca>
User-Agent: Thunderbird 1.5.0.4 (X11/20060614)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Optimizing queries
References: <44D8CE92.1080407@avior.ca>
	<1155062399.20252.50.camel@state.g2switchworks.com>
In-Reply-To: <1155062399.20252.50.camel@state.g2switchworks.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Scott Marlowe wrote:
> On Tue, 2006-08-08 at 12:49, Patrice Beliveau wrote:
>   
>> Hi,
>>
>> I have a query that use a function and some column test to select row. 
>> It's in the form of:
>>
>> SELECT * FROM TABLE
>>    WHERE TABLE.COLUMN1=something
>>       AND TABLE.COLUMN2=somethingelse
>>       AND function(TABLE.COLUMN3,TABLE.COLUMN4) > 0;
>>
>> The result of the function does NOT depend only from the table, but also 
>> from some other tables.
>>
>> Since it's long to process, I've add some output to see what's going on. 
>> I find out that the function process every row even if the row should be 
>> rejected as per the first or the second condition. Then , my question 
>> is: Is there a way to formulate a query that wont do all the check if it 
>> does not need to do it ? Meaning that, if condition1 is false then it 
>> wont check condition2 and that way the function will only be called when 
>> it's really necessary.
>>     
>
> What version of postgresql are you running?  It might be better in later
> versions.  The standard fix for such things is to use a subquery...
>
> select * from (
>    select * from table where 
>    col1='something'
>    and col2='somethingelse'
> ) as a
> where function(a.col3,a.col4) > 0;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>
>   
Thanks for the answer, but it does not work, maybe I did something wrong

First, I'm using version 8.1.3

This is what I did:

select * from (
    select * from sales_order_delivery
        where sales_order_id in (
                select sales_order_id from sales_order
                where closed=false
        )
  ) as a where outstandingorder(sales_order_id, sales_order_item, 
date_due) > 0;

Some output that I've create look like
INFO:  so:03616 soi:1 date:1993-12-23
INFO:  so:09614 soi:1 date:1998-06-04

which are the three arguments passed to the function "outstandingorder", 
but sales_order 03616 and 09614 are closed.

What's wrong ??

Thanks