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?
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
