From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Chris Mungall <cjm(at)fruitfly(dot)org> |
Cc: | <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Using functions as filters in queries |
Date: | 2003-03-12 22:10:41 |
Message-ID: | 20030312140421.O56401-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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. :(
From | Date | Subject | |
---|---|---|---|
Next Message | Xue-Feng Yang | 2003-03-12 22:24:49 | Re: Using functions as filters in queries |
Previous Message | Stephan Szabo | 2003-03-12 22:02:26 | Re: Using functions as filters in queries |