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?
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? |