Pattern matching with index

From: Viatcheslav Kalinin <vka(at)ipcb(dot)net>
To: Postgres general mailing list <pgsql-general(at)postgresql(dot)org>
Subject: Pattern matching with index
Date: 2007-07-24 16:19:07
Message-ID: 46A6267B.5020405@ipcb.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,
I can't seem to make a request inside a plpgsql function use an index,
basically I have something like this:

CREATE TABLE T1 (f VARCHAR(100));
/* db is created with utf8 locale hence 2 indicies */
CREATE INDEX ix_t1_f ON T1
USING btree ((lower("f")::text) text_pattern_ops);

CREATE INDEX ix_t1_f1 ON T1
USING btree ((lower("f")::text));

CREATE FUNCTION get_f(ptrn VARCHAR, inout cur pg_catalog.refcursor) AS
$$
begin
open cur for
select * from T1 where lower(f) like ptrn || '%';
end;
$$
LANGUAGE 'plpgsql';

Function works slowly apparently because of that it ignores the index, while
> explain analyze select * from T1 where lower(f) like 'new%';

Bitmap Heap Scan on t1 (cost=64.82..2913.40 rows=2389 width=218)
(actual time=0.575..2.571 rows=572 loops=1)
Filter: (lower((f)::text) ~~ 'new%'::text)
-> Bitmap Index Scan on ix_t1_f (cost=0.00..64.22 rows=2389 width=0)
(actual time=0.518..0.518 rows=572 loops=1)
Index Cond: ((lower((f)::text) ~>=~ 'new'::text) AND
(lower((f)::text) ~<~ 'nex'::text))
Total runtime: 3.138 ms

My guess would be planner won't consider "ptrn || '%'" as constant
pattern. Changing function body to

declare
cptrn constant text := ptrn || '%';
begin
open cur for
select * from T1 where lower(f) like cptrn;
end;

does help. Is there any way to get around this and make it use the index?

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2007-07-24 16:19:25 Re: Delete/update with limit
Previous Message Joshua D. Drake 2007-07-24 16:12:17 Re: Using result from current_database() into another command?