Как ускорить поиск в Django в 100 раз: полнотекстовый поиск на pg_trgm, GIN с профилировкой EXPLAIN ANALYZE

В этой статье вы узнаете, как реализовать профессиональный полнотекстовый поиск в Django 5 с PostgreSQL 16. Мы рассмотрим расширения pg_trgm, unaccent, индексацию GIN, ранжирование через SearchRank, поиск с морфологией (config='russian'), подсветку совпадений, fuzzy-поиск и даже профилирование SQL-запросов в psql через EXPLAIN ANALYZE.

Оглавление:

Теория сопровождается реальными примерами запросов на БД в 10 000 постов и разбором ошибок, которые совершают даже опытные разработчики.

1. Введение и настройка

2. Поиск через SearchVector + SearchQuery

3. Индексация и GIN

4. Расширения PostgreSQL: pg_trgm, unaccent

5. Оптимизация через EXPLAIN ANALYZE

6. Сравнение плохих и хороших запросов

7. Реальные практические задачи

8. Частые вопросы на собеседованиях

9. Заключение

Полнотекстовый поиск позволяет находить текстовые совпадения в полях модели с учётом морфологии языка, релевантности, весов полей, опечаток, и даже подсветки совпадений. Это must-have фича для блога, каталога, поиска по статьям и т.д.

Все примеры ниже используют config='russian' — это позволяет PostgreSQL понимать морфологию русского языка: "Пушкин", "Пушкина", "поэтом" и т.д. Смотри статью Дзен чтобы правильно настроить окружение: https://dzen.ru/a/aEl0bGvEnjRyL8bC?share_to=link
Смотри статью на Дзен, чтобы собрать и наполнить постами приложение blog на Django 5:
https://dzen.ru/a/aES0yqlaymyfifxa?share_to=link

Список постов в приложении blog по которым будут отработаны примеры.

Общая настройка

Запуск Python-интерпретатора Django

Запустите следующую команду в терминале, чтобы открыть интерактивную оболочку Python:

python manage.py shell_plus

Установка shell_plus - если не работает.

pip install django-extensions

После установки добавьте 'django_extensions' в INSTALLED_APPS (в settings.py)

Загрузка 10000 постов через фикстуры

Для проведения полноценных экспериментов и решения сложных задач полнотекстового поиска создадим с помощью ChatGPT фикстуры для модели Posts на 10000 постов о русских поэтах от 100 разных авторов.
Файлы фикстур ищите в моем GitHub по ссылке:
https://github.com/myasoedas/Django-5/blob/main/01-Blog/blog-project/README.md

Загрузим файл с фикстурами 100 авторов постов users_fixtures.json в папку проекта, где лежит файл manage.py.

Используйте команду Django для загрузки:

python manage.py loaddata users_fixtures.json

Загрузим файл фикстур blog_fixtures_ru_fulltext_10k.json в папку проекта, где лежит файл manage.py.

Используйте команду Django для загрузки:

python manage.py loaddata blog_fixtures_ru_fulltext_10k.json

Теперь в базу данных добавлены 100 авторов и 10000 постов про русских поэтов.

Теперь все готово, чтобы приступить к решению задач полнотекствого поиска в приложении blog.

Затем выполните в консоли следующие строки:

from django.contrib.auth.models import User

from blog.models import Post

user = User.objects.get(username='alex')

Импоритируйте необходимые модули для работы полнотекстового поиска

from django.contrib.postgres.search import (

SearchVector, SearchQuery, SearchRank,

SearchHeadline, TrigramSimilarity, SearchVectorField

)

from django.db.models import F

SearchVector

Объединяет один или несколько текстовых полей модели в один "поисковый вектор", пригодный для полнотекстового поиска.

Пример использования:

SearchVector('title', 'body', config='russian')

Нужен, чтобы указать, какие поля участвуют в поиске. Можно задать веса и морфологию (например, config='russian'), чтобы поддерживать склонения.

Добавьте поле SearchVectorField и GinIndex к нему в модель Post.

from django.contrib.postgres.search import SearchVectorField

search_vector = SearchVectorField(null=True, editable=False)

GinIndex(fields=["search_vector"]),

Создайте и примените миграции.

python manage.py makemigrations

python manage.py migrate

Результат:

Создай файл signals.py в приложении blog.

Вставь туда код сигнала:

# blog/signals.py

from django.db.models.signals import post_save

from django.dispatch import receiver

