Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Как заставить gin индекс брать триграммы не на основе дефолтного collation базы?  [new]
Евгений Калинин
Member

Откуда:
Сообщений: 3
В базе данных все таблицы изначально были установлены в порядке сортировки: LC_COLLATE=C и LC_CTYPE = C. Оказалось, что из-за этого поиск игрока по нику не работает для кириллицы. Поэтому я решил изменить правила сортировки для отдельной колонки с ником:

CREATE COLLATION IF NOT EXISTS english (provider = icu, locale = 'en_US');
ALTER TABLE player
    ALTER COLUMN nickname SET DATA TYPE character varying(64) COLLATE "english";


Далее я хотел сделать быстрый поиск игрока по нику с помощью индексов триграмм и создал gin индекс:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY player_nickname_idx_gin ON player USING gin (nickname gin_trgm_ops);


После этого поиск по нику значительно ускорился на латинице, и в плане запроса видно, что используется gin индекс.

EXPLAIN ANALYSE
SELECT * FROM player
WHERE nickname like '%qwerty%';

QUERY PLAN
"Bitmap Heap Scan on player  (cost=81.19..6172.98 rows=1702 width=266) (actual time=2.788..6.735 rows=580 loops=1)"
"  Recheck Cond: ((nickname)::text ~~ '%qwerty%'::text)"
"  Rows Removed by Index Recheck: 448"
"  Heap Blocks: exact=1021"
"  ->  Bitmap Index Scan on player_nickname_idx_gin  (cost=0.00..80.77 rows=1702 width=0) (actual time=2.446..2.447 rows=1028 loops=1)"
"        Index Cond: ((nickname)::text ~~ '%qwerty%'::text)"
"Planning Time: 0.304 ms"
"Execution Time: 6.869 ms"


Но тот же запрос не работает с кириллицей...

EXPLAIN ANALYSE
SELECT * FROM player
WHERE nickname like '%абвгд%';

QUERY PLAN
"Gather  (cost=1000.00..122898.55 rows=532 width=266) (actual time=190.395..1243.729 rows=27 loops=1)"
"  Workers Planned: 2"
"  Workers Launched: 2"
"  ->  Parallel Seq Scan on player  (cost=0.00..121845.35 rows=222 width=266) (actual time=256.894..1103.830 rows=9 loops=3)"
"        Filter: ((nickname)::text ~~ '%абвгд%'::text)"
"        Rows Removed by Filter: 1773005"
"Planning Time: 0.252 ms"
"JIT:"
"  Functions: 12"
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 10.563 ms, Inlining 0.000 ms, Optimization 5.027 ms, Emission 59.776 ms, Total 75.367 ms"
"Execution Time: 1248.374 ms"



Я также попытался установить collation для gin индекса, но ничего не изменилось. Скажите, как заставить gin индекс брать триграммы, не основанные на стандартной сортировке базы? Есть ли какой-нибудь способ избежать перестройки кластера в этой ситуации?
1 апр 21, 22:08    [22303166]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить gin индекс брать триграммы не на основе дефолтного collation базы?  [new]
Alexius
Member

Откуда:
Сообщений: 745
Евгений Калинин,

а если указать collation и для поля в индексе и для самой поисковой строки в запросе - так не подхватит индекс?
2 апр 21, 10:56    [22303259]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить gin индекс брать триграммы не на основе дефолтного collation базы?  [new]
Евгений Калинин
Member

Откуда:
Сообщений: 3
Создал новый индекс, дропнув старый:

CREATE INDEX CONCURRENTLY player_nickname_idx_gin ON player USING gin 
(nickname COLLATE public.english gin_trgm_ops);


Попробовал:

explain analyse
select * from player
where nickname like '%абвгде%' COLLATE public.english;


Без изменений:

QUERY PLAN
"Gather  (cost=1000.00..134059.97 rows=547 width=260) (actual time=402.362..1215.868 rows=13 loops=1)"
"  Workers Planned: 2"
"  Workers Launched: 2"
"  ->  Parallel Seq Scan on player  (cost=0.00..133005.27 rows=228 width=260) (actual time=345.131..1095.698 rows=4 loops=3)"
"        Filter: ((nickname)::text ~~ '%абвгде%'::text COLLATE english)"
"        Rows Removed by Filter: 1823437"
"Planning Time: 0.299 ms"
"JIT:"
"  Functions: 12"
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 10.706 ms, Inlining 0.000 ms, Optimization 4.560 ms, Emission 60.069 ms, Total 75.335 ms"
"Execution Time: 1221.123 ms"


Сообщение было отредактировано: 2 апр 21, 11:37
2 апр 21, 11:45    [22303283]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить gin индекс брать триграммы не на основе дефолтного collation базы?  [new]
Евгений Калинин
Member

Откуда:
Сообщений: 3
Так же заметил, что русский текст не разбивается на триграммы:

SELECT show_trgm('abcd') union 
SELECT show_trgm('абвгд');

{}
{"  a"," ab",abc,bcd,"cd "}


Сообщение было отредактировано: 2 апр 21, 11:59
2 апр 21, 12:06    [22303297]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить gin индекс брать триграммы не на основе дефолтного collation базы?  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4691
Евгений Калинин
Так же заметил, что русский текст не разбивается на триграммы:

SELECT show_trgm('abcd') union 
SELECT show_trgm('абвгд');

{}
{"  a"," ab",abc,bcd,"cd "}


В общем не вылечите вы это без пересоздания базы с нормальными LC_COLLATE/LC_CTYPE
во всяком случае штатными методами.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
2 апр 21, 12:59    [22303345]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить