Добрый день. Встала проблема замедленного поиска.
Имеется БД ОКАТО(Общероссийский классификатор административно-территориального деления объектов) с почти 200т. записей.
Описание таблички:
CREATE TABLE IF NOT EXISTS okato (
id serial primary key,
code ltree,
razdel smallint,
name varchar,
centrum varchar,
name_vector tsvector);
INSERT INTO okato (code, razdel, name, centrum, name_vector)
VALUES (%s,%s,%s,%s, (to_tsvector('russian', %s))); /*тут в цикле заполняю значения из python*/
CREATE INDEX name_vector_idx ON okato USING gin(name_vector);
На данный момент использую поиск через ILIKE, что , соответственно, очень медленно. Например:
EXPLAIN VERBOSE SELECT replace(ltree2text(code), '.', '_') AS code, nlevel(code) AS lvl, name
FROM okato
WHERE code @> ARRAY(SELECT code
FROM okato
WHERE name ILIKE '%ал%'
ORDER BY code LIMIT 2000);
-------------------------------------------------------------------------------------------------
Seq Scan on public.okato (cost=7342.70..13609.52 rows=199 width=65)
Output: replace(ltree2text(okato.code), '.'::text, '_'::text), nlevel(okato.code), okato.name
Filter: (okato.code @> $0)
InitPlan 1 (returns $0)
-> Limit (cost=7337.70..7342.70 rows=2000 width=34)
Output: okato_1.code
-> Sort (cost=7337.70..7382.51 rows=17924 width=34)
Output: okato_1.code
Sort Key: okato_1.code
-> Seq Scan on public.okato okato_1 (cost=0.00..6265.33 rows=17924 width=34)
Output: okato_1.code
Filter: ((okato_1.name)::text ~~* '%ал%'::text)
(12 rows)
Этот запрос выдаёт хорошие результаты, но отрабатывает около 10сек, отадает 3587т. записей.
Я читаю доку постгреса по индексам и полнотекстовому поиску.
Следующий мой шаг по полнотекстовому поиску таков:
EXPLAIN VERBOSE SELECT replace(ltree2text(code), '.', '_') AS code, nlevel(code) AS lvl, name
FROM okato
WHERE code @> ARRAY(SELECT code
FROM okato
WHERE name_vector @@ plainto_tsquery('russian','ал')
ORDER BY code LIMIT 2000);
---------------------------------------------------------------------------------------------------
Seq Scan on public.okato (cost=69.60..6336.41 rows=199 width=65)
Output: replace(ltree2text(okato.code), '.'::text, '_'::text), nlevel(okato.code), okato.name
Filter: (okato.code @> $0)
InitPlan 1 (returns $0)
-> Limit (cost=69.55..69.60 rows=17 width=34)
Output: okato_1.code
-> Sort (cost=69.55..69.60 rows=17 width=34)
Output: okato_1.code
Sort Key: okato_1.code
-> Bitmap Heap Scan on public.okato okato_1 (cost=4.41..69.21 rows=17 width=34)
Output: okato_1.code
Recheck Cond: (okato_1.name_vector @@ '''ал'''::tsquery)
-> Bitmap Index Scan on name_vector_idx (cost=0.00..4.41 rows=17 width=0)
Index Cond: (okato_1.name_vector @@ '''ал'''::tsquery)
(14 rows)
Этот в свою очередь отрабатывает за
100мс, отдает 42 записи.
Делаю выводы:
-первый случай устраивает по результату, но не по скорости
-второй по скорости =), но не по результату
-что-то я делаю не так!
Помогите привести полнотекстовый поиск к хорошему результату и скорости. Буду благодарен за подсказки.
