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 по дате отправления:

Предыдущее
От: Csaba Nagy
Дата:
Сообщение: Re: Delete/update with limit
Следующее
От: Gregory Stark
Дата:
Сообщение: Re: Delete/update with limit