Re: Using functions as filters in queries

On Mon, 10 Mar 2003, Chris Mungall wrote:

> I have a problem that can be reduced to this equivalent but simpler
> problem:

> EXPLAIN ANALYZE select * from t where n=5 AND x like 'a%';

5 is a constant.

> Case 2:
>
> Now I want to replace the "n=5" clause with a function:
>
> CREATE FUNCTION f(t, int) RETURNS bool AS
> 'SELECT $1.n = $2'
> LANGUAGE 'sql';
>
> EXPLAIN ANALYZE select * from t where f(t, 5) AND x like 'a%';
>
> Now looking at the two cases (without and with function) I can see that
> they are equivalent, but Pg treats them differently. I guess it is failing
> to distiguish between two cases - if the function has no FROM clause, then
> it is a simple case of variable substitution into the original WHERE
> clause. If it does include a FROM clause, it's not so simple and
> optimisation is hard, so the function becomes the filter.

It doesn't look at the contents of the function. It looks at whether the
function is defined IMMUTABLE, STABLE or VOLATILE. With a VOLATILE
function (the default), the system is not guaranteed that given the
same arguments that the result is the same. You might want to read
the description in the manpage for CREATE FUNCTION.