Pattern matching with index
От | Viatcheslav Kalinin |
---|---|
Тема | Pattern matching with index |
Дата | |
Msg-id | 46A6267B.5020405@ipcb.net обсуждение исходный текст |
Список | 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?
В списке pgsql-general по дате отправления: