Question about simple function folding optimization

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: Question about simple function folding optimization
Date: 2003-04-09 14:48:53
Message-ID: 20030409074051.Y65184-100000@megazone23.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


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=6 width=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.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2003-04-09 15:00:08 Re: pg_get_viewdef 7.4 et al
Previous Message Tom Lane 2003-04-09 14:20:25 Re: pg_get_viewdef 7.4 et al