Re: Optimizing queries

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;