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.
