Как правильно сформулировать индексы?

Question

[Сергей]

Помогите сформулировать индексы, чтобы ускорить подзапрос вида (внутри Exists()):

SELECT 1
FROM "marketplace_product" U0
WHERE (U0."id" = АйДи из внешнего запроса AND
              (UPPER(U0."name"::text) LIKE UPPER('%подстрока%') OR
               UPPER(U0."short_description"::text) LIKE UPPER('%подстрока%') OR
               UPPER(U0."full_description"::text) LIKE UPPER('%подстрока%')))
LIMIT 1

Какие индексы тут могут помочь? Проблема в том, что запрос генерируется Django ORM и не совсем понятно как сделать подходящий индекс с помощью самого ORM.

Используется Django 4.2 и Postgres 13 (SQLite для тестов).

Comments

[Модератор]

Не надо ставить как можно больше тэгов. Лучше оставить один, но конкретный, с которым проблема.
См.п.3.1 Регламента. Также обратите внимание на п.3.4

[Shavadrius]

У меня есть несколько вопросов к логике запроса:
1. Если id - это идентификатор (я так понимаю, что уникальный) - то вся вторая часть после AND не нужна. Вернее нужна, но чтобы проверить что в одной записи, найденной по id, будет подстрока, что можно проверить и на бэкенде, а не в БД;
2. Думаю, что быстрее будет соединить три столбца в один и потом проверить на подстроку, но это только ощущения. Может так как написано будет и быстрее;
3. Разве в ORM нельзя самому написать запрос?

[Сергей]

Shavadrius
1. На бэкэнде не получится, потому что это (цитирую) "подзапрос вида (внутри Exists())", а используется он внутри WHERE. Для 1000к записей может и проканало бы, но не для 100кк.
2. С одной стороны да, но тогда индекс тоже надо сделать соответствующий. У меня получилось добиться parralel index scan, что думаю быстрее (на практике не заметил разницы, видимо слово попадалось в одном индексе).
3. Можно. Но зачем? У меня используется общая логика по поиску, которая требовала просто индексов.

Вообще я думаю там ещё кое-что оптимизировать, но именно эти условия формирует ORM в той части кода, которую не хочется менять. Вопрос был в том, как правильно именно джанговские индексы сформулировать, чтоб работали с LIKE и UPPER.

Answer 1

[Сергей]

Методом подбора и внимательного исследования стаковерфлоу, получилось что-то вроде такого:

1. Нужно в миграцию добавить

   django.contrib.postgres.operations.TrigramExtension()

   
   
2. Нужно для каждого поля создать индекс (на примере name)

   django.contrib.postgres.indexes.GinIndex(django.contrib.postgres.indexes.GinIndex(django.contrib.postgres.indexes.OpClass(Upper('name'), name='gin_trgm_ops')))

   
   

Это капец как неочевидно, но для поиска по icontains работает именно так. Сорян, что потревожил сообщество.