В базе данных все таблицы изначально были установлены в порядке сортировки:
LC_COLLATE=C и LC_CTYPE = C. Оказалось, что из-за этого поиск игрока по нику не работает для кириллицы. Поэтому я решил изменить правила сортировки для отдельной колонки с ником:
1.
2.
3.
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 индекс:
1.
2.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY player_nickname_idx_gin ON player USING gin (nickname gin_trgm_ops);
После этого поиск по нику значительно ускорился на
латинице , и в плане запроса видно, что используется gin индекс.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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"
Но тот же запрос не работает с
кириллицей ...
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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 индекс брать триграммы, не основанные на стандартной сортировке базы? Есть ли какой-нибудь способ избежать перестройки кластера в этой ситуации?
