Как сделать префиксный поиск в PostgeSQL, чтобы использовался индекс?

Question

[un1t]

Мне нужен поиск по префиксу, по точному вхождению.

Результат можно получить такими запросами.
SELECT * FROM regions WHERE name ILIKE 'екат%';

SELECT * FROM regions WHERE LOWER(name) LIKE 'екат%';

Простой btree индекс по полю работает с LIKE, но не работает с ILIKE.
Функциональный индекс lower(name), работает по точному вхождению, но не рабтает с "LOWER(name) LIKE 'екат%';".

Можно еще через полнотекстовый поиск:

SELECT * FROM geobase_region WHERE fts @@ to_tsquery('simple', 'заречный:*');

Но в случае с полнотекстовым добавляется геморой с обновлением индекса, ну там проедуры какие-то писать. Может быть без этого можно?

Comments

[Falseclock]

а если сделать set enable_seqscan=false ?

[un1t]

Falseclock: оно установлено.

[Falseclock]

un1t: а покажите синтаксис индекса

[un1t]

уже не надо, ниже ответ написали.

Answer 1

[Александр Шелеметьев]

У меня индекс работает в таком случае:

CREATE INDEX addr_lower_idx
  ON public.addr
  USING btree
  (lower(factaddr) COLLATE pg_catalog."default" text_pattern_ops);

explain analyze
select * from addr 
where lower(factaddr) like lower('Моск%');

Результат:

Bitmap Heap Scan on addr  (cost=22.40..490.53 rows=194 width=150) (actual time=0.110..0.110 rows=0 loops=1)
  Filter: (lower(factaddr) ~~ 'моск%'::text)
  ->  Bitmap Index Scan on addr_lower_idx  (cost=0.00..22.36 rows=194 width=0) (actual time=0.108..0.108 rows=0 loops=1)
        Index Cond: ((lower(factaddr) ~>=~ 'моск'::text) AND (lower(factaddr) ~<~ 'мосл'::text))
Planning time: 0.174 ms
Execution time: 0.173 ms

Возможно, у вас индекс не подтягивался потому что не использовалось text_pattern_ops?
Экспериментировал с поиском по тексту тут

Comments

[un1t]

Спасибо. Вот так заработало.

create index region_prefix_indeч on regions (lower(name) varchar_pattern_ops);