from django.contrib.postgres.search import SearchVector

from .models import Post # обязательно импортируй модель

@receiver(post_save, sender=Post)

def update_search_vector(sender, instance, **kwargs):

Post.objects.filter(id=instance.id).update(

search_vector=(

SearchVector("title", weight="A", config="russian") +

SearchVector("body", weight="B", config="russian")

)

)

Зарегистрируй сигнал в apps.py.

Проверь работу сигнала.

Запустите shel_plus:

python manage.py shell_plus

Выполните следующие команды:

from blog.models import Post

from django.contrib.auth import get_user_model

User = get_user_model()

alex = User.objects.get(username="alex")

post = Post.objects.create(

title="Лермонтов: Кавказ",

body="Поездка на юг в 1841 году, мысли и предчувствия",

author=alex

)

# Смотри: должно появиться в консоли:

# [SIGNAL] Обновляем search_vector для поста <id>

post.refresh_from_db()

print(post.search_vector)

Что должно произойти:

Вы увидите что-то вроде:

[SIGNAL] Обновляем search_vector для поста 11005

'кавказ':2A 'лермонтов':1A 'мысль':8B 'поездк':4B 'предчувств':10B 'юг':6B

Теперь когда сигнал работает нам нужно вручную проиндексировать 10000 постов в нашей базе данных с помощью скрипта.

Сделаем это надёжно и переиспользуемо: через management-команду

Создайте файл:

blog/management/commands/rebuild_search_vectors.py

from django.core.management.base import BaseCommand

from django.contrib.postgres.search import SearchVector

from blog.models import Post

class Command(BaseCommand):

help = "Пересчитывает поле search_vector для всех постов"

def handle(self, *args, **kwargs):

updated = Post.objects.update(

search_vector=(

SearchVector("title", weight="A", config="russian") +

SearchVector("body", weight="B", config="russian")

)

)

self.stdout.write(self.style.SUCCESS(f"Обновлено постов: {updated}"))

Выполните команду:

python manage.py rebuild_seach_vectors

Результат:

После обновления вектора проверьте выборку:

from blog.models import Post

Post.objects.filter(search_vector__isnull=False).count()

Результат:

Теперь все наши посты векторизированы и каждый новый пост по сигналу автоматически будет векторизирован и сохранен в поле search_vector.

SearchQuery

Представляет запрос для полнотекстового поиска.

Пример использования:

SearchQuery('пушкин', config='russian')

Нужен для формирования условия поиска: находит слова, синонимы, формы и т.д., в зависимости от config.

SearchRank

Вычисляет релевантность соответствия между SearchVector и SearchQuery.

Пример использования:

SearchRank(vector, query)

Позволяет сортировать результаты по убыванию релевантности, как в Яндекс-поиске.

SearchHeadline

Создаёт сниппет (аннотацию) с подсветкой найденных слов в тексте.

Пример использования:

SearchHeadline('body', query, config='russian')

Нужен, чтобы визуально подсветить найденные фразы в результатах поиска, как в поисковиках.

TrigramSimilarity

Измеряет похожесть строк (например, Пушкин vs Пушкн, Pushkin) с помощью триграмм.

Пример использования:

TrigramSimilarity('title', 'пушкин')

Полезен для поиска с опечатками, fuzzy-поиска, похожих названий.

Важно: работает только если включено расширение pg_trgm в PostgreSQL.

Примечание:

pg_trgm — расширение для поиска по триграммам.

pg_trgm (PostgreSQL Trigram) — это расширение, реализующее поиск по схожести строк с использованием триграмм. Триграмма — это последовательность из трёх подряд идущих символов (включая пробелы), например:

'пушкин' → {' пу', ' пуш', 'ушк', 'шки', 'кин', 'ин '}

Поиск работает по схожести множества триграмм между двумя строками: чем больше триграмм совпадает, тем выше "похожесть".

Когда и зачем использовать pg_trgm

1. Поиск с опечатками:
   Пользователь вводит пушкн, а ты хочешь, чтобы ему нашлось Пушкин.
2. Поиск похожих названий:
   Особенно важно в блогах, новостных сайтах и e-commerce (например, iPhone 13 и iPhne 13).
3. LIKE '%substring%':
   Без триграмм LIKE работает медленно, потому что не может использовать индексы.
   С pg_trgm и GIN-индексом — в разы быстрее!

Пример использования на Django 5+:

