Re: Question about simple function folding optimization

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)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