Как повысить производительность TrigramSimilarity?

Question

[axel]

Есть таблица с ~ 300 тысяч записей, БД Postgres. Необходимо вести по таблице поиск с опечатками, для чего я использую TrigramSimilarity.

SomeModel.objects.annotate(sim=TrigramSimilarity('title', q)).filter(sim__gt=0.05).order_by("-sim")

В title сделан GIN-индекс.
Проблема в том, что поиск выполняется очень, просто нестерпимо долго, по 15-20 секунд. Вопрос, собственно, в том, каким образом повысить производительность.

Comments

[ky0]

А постгрес настроен как следует? 300к записей по идее должны в память помещаться полностью.

[axel]

ky0, хотелось бы знать, что имеется в виду под "настроен как следует" ?

[ky0]

axel, параметры оперативной памяти, доступа к диску, параллелизм и всё такое.

[axel]

ky0,
work_mem = 1024MB
shared_buffers = 512MB
effective_cache_size = 6GB
wal_buffers = 16
Какие еще параметры принципиальны?

[Melkij]

Запрос-то какой выполняется? А то окажется, что вопрос вообще к базе не относится, потому что ваш код вычитывает всю таблицу и считает её локально.

Параметры вы назвали странные. Судя по effective_cache_size + shared_buffers не похоже, что у вас есть пара сотен гигабайт RAM свободных. Для тяжёлого OLAP запроса в 1-5 потоков всего на сервер, где такой work_mem бывает нужен, соответственно опять же буфер слишком маленький.
Почему вы назвали wal_buffers важной настройкой - для меня загадка.

[axel]

Melkij, запрос ORM Django генерирует такой:

SELECT DISTINCT "catalog_catalogitem"."id", "catalog_catalogitem"."seo_title", "catalog_catalogitem"."seo_keywords", "catalog_catalogitem"."seo_description", "catalog_catalogitem"."updated",
"catalog_catalogitem"."created", "catalog_catalogitem"."status", "catalog_catalogitem"."sort", "catalog_catalogitem"."itm_code", "catalog_catalogitem"."city_id", "catalog_catalogitem"."firm_id",
"catalog_catalogitem"."group_id", "catalog_catalogitem"."subgroup_id", "catalog_catalogitem"."producer_country_id", "catalog_catalogitem"."title", "catalog_catalogitem"."unit", "catalog_catalogitem"."pack",
"catalog_catalogitem"."description", "catalog_catalogitem"."image", "catalog_catalogitem"."cost", "catalog_catalogitem"."currency", "catalog_catalogitem"."payment", "catalog_catalogitem"."sale",
"catalog_catalogitem"."manufacture", "catalog_catalogitem"."is_poster", "catalog_catalogitem"."search_string", "catalog_catalogitem"."search_vector",
SIMILARITY("catalog_catalogitem"."search_string", 'remnot') AS "sim"
FROM "catalog_catalogitem" WHERE ("catalog_catalogitem"."status" = true AND SIMILARITY("catalog_catalogitem"."search_string", 'remnot') > 0.05) ORDER BY "sim" DESC

По поводу параметров - я выполнил рекомендации, указанные здесь
https://wiki.postgresql.org/wiki/Slow_Query_Questi...
Потому эти параметры сюда и перечислил.

[Melkij]

Судя по использованию SIMILARITY - вы, вероятно, используете pg_trgm.
Index support есть во-первых только с указанием gin_trgm_ops или gist_trgm_ops и во-вторых - для описанных операторов. Не для функций.
Смотрите попутно вот неплохой ответ: https://dba.stackexchange.com/a/103823

[axel]

Melkij, да, совершенно верно, pg_trgm использую. GIN index и был у меня сделан таким образом:

CREATE INDEX catalog_item_srch_str ON catalog_catalogitem USING GIN (search_string gin_trgm_ops);

и
CREATE EXTENSION btree_gin;

тоже проделал. Но ищется-то медленно (

Вы что-то о настройке постгреса говорили, можете подсказать, какие параметры подкрутить попробовать?

[Melkij]

Но ищется-то медленно (

Ещё раз и внимательно:

для описанных операторов. Не для функций.

index support is bound to operators in Postgres, not to functions.

По настройкам - надо понимать под какое окружение настраивать. CPU/RAM/диски, выделенная железка под базу или ещё всякое здесь же крутится, объёмы данных в гигабайтах.

[axel]

Melkij, не очень понял про операции и функции, я просто первый раз с Постгресом, до этого использовал mysql (
По поводу настроек - рядом с базой еще крутится много всего, базы объем небольшой, 400 мб. Оперативы 64 Гб.

[Melkij]

job_title % 'sales executive' - может использовать *_trgm_ops индекс. Это оператор
similarity(job_title, 'sales executive') - не может. Это функция

На 400мб базе что-то крутить смысла мало.

[axel]

Melkij, то есть, для ускорения запроса сделать ничего не получится?

[Melkij]

В текущем виде - нет. Надо переписывать запрос, см. ответ на SO