similarity__gt=0.3 — порог "похожести" (от 0 до 1).

Как включить pg_trgm в базе данных в PostgreSQL

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Индексация - добавление GIN-индексов

В Django 5 GIN-индексы необходимо добавлять через модели данных приложения

Добавим в модель Post GIN-индексы для поля title и поля body для повышения скорости полнотекстового поиска на большом количестве строк в базе данных:

Добавили в код модели Post GIN-индексы для поля title и поля body, чтобы сделать полнотекстовый поиск быстрым на большом массиве данных.

При добавлении GIN-индексов в код модели Post, необходимо добавить строку импорта:

from django.contrib.postgres.indexes import GinIndex

После добавления GIN-индексов в код модели Post необхолимо выполнить команды для создания и запуска миграции:

python manage.py makemigrations

python manage.py migrate

Теперь GIN-индексы из кода модели Post добавлены в таблицу базы данных и будут работать. А это значит, что полнотекстовый поиск в нашем приложении blog будет работать в полный рост даже на большом количестве постов.

В примерах мы будем работать с 10000 строк в базе данных.

Использование unaccent с Django 5 и PostgreSQL 16

unaccent — расширение PostgreSQL для нормализации текста, которое удаляет диакритические знаки (акценты, ударения и т.п.) из символов.

Пример:

'Есенин' ↔ 'Esenin'

'café' → 'cafe'

'Müller' → 'Muller'

Для работы с полнотекстовым поиском создадим отдельное приложение seach в нашем проекте:

python manage.py startapp seach

Подключим приложение seach в файле settings.py:

'search.apps.SearchConfig',

В приложении seach создадим файл functions.py, где разместим код функции Unaccent, которая при вызове будет удаляет диакритические знаки (акценты, ударения и т.п.) из символов строки поиска.

Добавим код функции Unaccent в файл functions.py:

Давайте разберём построчно код.

from django.db.models import Func

Импорт Func

Мы импортируем базовый класс Func из Django ORM — это универсальный способ обернуть SQL-функцию и использовать её как часть запросов в Django ORM.
Например, такие SQL-функции, как LOWER(), CONCAT(), COALESCE(), или в нашем случае — UNACCENT().

class Unaccent(Func):

Создаём кастомный класс Unaccent, наследуя Func.
Так мы определяем свою обёртку вокруг PostgreSQL-функции unaccent, чтобы использовать её удобно прямо из ORM-запросов:

Пример использования:

Post.objects.annotate(clean_title=Unaccent('title')).filter(clean_title__icontains='esenin')

Следующая строка кода:

function = 'unaccent'

Указываем имя SQL-функции, которую должен вызывать Func
Это имя будет подставлено в SQL-запрос. Django построит такую конструкцию:

SELECT unaccent("title") FROM ...

Следующавя строка кода:

arity = 1

Указываем арность (число аргументов функции)
Здесь arity = 1, потому что unaccent() принимает один аргумент — строку, из которой нужно убрать диакритику:

SELECT unaccent('Ésenin') → 'Esenin'

Если бы функция принимала, скажем, два аргумента (например substring(str, pattern)), вы бы указали arity = 2.

Это особенно полезно, когда:

• вы используете PostgreSQL;
• у вас текст на русском, английском, с символами ё/е, é/e;
• вам нужен поиск без учёта диакритики:

.filter(Unaccent(F("title"))__icontains="esenin")

Когда и зачем использовать unaccent

1. Поиск без учёта акцентов:
   Особенно актуально, если пользователь вводит на англ. раскладке (Esenin) или без учёта диакритики.
2. Транслитерация:
   Можно комбинировать с функцией translate() или внешними словарями для упрощённого поиска русских имён в латинской форме.
3. Лучший UX для поиска:
   Пользователь не обязан вводить точную форму — unaccent помогает находить нужное даже с упрощением.

Пример использования Unaccent в запросах Django ORM

Это позволит найти пост "Есенин — великий поэт", даже если пользователь ищет "esenin".

Дополнительно: нормализуем и регистр (LOWER())

Что это даёт:

• Поиск без учёта регистра (например: esenin, Esenin, ESENIN — всё найдётся).
• Работает на уровне базы данных, быстро и эффективно.

SearchVectorField

Поле модели Django, которое хранит готовый поисковый вектор (для индексирования).

Пример использования:

from django.contrib.postgres.indexes import GinIndex

class Post(models.Model):

search_vector = SearchVectorField(null=True)

