Подскажите плс, как реализовать нечувствительный к регистру поиск по русским именам, и чтобы при этом использовался индекс.
Таблица такая.
CREATE TABLE IF NOT EXISTS public.names
(
id integer NOT NULL,
name character varying(50) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT names_pkey PRIMARY KEY (id)
)
CREATE INDEX IF NOT EXISTS idx_name
ON public.names USING btree
(name ASC NULLS LAST)
TABLESPACE pg_default;
Например, запрос:
SELECT name FROM names WHERE name LIKE 'АБ%'
Нужно, чтобы возвращались строки с разным регистром:
Здравствуйте, yenik, Вы писали:
Y>Подскажите плс, как реализовать нечувствительный к регистру поиск по русским именам, и чтобы при этом использовался индекс. Y>Таблица такая.
Y>
Y>CREATE TABLE IF NOT EXISTS public.names
Y>(
Y> id integer NOT NULL,
Y> name character varying(50) COLLATE pg_catalog."default" NOT NULL,
Y> CONSTRAINT names_pkey PRIMARY KEY (id)
Y>)
Y>CREATE INDEX IF NOT EXISTS idx_name
Y> ON public.names USING btree
Y> (name ASC NULLS LAST)
Y> TABLESPACE pg_default;
Y>
Y>Например, запрос:
Y>SELECT name FROM names WHERE name LIKE 'АБ%'
Y>Нужно, чтобы возвращались строки с разным регистром:
Y>"АбвгД" Y>"аБВ" Y>"АБББ"
Здравствуйте, yenik, Вы писали:
Y>Подскажите плс, как реализовать нечувствительный к регистру поиск по русским именам, и чтобы при этом использовался индекс. Y>Таблица такая.
Y>
Y>CREATE TABLE IF NOT EXISTS public.names
Y>(
Y> id integer NOT NULL,
Y> name character varying(50) COLLATE pg_catalog."default" NOT NULL,
Y> CONSTRAINT names_pkey PRIMARY KEY (id)
Y>)
Y>CREATE INDEX IF NOT EXISTS idx_name
Y> ON public.names USING btree
Y> (name ASC NULLS LAST)
Y> TABLESPACE pg_default;
Y>
Y>Например, запрос:
Y>SELECT name FROM names WHERE name LIKE 'АБ%'
Здравствуйте, vaa, Вы писали:
Y>>Подскажите плс, как реализовать нечувствительный к регистру поиск по русским именам, и чтобы при этом использовался индекс.
vaa>ILIKE vaa>
С базами данных работаю лет 20, никогда не знал про ILIKE
Здравствуйте, qaz77, Вы писали:
Q>Здравствуйте, Alex.Che, Вы писали: AC>>можно конечно через SIMILAR или UPPER, но индексы тогда отдыхают.
Q>Так, стоп. Q>А как ILIKE может использовать индекс, построенный для case sensitive строк?
Никак. Поэтому либо хранить в lowercase и делать простой индекс, либо делать индекс по функции.
Здравствуйте, qaz77, Вы писали:
vsb>>Никак. Поэтому либо хранить в lowercase и делать простой индекс, либо делать индекс по функции.
Q>Ну, понятно. Т.е. делать руками...
если при создании поля указать правильный COLLATE то индекс будет подхватываться.
Здравствуйте, Alex.Che, Вы писали:
AC>если при создании поля указать правильный COLLATE то индекс будет подхватываться.
Точно будет? С btree-индексом и префиксным like? Народ в интернете плачет, что по-умолчанию оно не работает. Да и документация по опклассам намекает, что обычный индекс в LIKE будет работать только в "C" locale. В теории collation можно задать, и она даже будет учитываться в индексе. Но нечувствительная к регистру collation будет и недетерминированной (non-deterministic). А в документации пишут, что
Note, in particular, that B-tree cannot use deduplication with indexes that use a nondeterministic collation. Also, certain operations are not possible with nondeterministic collations, such as pattern matching operations. Therefore, they should be used only in cases where they are specifically wanted.
Я не знаю, считается ли LIKE/ILIKE сопоставлением с образцом. Нужно экспериментировать.
Непросто это делается.
За примером данных заходим на https://mydata.biz/ru/catalog/databases/names_db и скачиваем БД русских имён и фамилий.
Заливаем это в PostgreSQL.
Нужно найти фамилию по первым буквам без учёта регистра. Насколько я понял, собаководы рекомендуют LOWER. Или UPPER, что то же самое.
SELECT * FROM russian_surnames WHERE LOWER(surname) = 'петров';
"id" "surname" "sex" "peoplescount" "whenpeoplescount" "source"
199924 "Петров" 334000 "2016-06-06 04:35:16" "myData.biz"
Итак, запрос работает.
Посмотрим план.
test=# EXPLAIN SELECT * FROM russian_surnames WHERE surname = 'петров';
QUERY PLAN
-----------------------------------------------------------------------------------
Gather (cost=1000.00..5863.82 rows=1 width=52)
Workers Planned: 2
-> Parallel Seq Scan on russian_surnames (cost=0.00..4863.72 rows=1 width=52)
Filter: ((surname)::text = '??????'::text)
(4 rows)
Индексов нет, поэтому сканирование по таблице — Seq Scan.
Поскольку запрос с LOWER, то создаём индекс по функции LOWER.
test=# CREATE INDEX russian_surnames_lower_idx ON russian_surnames (LOWER(surname));
test=# EXPLAIN SELECT * FROM russian_surnames WHERE LOWER(surname) = 'петров';
QUERY PLAN
---------------------------------------------------------------------------------------------
Bitmap Heap Scan on russian_surnames (cost=44.76..2757.27 rows=1592 width=52)
Recheck Cond: (lower((surname)::text) = '??????'::text)
-> Bitmap Index Scan on russian_surnames_lower_idx (cost=0.00..44.36 rows=1592 width=0)
Index Cond: (lower((surname)::text) = '??????'::text)
(4 rows)
Индекс работает — Index Scan.
Но нам нужен поиск с LIKE.
test=# EXPLAIN SELECT * FROM russian_surnames WHERE LOWER(surname) LIKE 'петро%';
QUERY PLAN
-------------------------------------------------------------------------------------
Gather (cost=1000.00..6354.66 rows=1592 width=52)
Workers Planned: 2
-> Parallel Seq Scan on russian_surnames (cost=0.00..5195.46 rows=663 width=52)
Filter: (lower((surname)::text) ~~ '?????%'::text)
(4 rows)
Опять Seq Scan — индекс не работает.
Оказывается, просто индекса недостаточно, нужна опция text_pattern_ops.
DROP INDEX russian_surnames_lower_idx;
CREATE INDEX russian_surnames_lower_idx ON russian_surnames (LOWER(surname) text_pattern_ops);
test=# EXPLAIN SELECT * FROM russian_surnames WHERE LOWER(surname) LIKE 'петро%';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on russian_surnames (cost=48.74..2761.25 rows=1592 width=52)
Filter: (lower((surname)::text) ~~ '?????%'::text)
-> Bitmap Index Scan on russian_surnames_lower_idx (cost=0.00..48.34 rows=1592 width=0)
Index Cond: ((lower((surname)::text) ~>=~ '?????'::text) AND (lower((surname)::text) ~<~ '????@'::text))
(4 rows)
Индекс работает.
Но после SQL Server всё это выглядит странновато.
И ещё неприятно, что EF такие индексы не умеет скаффолдить и создавать штатным образом. Приходится в миграции писать raw SQL.
