GrAndSE's blog

Оптимізація запитів для повнотекстового пошуку в PostgreSQL

Нещодавно я вже писав про реалізацію повнотекстового пошуку в PostgreSQL. На мій погляд стаття непогана, хоча мені на пошту прийшов коментар від читача, який сказав, що я дещо помиляюсь і деякі операції є зайвими. Я трішки не зрозумів в чому саме я виконую зайві операції, однак цілком можливо, оскільки я вперше зіштовхнувся з реалізацією повнотекстового пошука в PostgreSQL 7.x, а з того часу бугато речей було спрощено і деякі мої маніпуляції вже могли потрапити в статус deprecated. Як би там не було, з радістю та увагою спробую сприйняти будь-яку критику в свій бік. Особливо з огляду на те, що проблему з коментарями я виправив.

А сьогодні я хочу на прикладі розповісти про деякі тонкощі написання запитів для роботи з повнотекстовим пошуком

На чому ми зупинились

Будемо вважати, що вже є розгорнуто базу з попередньої статті. Сенсу повторювати весь шлях я не бачу, тільки скажу, що вибірка робиться трішки модифікованим запитом з попереднього матеріалу:

SELECT
id, title, ts_headline(body, q) AS head
FROM
index, to_tsquery('ukrainian', 'робота') AS q
WHERE
(idxfti @@ q)
ORDER BY ts_rank(idxfti, q) DESC
OFFSET 10
LIMIT 10


Тут додано обмеження в кількості отримуваних результатів та деякий зсув, а також змінено рядок за яким буде вестись пошук з метою збільшення кількості релевантних результатів. Така собі емуляція посторінкової навігації для великої вибірки.

Для кількості сторінок в індексі, що перевищує 2000 елементів, коли обсяг таблиці приблизно 20 МГб виконання цього запиту займає близкь 0,75-0,8 сек. Це доволі багатенько, оскільки сучані пошукові системи працюють майже міттєво.

Одразу ж скажу, що перше виконання запиту займає на майже 2 сек більше часу, тоді як повторне виконання подібного запиту зі зміною ключових слів вже працює саме з такими витратами часу. Цю проблему легко вирішити, організувався постійні конекти до БД. Гадати на кавовій гущі, чому ж так не буду, бо в інтернеті нічого розумного знайти не вдалось. Можливо хтось в коментарях пояснить в чому справа.

З чого ж складаються ці 0,75 сек? Одразу ж на думку спадає, що багато часу займає сортування. Легко перевірити:

SELECT
id, title, ts_headline(body, q) AS head
FROM
index, to_tsquery('ukrainian', 'робота') AS q
WHERE
(idxfti @@ q)
OFFSET 10
LIMIT 10


І покащення швидкодії дуже незначне - не більше 50 мс. Так в чому ж справа? Поглянуши на EXPLAIN для запиту, я зрозумів, що практично нічого тут не розумію, і здається все працює так як і має працювати:

Limit (cost=105.73..105.73 rows=1 width=180)
-> Sort (cost=105.73..105.73 rows=2 width=180)
Sort Key: (ts_rank(index.idxfti, q.q))
-> Nested Loop (cost=0.00..105.72 rows=2 width=180)
Join Filter: (index.idxfti @@ q.q)"
-> Function Scan on q (cost=0.00..0.01 rows=1 width=32)
-> Seq Scan on index (cost=0.00..80.31 rows=2031 width=148)


Тому почав копати крок за кроком. Почав з малого:

SELECT to_tsquery('ukrainian', 'робота')


Цей клиптик коду відпраьовує практично миттєво 10-20 мс. Вирішив додати запит данних, однак без додаткової обробки:

SELECT
id, title, body
FROM
index, to_tsquery('ukrainian', 'робота') AS q
WHERE
(idxfti @@ q)
OFFSET 10
LIMIT 10


І реузльтат, який я отримав дав мені відповідь, в чому ж таки проблема. Цей запит виконується за 250-300 мс, що значно прийнятніше в плані швидкодії.Гльмом виявилась ts_headline, яка повертає місце, де було знайдено шукане ключове слово. Однак відкинути корисну інформацію не гарною ідеєю.