class Meta:

indexes = [GinIndex(fields=['search_vector'])]

Ускоряет поиск, позволяя индексировать SearchVector (GIN-индексы).

from django.db.models import F

Позволяет ссылаться на значения других полей в одном и том же запросе.

Пример использования:

SearchVector(F('title'), weight='A') + SearchVector(F('body'), weight='B')

Для динамической генерации поисковых векторов и задания весов.

Итого: практическое применение

from django.contrib.postgres.search import (

SearchVector, SearchQuery, SearchRank,

SearchHeadline, TrigramSimilarity, SearchVectorField

)

from django.db.models import F

from blog.models import Post # предполагаемая модель

# Создаём поисковый запрос с русской морфологией

query = SearchQuery('пушкин', config='russian')

# Формируем вектор поиска с разными весами полей

vector = (

SearchVector('title', weight='A', config='russian') +

SearchVector('body', weight='B', config='russian')

)

# Выполняем поиск с ранжированием, подсветкой и триграммным сравнением

posts = Post.objects.annotate(

rank=SearchRank(vector, query),

headline=SearchHeadline('body', query, config='russian'),

similarity=TrigramSimilarity('title', 'пушкин')

).filter(rank__gte=0.1).order_by('-rank')

Практические задачи полнотекстового поиска на русском языке в Django 5

Уровень: Junior с учебным уклоном. Каждая задача включает:

- Неправильный (медленный) запрос;

- Правильный (оптимальный) запрос;

- Пошаговую инструкцию, как замерить и проанализировать SQL-запрос.

В терминале проекта выполните:

python manage.py shell_plus --print-sql

Результат:

Задача 1: Поиск по слову "пушкин" в заголовке поста

Пример медленного неэффективного запроса:

Post.objects.filter(title__icontains="пушкин").first()

Результат - Execution time 0.044091s:

❌ Этот запрос будет использовать ILIKE '%...%', что плохо масштабируется без GIN + pg_trgm.

Пример оптимального запроса:

from django.contrib.postgres.search import TrigramSimilarity

Post.objects.annotate(similarity=TrigramSimilarity("title", "пушкин")).filter(similarity__gt=0.3).order_by("-publish").first()

Результат - Execution time: 0.001575s:

✅ Описание механики запроса:
Запрос вычисляет степень похожести между строкой 'пушкин' и полем title для каждой записи, отфильтровывает только те, где similarity > 0.3, и сортирует результат по дате публикации (publish DESC), возвращая самую свежую запись.

⚡️ Почему он супер быстрый:
Запрос не сортирует по similarity, а использует индекс по publish, благодаря чему PostgreSQL выполняет Index Scan, быстро отфильтровывает записи по similarity, и не тратит время на полную сортировку.

Как измерить производительность SQL-запроса через EXPLAIN ANALYZE в PostgreSQL

Это ключевой навык для джунов и обязательный для мидлов и сеньоров.

Что делает EXPLAIN ANALYZE?

• EXPLAIN — показывает план выполнения запроса.
• ANALYZE — выполняет сам запрос и показывает время, затраченные ресурсы, индекс ли используется и т.п.

Шаги: как использовать EXPLAIN ANALYZE с Django ORM

Шаг 1. Выполните ORM-запрос в shell_plus --print-sql

python manage.py shell_plus --print-sql

Post.objects.filter(title__icontains="пушкин").first()

На экране вы увидите SQL-запрос:

Шаг 2. Скопируйте этот SQL-запрос

Шаг 3. Подключитесь к PostgreSQL через psql

psql -U django_user -d django_db

Результат:

где:

• django_user — имя пользователя PostgreSQL (из .env);
• django_db — имя базы данных.

Шаг 4. Выполните команду

EXPLAIN ANALYZE

SELECT "blog_post"."id",

"blog_post"."title",

"blog_post"."author_id",

"blog_post"."publish",

"blog_post"."created",

"blog_post"."updated",

"blog_post"."status"

FROM "blog_post"

WHERE UPPER("blog_post"."title"::text) LIKE UPPER('%пушкин%')

ORDER BY "blog_post"."publish" DESC

LIMIT 1;

Обратите внимание сверху мы добавили к скопированному SQL коду команду EXPLAIN ANALYZE! Убрали неинтересные нам поля body, slug.

Результат:

Расшифровка результата:

Проблема: %пушкин% — это LIKE '%...%', оно не может использовать стандартные B-Tree индексы → приводит к полному сканированию таблицы (Seq Scan).

