Subject: Re: Optimizing queries
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Patrice Beliveau <pbeliveau@avior.ca>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <44D8CE92.1080407@avior.ca>
References: <44D8CE92.1080407@avior.ca>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1155062399.20252.50.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Tue, 08 Aug 2006 13:39:59 -0500

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;