Обсуждение: Using functions as filters in queries
I have a problem that can be reduced to this equivalent but simpler
problem:
Case 1:
CREATE TABLE t (
n int,
x varchar(32)
);
CREATE INDEX ti ON t(n);
CREATE INDEX tx ON t(x);
<insert 100k rows of data, random words into x>
VACUUM ANALYZE;
EXPLAIN ANALYZE select * from t where n=5 AND x like 'a%';
Index Scan using ti on t (cost=0.00..3.02 rows=1 width=15) (actual
time=0.12..0.12 rows=0 loops=1)
Index Cond: (n = 5)
Filter: (x ~~ 'a%'::text)
Total runtime: 0.16 msec
This is perfect - ti is used for indexing.
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%';
Seq Scan on t (cost=0.00..1161.25 rows=436 width=15) (actual
time=265.04..265.04 rows=0 loops=1)
Filter: (f(t.*, 5) AND (x ~~ 'a%'::text))
Total runtime: 265.08 msec
If I set enable_seqscan=0, I get this:
Seq Scan on t (cost=0.00..1161.25 rows=436 width=15) (actual
time=262.45..262.45 rows=0 loops=1)
Filter: (f(t.*, 5) AND (x ~~ 'a%'::text))
Total runtime: 262.49 msec
Same thing.
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.
I notice that internal functions (eg @ on boxes/points) are not treated
this way. is there any way to get my function treated like an internal
function, or is not as simple as that?
You could argue that my function is pointless and I could simply do the
replacement in the application layer that calls the SQL. This is true, but
with my full example I would like to hide some aspects of the physical
layer behind a nice SQL/function logical layer.
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.
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. :(
Could you please stop to send these message? --- Stephan Szabo <sszabo@megazone23.bigpanda.com> 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. > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster ______________________________________________________________________ Post your free ad now! http://personals.yahoo.ca
On Wed, 12 Mar 2003 17:24:49 -0500 (EST), Xue-Feng Yang wrote: >Could you please stop to send these message? Yes! Please! How did this flood of messages start a few days ago??? This is getting quite annoying. Stéphane
Could you please stop to send these message? I unscripted this list a few months ago and hadn't received messages for months. I received messages from the list again started from yesterday. Could you please stop to send these message? --- Xue-Feng Yang <just4look@yahoo.com> wrote: > Could you please stop to send these message? > > > --- Stephan Szabo <sszabo@megazone23.bigpanda.com> > 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. > > > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > ______________________________________________________________________ > > Post your free ad now! http://personals.yahoo.ca > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to majordomo@postgresql.org > so that your > message can get through to the mailing list cleanly ______________________________________________________________________ Post your free ad now! http://personals.yahoo.ca
Could you please stop to send these message? I unscripted this list a few months ago and hadn't received messages for months. I received messages from the list again started from yesterday. Could you please stop to send these message? --- Xue-Feng Yang <just4look@yahoo.com> wrote: > > > > --- Stephan Szabo <sszabo@megazone23.bigpanda.com> > 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. > > > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > ______________________________________________________________________ > > Post your free ad now! http://personals.yahoo.ca > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to majordomo@postgresql.org > so that your > message can get through to the mailing list cleanly ______________________________________________________________________ Post your free ad now! http://personals.yahoo.ca
Could you please stop to send these message? I unscripted this list a few months ago and hadn't received messages for months. I received messages from the list again started from yesterday. Could you please stop to send these message? --- Xue-Feng Yang <just4look@yahoo.com> wrote: > > > > --- Stephan Szabo <sszabo@megazone23.bigpanda.com> > 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. > > > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > ______________________________________________________________________ > > Post your free ad now! http://personals.yahoo.ca > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to majordomo@postgresql.org > so that your > message can get through to the mailing list cleanly ______________________________________________________________________ Post your free ad now! http://personals.yahoo.ca
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> 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. :(
Note that inline-expansion of SQL functions like this is new for 7.4;
it's not done in any current release.
I think the extra step to make this expression indexable is probably not
too hard: the constant-expression folder needs to be taught that
extracting a field from a whole-row Var can be replaced by a Var
reference to the field, ie, fold "(t.*).n" into "t.n".
regards, tom lane
On Wed, 12 Mar 2003, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > 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. :( > > Note that inline-expansion of SQL functions like this is new for 7.4; > it's not done in any current release. I wasn't sure when it was added. > I think the extra step to make this expression indexable is probably not > too hard: the constant-expression folder needs to be taught that > extracting a field from a whole-row Var can be replaced by a Var > reference to the field, ie, fold "(t.*).n" into "t.n". That's what I figured, but I wasn't sure if there was some case where it was unsafe.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Wed, 12 Mar 2003, Tom Lane wrote:
>> I think the extra step to make this expression indexable is probably not
>> too hard: the constant-expression folder needs to be taught that
>> extracting a field from a whole-row Var can be replaced by a Var
>> reference to the field, ie, fold "(t.*).n" into "t.n".
> That's what I figured, but I wasn't sure if there was some case where it
> was unsafe.
I haven't thought it through either, but this particular example seems
safe.
regards, tom lane
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> 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. :(
As of a few moments ago, CVS tip reduces that to t.n=5 which is
indexable.
regression=# EXPLAIN select * from t where f(t, 5) AND x like 'a%';
QUERY PLAN
--------------------------------------------------------------
Index Scan using ti on t (cost=0.00..17.08 rows=1 width=38)
Index Cond: (n = 5)
Filter: (x ~~ 'a%'::text)
(3 rows)
regards, tom lane