Анализ плана выполнения:

Limit (cost=2965.01..2965.02 rows=1 width=86) (actual time=34.687..34.688 rows=1 loops=1)

Limit — ограничение результата до 1 строки

• rows=1 — планировщик ожидал одну строку.
• actual time=34.688 — фактическое время выполнения этой части запроса.
• Время сработки: 34.7 мс — уже немного тревожно для поиска по одной строке.

Сортировка результатов по publish DESC

• Хотя вернулась только одна строка, всё равно пришлось отсортировать все подходящие 1629 строк перед LIMIT.
• Используется top-N heapsort — нормальный метод, если нужно только top-1 запись.
• 25kB памяти — немного, но не ноль, если таких запросов много — станет нагрузкой.

Самая проблемная часть — Seq Scan (последовательный скан)

-> Seq Scan on blog_post

(cost=0.00..2965.00 rows=1 width=86)

(actual time=0.125..34.116 rows=1629 loops=1)

Проблема:

• PostgreSQL просмотрел все 11000 строк таблицы blog_post (1629+9371 = 11000).
• actual time=34.116 — на это и ушло основное время запроса.
• Это классическая боль при LIKE '%...%'.

Filter: (upper((title)::text) ~~ '%ПУШКИН%'::text)

Rows Removed by Filter: 9371

Почему всё так плохо?

• ILIKE (или UPPER(title) LIKE UPPER('%пушкин%')) не использует индекс.
• Фильтрация происходит вручную над всеми строками.
• 9371 строк не подошли, 1629 — прошли фильтр.

Planning Time: 0.336 ms

Execution Time: 34.750 ms

• Планирование заняло мало (0.336 ms) OK.
• Основное — это выполнение (34.75 ms) много!

⚠️ Вывод

Текущий запрос:

• Медленный (34 мс на 1 запись — это много).
• Не использует индекс.
• Плохо масштабируется — будет в 10 раз хуже на 100К строках.

✅ Как ускорить?

1. Мы подключили расширения в нашей базе данных PostgreSQL:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE EXTENSION IF NOT EXISTS unaccent;

2. Создали индексы GinIndex в модели Post:

3. Выполнили миграции:

python manage.py makemigrations

python manage.py migrate

4. Перепишем запрос с TrigramSimilarity:

from django.contrib.postgres.search import TrigramSimilarity

Post.objects.annotate(

similarity=TrigramSimilarity("title", "пушкин")).filter(similarity__gt=0.3).order_by("-publish").first()

Результат:

5. Скопируйте запрос (я убрал ненужные поля slug, body):

EXPLAIN ANALYZE

SELECT "blog_post"."id",

"blog_post"."title",

"blog_post"."author_id",

"blog_post"."publish",

"blog_post"."created",

"blog_post"."updated",

"blog_post"."status",

SIMILARITY("blog_post"."title", 'пушкин') AS "similarity"

FROM "blog_post"

WHERE SIMILARITY("blog_post"."title", 'пушкин') > 0.3

ORDER BY "blog_post"."publish" DESC

LIMIT 1;

6. Выполните скопированный запрос в psql.

Результат:

Расшифровка результата:

Отличный результат! Мы получили идеальный план выполнения SQL-запроса — он использует индекс, фильтрует эффективно, и всё работает быстро (<1 мс).

Давайте разберём EXPLAIN ANALYZE построчно, с пояснениями, как на собеседовании senior-уровня, и с уклоном в обучение.

Исходный запрос:

Расшифровка QUERY PLAN

Limit (cost=0.29..3.49 rows=1 width=90)

(actual time=0.834..0.835 rows=1 loops=1)

Limit

• Что делает: Ограничивает результат до одной строки.
• Планируемая стоимость: от 0.29 до 3.49 — PostgreSQL ожидал, что он быстро найдёт результат.
• Фактическое время: 0.834 ms — подтверждает, что ожидание оправдалось.
• Вывод: 🚀 супер быстро, нет перебора всей таблицы.

-> Index Scan using blog_post_publish_bb7600_idx on blog_post

(cost=0.29..11751.42 rows=3667 width=90)

(actual time=0.830..0.830 rows=1 loops=1)

Index Scan

