Thread: Question about simple function folding optimization
I noticed today (while trying to answer a question) that the following
doesn't seem to use the index on reasonably recent sources whereas it
seems to have at 7.3.1.
create table b1(a int, b text);
create function fold_clients(int, text) returns text as 'select
$1 || ''_'' || upper($2)' language 'sql' immutable;
create index b1ind on b1(fold_clients(a,b));
set enable_seqscan=off;
explain select * from b1 where (fold_clients(a,b))='1_A';
Should that work to use the index or was it just a fluke that it worked in
the past? I think that it's an issue that the function is being expanded
inline (the explain looks like:)
QUERY PLAN
-------------------------------------------------------------------Seq Scan on b1 (cost=100000000.00..100000032.50
rows=6width=36) Filter: ((((a)::text || '_'::text) || upper(b)) = '1_A'::text)
which makes it not realize it can use the index.
It's easy to get around for simple functions like this since you can just
make a plpgsql function that returns the expression, but we should at
least note it in the release notes since it's likely to catch people by
surprise.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> I noticed today (while trying to answer a question) that the following
> doesn't seem to use the index on reasonably recent sources whereas it
> seems to have at 7.3.1.
> create table b1(a int, b text);
> create function fold_clients(int, text) returns text as 'select
> $1 || ''_'' || upper($2)' language 'sql' immutable;
> create index b1ind on b1(fold_clients(a,b));
> set enable_seqscan=off;
> explain select * from b1 where (fold_clients(a,b))='1_A';
Hmm. That's an unexpected downside of the recent change to inline
simple SQL functions :-(. The inlined expression no longer looks
like a match to the index.
The simplest answer is probably to convert the function to plpgsql,
which would probably give better performance for index access anyway.
But I wonder whether any better answer is possible. I don't want to
give up on the inlining optimization --- anyone see another fix?
regards, tom lane
Tom Lane wrote: > Hmm. That's an unexpected downside of the recent change to inline > simple SQL functions :-(. The inlined expression no longer looks > like a match to the index. > > The simplest answer is probably to convert the function to plpgsql, > which would probably give better performance for index access anyway. > But I wonder whether any better answer is possible. I don't want to > give up on the inlining optimization --- anyone see another fix? > Would changing the function from immutable to stable work (similar to the other day)? Joe
Joe Conway <mail@joeconway.com> writes:
> Would changing the function from immutable to stable work (similar to
> the other day)?
No; it won't prevent inlining, but it *will* prevent you from using it
as an index function ...
regards, tom lane
On Wed, Apr 09, 2003 at 11:54:23 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > The simplest answer is probably to convert the function to plpgsql, > which would probably give better performance for index access anyway. > But I wonder whether any better answer is possible. I don't want to > give up on the inlining optimization --- anyone see another fix? Include an option for function definitions to not allow the function being defined to be inlined.
Bruno Wolff III <bruno@wolff.to> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> But I wonder whether any better answer is possible. I don't want to
>> give up on the inlining optimization --- anyone see another fix?
> Include an option for function definitions to not allow the function
> being defined to be inlined.
Seems pretty ugly. I was wondering how much it would cost to run the
index's function definition through the inliner before comparing ...
regards, tom lane
Tom Lane writes: > But I wonder whether any better answer is possible. I don't want to > give up on the inlining optimization --- anyone see another fix? If a functional index applies to the function, generate one plan with the function inlined and one with the function call left in, and then plan away as usual. While that could blow up the number of plan choices to consider (which would have to be controlled in some way), this would give the best end results because the planner could consider the choice knowing what the function results are used for. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> But I wonder whether any better answer is possible. I don't want to
>> give up on the inlining optimization --- anyone see another fix?
> If a functional index applies to the function, generate one plan with the
> function inlined and one with the function call left in, and then plan
> away as usual. While that could blow up the number of plan choices to
> consider (which would have to be controlled in some way), this would give
> the best end results because the planner could consider the choice knowing
> what the function results are used for.
But there isn't any situation where the non-inlined version is "better",
so there isn't really a plannable choice to be made here. I'd be
willing to do the above if there were cases where it actually made sense
not to inline a simple function, and the planner could determine which
those were using cost estimates. But it seems like an inappropriate
workaround for not being able to match up functions with indexes...
The urge is becoming very strong to fix this by replacing functional
indexes with expressional indexes, as I suggested doing many moons ago.
Then the difficulty at hand would be easily handled by running the
index's defining expression through eval_const_expressions before
checking if it is equal() to a candidate WHERE argument. Not sure I
have time to do that before 7.4 though :-(
regards, tom lane
Awhile back I said:
> [ SQL-function inlining breaks planner's ability to recognize functional
> indexes ]
> The urge is becoming very strong to fix this by replacing functional
> indexes with expressional indexes, as I suggested doing many moons ago.
> Then the difficulty at hand would be easily handled by running the
> index's defining expression through eval_const_expressions before
> checking if it is equal() to a candidate WHERE argument. Not sure I
> have time to do that before 7.4 though :-(
I've now done this (amazing what you can do when you're putting off
editing thousands of elog() calls ...), and so Stephan's example
works again:
regression=# explain select * from b1 where (fold_clients(a,b))='1_A'; QUERY PLAN
----------------------------------------------------------------------Index Scan using b1ind on b1 (cost=0.00..17.12
rows=5width=36) Index Cond: ((((a)::text || '_'::text) || upper(b)) = '1_A'::text)
(2 rows)
Note that EXPLAIN will show the index condition in terms of the inlined
function's expansion. A rather interesting property of this behavior
is that the index can get matched to whether you write the original
function or its equivalent expression.
regards, tom lane
On Thu, May 29, 2003 at 13:44:18 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > I've now done this (amazing what you can do when you're putting off > editing thousands of elog() calls ...), and so Stephan's example > works again: Can you use functional expressions in CREATE INDEX now (7.4 CVS) as well?
Bruno Wolff III <bruno@wolff.to> writes: > Can you use functional expressions in CREATE INDEX now (7.4 CVS) as well? You can use expressions. I'm not sure if you mean anything particular by "functional expressions". See the man page --- http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-createindex.html (the documentation build at developer.postgresql.org doesn't seem to have updated since before the server move :-() regards, tom lane
On Thu, May 29, 2003 at 15:19:09 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Bruno Wolff III <bruno@wolff.to> writes: > > Can you use functional expressions in CREATE INDEX now (7.4 CVS) as well? > > You can use expressions. I'm not sure if you mean anything particular > by "functional expressions". See the man page --- > http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-createindex.html > > (the documentation build at developer.postgresql.org doesn't seem to > have updated since before the server move :-() > > regards, tom lane Thanks. I had checked the create index on developer.postgresql.org before asking but thought that it might not be up to date.
Tom Lane writes: > (the documentation build at developer.postgresql.org doesn't seem to > have updated since before the server move :-() The program called "onsgmls" (or maybe "nsgmls") is missing. Marc, can you please install it? It should be in a package called "opensp". -- Peter Eisentraut peter_e@gmx.net
sorry for delay ... installing opensp right now ... On Sun, 1 Jun 2003, Peter Eisentraut wrote: > Tom Lane writes: > > > (the documentation build at developer.postgresql.org doesn't seem to > > have updated since before the server move :-() > > The program called "onsgmls" (or maybe "nsgmls") is missing. Marc, can > you please install it? It should be in a package called "opensp". > > -- > Peter Eisentraut peter_e@gmx.net > >