Re: Using functions as filters in queries

On Wed, 12 Mar 2003, Stephan Szabo wrote:

>
> 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.

Of course, I misread what explain did (without trying the
enable_seqscan=off case) and this is still not indexable because even
after that, you'll not get a clause on the outside that it considers
indexable.  It is smart enough (7.4 anyway) to make the filter ((t.*).n)=5
which I thought it'd index, but doesn't. :(