• Что делает: использует индекс blog_post_publish_bb7600_idx по полю publish, чтобы идти в обратном порядке (DESC).
• Почему это круто: PostgreSQL не сканирует всю таблицу, а идёт по отсортированным данным.
• Фактическое время: всего 0.830 ms — это почти мгновенно.
• Предсказание: 3667 строк могут удовлетворять условию.

Filter: (similarity((title)::text, 'пушкин'::text) > '0.3')

Rows Removed by Filter: 10

Filter

• Применяется уже после извлечения строк из индекса.
• similarity(...) > 0.3 — не участвует в сортировке, но применяется к каждой записи.
• Удалено по фильтру: 10 строк — PostgreSQL проверил 11 строк, и 1 прошла (остальные — нет).

Planning Time: 0.368 ms

Execution Time: 0.899 ms

Время

• Planning Time: сколько заняло построить план (≈ 0.36 мс).
• Execution Time: общее время выполнения запроса (≈ 0.9 мс) — очень хорошо!

Вывод:

• неэффективный запрос выполнил задачу за 34.750 ms.,
• эффективный запрос выполнил задачу за 0.899 ms.

Как видно из примера разница во времени выполнения запроса существенная.

Задача 2: Найти наиболее релевантный и при этом самый свежий пост, максимально похожий на "лермонтов"

Цель:

Мы хотим не просто найти свежий пост, содержащий пушкин, а найти самый похожий пост (по similarity), но если несколько постов имеют одинаковую похожесть — отдать выбор самому свежему из них.

Почему задача полезна:

• Показывает сложную сортировку по similarity + publish DESC;
• Вызывает heap sort, и помогает объяснить, почему такие запросы дорогие;
• Объясняет, как выбрать топ-N наиболее релевантных записей, например для UI выдачи;
• Отлично иллюстрирует разницу между скоростью и качеством ранжирования.

❌ Пример неэффективного запроса

Post.objects.filter(title__icontains="лермонтов").order_by("-publish").first()

Результат:

Скопируйте SQL код:

EXPLAIN ANALYZE

SELECT "blog_post"."id",

"blog_post"."title",

"blog_post"."author_id",

"blog_post"."publish",

"blog_post"."created",

"blog_post"."updated",

"blog_post"."status"

FROM "blog_post"

WHERE UPPER("blog_post"."title"::text) LIKE UPPER('%лермонтов%')

ORDER BY "blog_post"."publish" DESC

LIMIT 1;

Запустите скопированный SQL код в psql.

Результат:

Расшифровка результата:

Расшифровка QUERY PLAN

Limit (cost=2965.01..2965.02 rows=1 width=86)

(actual time=37.337..37.338 rows=1 loops=1)

Limit 1 — мы просим только одну строку. Это хорошо.

actual time = ~37 ms — это уже тревожный сигнал. Для LIMIT 1 запрос должен работать за миллисекунды, а не десятки миллисекунд.

Вложенный оператор Sort

-> Sort

Sort Key: publish DESC

Sort Method: top-N heapsort Memory: 25kB

PostgreSQL должен отсортировать все строки, прошедшие фильтр, по убыванию publish.
Поскольку нужен только один пост, он применяет top-N heapsort, т.е. сортирует только верхушку (это неплохо).

Основное "узкое горлышко" — Seq Scan

-> Seq Scan on blog_post

Filter: (upper((title)::text) ~~ '%ЛЕРМОНТОВ%'::text)

Rows Removed by Filter: 9387

Rows Returned: 1613

🔴 Seq Scan = "последовательное сканирование всей таблицы".

Это значит, что PostgreSQL прочитал ВСЕ 11 000 записей (9387+1613) и каждую строку проверил вручную, подходит ли она под шаблон.

Почему? Потому что:

UPPER(title) LIKE UPPER('%лермонтов%')

ломает любые индексы. Даже если у вас был бы обычный индекс по title, он не использовался бы — потому что:

• LIKE '%...%' делает невозможной префиксную оптимизацию;
• UPPER() — оборачивает поле, и это делает индекс бесполезным (если он не функциональный).

Что значит cost=2965.00?

cost=2965.00

• Это предполагаемая стоимость в абстрактных единицах (PostgreSQL сам вычисляет, насколько "дорого" выполнение запроса).
• Чем больше стоимость, тем тяжелее запрос. В данном случае, PostgreSQL говорит: "Я должен прочитать почти всю таблицу и отсортировать".

Вывод

❌ Проблема:

• UPPER(title) LIKE '%лермонтов%' не использует индексы
• Сканируется вся таблица (в будущем на 100К+ записей — будет > секунды)
• Сортируется весь результат, чтобы выбрать один пост.

