Как мне оптимизировать этот полнотекстовый поисковый запрос? (цветок) (1,5 мил строки)
В настоящее время у меня есть полнотекстовый поисковый запрос в PostgreSQL (см. Ниже), который просматривает одну таблицу из 1,5 миллионов строк, чтобы найти все элементы, которые соответствуют терминам "Все", а также "Любой".
Запрос выполняется правильно и с посредственной скоростью (~2-3 секунды) для запросов, которые дают мало результатов. И на ужасных скоростях с результатами более 100000 матчей (~15-100 секунд)
Запрос сначала упорядочивает результаты по типу термина (все совпадают термины, а затем совпадают любые термины), а затем подопределяет результаты путем вычисления релевантности ts_rank_cd. (А также более простые варианты, где он упорядочен по уже известным столбцам, которые могут быть проиндексированы, например, длительность)
SELECT
*,
ts_rank_cd(textsearchable, query_any_terms) AS relevance,
textsearchable @@ query_all_terms AS all_terms,
sum(1) over (PARTITION BY textsearchable @@ query_all_terms) AS match_method_total,
sum(1) over () AS all_matched_total
FROM
videos,
to_tsquery(?) AS query_any_terms,
to_tsquery(?) AS query_all_terms
WHERE
website IN (?)
AND textsearchable @@ query_any_terms
AND duration_in_seconds >= ?
AND duration_in_seconds <= ?
ORDER BY
all_terms DESC,
relevance DESC
LIMIT ?
OFFSET ?
Все соответствующие столбцы были проиндексированы, и системный мониторинг показывает, что память и процессор используются не в полной мере, а узким местом является дисковый ввод-вывод.
Сервер является Ubuntu Server 10.04. Память и мощность процессора могут быть легко увеличены с помощью бэкэнда динамически по мере необходимости для решения.
В настоящее время база данных является статической, когда она находится в рабочем состоянии, и запись в базу данных не будет производиться на рабочем сервере. Поэтому возможно полностью и точно создать индекс, который остается точным, если это выгодно.
Любая помощь в поиске любого пути улучшения будет принята с благодарностью. Дополнительная информация может быть предоставлена своевременно по запросу.
1 ответ
Загрузил БД в оперативную память с помощью tmpfs, и время запроса значительно улучшилось (т.е. оно выросло с ~100 с до ~2 с с).
см.: http://www.slideshare.net/pgconf/five-steps-perform2009