Що ж робити?

В усіх мануалах про оптимізацію запитів пишуть, про те що треба зменшувати кількість данних, що обирається, тобто виймати з бази тільки те, що потрібно. Якщо ще зменшити, кількість того, що виймається запитом, то отримаємо ще кращий результат:

SELECT
id
FROM
index,
to_tsquery('ukrainian', 'робота') AS q
WHERE
(idxfti @@ q)
ORDER BY ts_rank(idxfti, q) DESC
LIMIT 10
OFFSET 0


Виграли ще 50 мілісекунд. А що, якщо зменшити кількість даних, які вимушена обробляти функція ts_headline? Зробити це можна через вкладений запит, та синтаксичну конструкцію IN:

SELECT
title,
ts_headline(body, q) AS head
FROM
index,
to_tsquery('ukrainian', 'робота') AS q
WHERE
id IN (
SELECT
id
FROM
index,
to_tsquery('ukrainian', 'робота') AS q
WHERE
(idxfti @@ q)
OFFSET 10
LIMIT 10
);


В даному запиті спочатку обираються тілкі id елементів, що відповідають заданим умовам пошуку, а потім вже виконується вибір даних, для елементів id яких знаходится в списку обраних. Це працює і отримуємо час виконання запиту в рамках 450-500 мс. І сортування за ранком жодним чином не є проблемою, оскільки виконується всередині вкладеного запиту:

SELECT
title,
ts_headline(body, q) AS head,
url
FROM
index,
to_tsquery('ukrainian', 'робота') AS q
WHERE
id IN (
SELECT
id
FROM
index,
to_tsquery('ukrainian', 'робота') AS q
WHERE
(idxfti @@ q)
ORDER BY ts_rank(idxfti, q) DESC
OFFSET 10
LIMIT 10
);


В таком вигляді запит працює вс з тією ж швидкодією. Ніяких помітних втрат.

Ну що ж, зменшення затрат помітне: в 1,5 рази і ще й невеличкий запас залишився.

А тепер спробуємо в умовах, ближчих до реальних. 85+ тисяч проіндексованих документів, обсяг БД 900+ МГб. Як Вам? Мені подобається. Час виконання запиту з якого ми почали від 11 до 13 сек, тоді як його оптимізований брат вписується в 5 секунд. Як бачите, чим більший об'єм БД тим більший приріст отримуємо. І не кажіть мені після цього, що оптимізація є марними витратами часу.

Індексація та інші цікавості

Ще деякий час можна виграти створивши gin індекс. Детальніше можна почитати в документаціїї, а для БД з якою ми працювали SQL для генерації індексу матиме вигляд:

CREATE INDEX fti
ON "index"
USING gin
(idxfti);


База розростається (так для таблиці в 900 МГб індекс в мене займає додаткових 300 МГб з гаком), однак час виконання запиту зменшується десь в 1,5 рази. Тобто ще десь 1,5 секунди для БД з 82 тис. записів. Думаю, що реузльтат непоганий.

Також правильне налаштування БД може суттєво вплинути на швидкодію. Однак це не до мене - на радість завжди поряд опиняється людина, що знає що й до чого, тому особливих рекомендацій що до тюнингу PostgreSQL я не зможу дати. Всі мої рекомендації можна знайти з легкістю на першій же сторінки пошуку в Google. :)

Взагалі то методи оптимізації, які я показав в цьому матеріалі можна перенести на ряд інших випадків. Так, я доволі часто використовую IN. Та й про індексацію полів у БД кажуть на кожному кроці. Просто треба бути уважним і не лінуватись поміркувати, а як можна зробити краще та швидше. Зазвичай, оптимізація роботи з БД найдешевший спосіб прискорити роботу багатьох проектів, пов'язаниз з базами даних. Так що бажаю успіхів і сподіваюсь стане внагоді.

Дякую за увагу!