✅ Пример эффективного запроса

1. Полнотекстовый поиск с ранжированием (SearchRank)

SearchVector + SearchRank (полнотекстовый поиск) в PostgreSQL учитывает:

• Морфологию (с учётом русского языка);
• Вес разных полей (title > body);
• Релевантность (tf-idf);
• Стоп-слова.

Django ORM

Выполните команду:

python manage.py shell_plus --print-sql

Запустите код Django ORM запроса

from django.contrib.postgres.search import (

SearchVector, SearchQuery, SearchRank, SearchHeadline

)

query = SearchQuery("лермонтов", config="russian")

vector = (

SearchVector("title", weight="A", config="russian") +

SearchVector("body", weight="B", config="russian")

)

Post.objects.annotate(

rank=SearchRank(vector, query),

headline=SearchHeadline("body", query, config="russian")

).filter(

rank__gt=0.1

).order_by("-rank", "-publish").first()

Результат:

Вывод - запрос выполняется очень долго: Execution time: 1.433396s
Почему? Ведь мы с вами выполнили векторизацию всех потов, добавили сигнал, по которому автоматически выполняется векторизация для каждого нового поста по полю title и body?

Причина вот в чем:

rank=SearchRank(vector, query),

Это приводит к пересчёту SearchVector(...) для каждого поста при каждом запросе. В результате GIN-индекс не используется — Postgres вынужден пересчитывать tsvector по title и body вручную.

Как сделать правильно:

rank=SearchRank(F("search_vector"), query)

from django.db.models import F

from django.contrib.postgres.search import SearchQuery, SearchRank, SearchHeadline

query = SearchQuery("лермонтов", config="russian")

Post.objects.annotate(

rank=SearchRank(F("search_vector"), query),

headline=SearchHeadline("body", query, config="russian")

).filter(

search_vector=query,

rank__gt=0.1

).order_by("-rank", "-publish").first()

Результат - Execution time: 0.013309s:

Благодаря заранее выполненной векторизации и правильного запроса мы получили выигрыш в скорости запроса с Execution time: 1.433396s до Execution time: 0.013309s.

Скопируйте SQL запрос:

EXPLAIN ANALYZE

SELECT "blog_post"."id",

"blog_post"."title",

"blog_post"."slug",

"blog_post"."author_id",

"blog_post"."body",

"blog_post"."publish",

"blog_post"."created",

"blog_post"."updated",

"blog_post"."status",

"blog_post"."search_vector",

ts_rank("blog_post"."search_vector", plainto_tsquery('russian'::regconfig, 'лермонтов')) AS "rank",

ts_headline('russian'::regconfig, "blog_post"."body", plainto_tsquery('russian'::regconfig, 'лермонтов')) AS "headline"

FROM "blog_post"

WHERE (ts_rank("blog_post"."search_vector", plainto_tsquery('russian'::regconfig, 'лермонтов')) > 0.1 AND "blog_post"."search_vector" @@ (plainto_tsquery('russian'::regconfig, 'лермонтов')))

ORDER BY 11 DESC,

"blog_post"."publish" DESC

LIMIT 1;

Выполните этот запрос в psql.

Результат:

Разбор EXPLAIN ANALYZE

Limit (cost=2606.60..2606.86 rows=1 width=1089)

(actual time=12.510..12.512 rows=1 loops=1)

Limit: мы просили вернуть 1 строку (LIMIT 1), и Postgres остановился после первой найденной записи. Отлично: это снижает нагрузку после сортировки.

Result (cost=2606.60..2757.38 rows=569 width=1089)

(actual time=12.508..12.509 rows=1 loops=1)

Result — вспомогательный узел, просто указывает, что дальше идет вычисление полей rank и headline.

Sort (cost=2606.60..2608.02 rows=569 width=1057)

(actual time=11.828..11.829 rows=1 loops=1)

Sort Key: (ts_rank(search_vector, '''лермонт'''::tsquery)) DESC, publish DESC

Sort Method: top-N heapsort Memory: 27kB

Sort: Postgres отсортировал 569 записей по rank DESC, publish DESC, чтобы взять топ-1.

• top-N heapsort — алгоритм сортировки, оптимизированный под LIMIT N.
• 27kB памяти — это дёшево, значит сортировка была быстрая.

⚠️ Блокирующий шаг: сортировка происходит после фильтрации, то есть сначала Postgres должен найти все подходящие записи.

Bitmap Heap Scan on blog_post

(cost=21.48..2603.75 rows=569 width=1057)

(actual time=0.945..10.620 rows=1708 loops=1)

Bitmap Heap Scan — Postgres сначала ищет ссылки на строки в GIN-индексе, затем читает нужные строки из таблицы.

• rows=1708 — он нашёл 1708 записей, у которых search_vector @@ plainto_tsquery('лермонтов').
• Filter: далее отфильтровал по ts_rank(...) > 0.1 — оставил 569 (см. выше).

Это нормальный, быстрый способ доступа к текстовому индексу.

Bitmap Index Scan on blog_post_search__528e75_gin

(cost=0.00..21.34 rows=1708 width=0)

(actual time=0.711..0.711 rows=1708 loops=1)

Index Cond: (search_vector @@ '''лермонт'''::tsquery)

Bitmap Index Scan по GIN-индексу:
Вот он — наш герой: GIN-индекс на search_vector используется

• 1708 совпадений найдено
• за 0.7 мс — супер быстро

Planning Time: 5.760 ms

Execution Time: 13.337 ms

Общее время: 13.3 мс

• Planning Time — построение плана
• Execution Time — реальное выполнение, включая фильтрацию, сортировку и LIMIT 1

Вывод: первый запрос был выполнен за 37.600 ms, второй за 13.337 ms. Результат от использования полнотекстового поиска на лицо.

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

Вопросы которые задают на собеседовании по этой теме

🟢 Junior (начальный уровень)

Вопросы направлены на понимание базового ORM и SQL:

1. Что делает метод filter() в Django ORM?
2. Чем filter() отличается от get()?
3. Как выполнить поиск по подстроке в Django?
4. Что делает icontains и чем он отличается от contains?
5. Как записать фильтр: найти все посты, содержащие слово "пушкин" в заголовке?
6. Как посмотреть SQL-запрос, который выполняет Django ORM?
7. Что такое QuerySet?
8. Можно ли использовать LIKE и ILIKE в Django?
9. Как добавить индекс на поле модели Django?
10. Что делает EXPLAIN в PostgreSQL?

🟡 Middle (средний уровень)

Ожидается понимание производительности, индексов, расширений:

1. Почему LIKE '%пушкин%' плохо масштабируется?
2. Что такое pg_trgm? Для чего используется?
3. Как создать GIN-индекс с pg_trgm в Django?
4. Что делает TrigramSimilarity?
5. Чем SearchVector отличается от TrigramSimilarity?
6. Как работает EXPLAIN ANALYZE и что такое Seq Scan / Index Scan?
7. Почему сортировка по similarity замедляет запрос?
8. Как создать полнотекстовый поиск на русском языке в PostgreSQL?
9. Что такое tsvector и tsquery? Где они используются?
10. Какие типы индексов поддерживает PostgreSQL и для чего они? (BTREE, GIN, GIST, BRIN)

🔴 Senior (продвинутый уровень)

Ожидается умение читать, профилировать, оптимизировать и объяснять запросы:

1. Проанализируй вывод EXPLAIN ANALYZE и скажи, где бутылочное горлышко.
2. Почему TrigramSimilarity + order_by('-similarity') приводит к Seq Scan даже при наличии GIN индекса?
3. В каком случае PostgreSQL не использует индекс, даже если он существует?
4. Чем Bitmap Index Scan отличается от Index Scan? Когда это важно?
5. Когда GIN индекс не ускорит запрос? Приведи пример.
6. Как сделать fuzzy поиск, при этом использовать Index Scan, а не Seq Scan?
7. Как спроектировать индексную стратегию для поля title, если нужно искать:
   точные слова,
   части слова,
   похожие слова?
8. Чем отличается SearchVectorField от CharField? Когда его использовать?
9. Какую роль играют config='russian' в SearchVector?
10. Как собрать статистику по производительности запросов в продакшене (pg_stat_statements)?

Если вам понравилась статья — не забудьте:

👍 Поставить лайк;

📌 Сохранить статью в закладки;

🔁 Поделиться с коллегами;

✍️ Написать в комментарии, какие ещё темы вам интересны: автодополнение, поиск с Elastic, мультиязычный поиск или что-то другое?

Подпишись на канал, чтобы не пропустить следующие обучающие материалы уровня middle → senior, которые реально применимы в бою.