Индекс для Postgres, или Как автоматизировать то, что бесит

[seoDNK]

Хочу поговорить про рутину, которая бесит каждого бэкендера, сисадмина и вебмастера, оптимизацию производительности баз данных. Представьте картину: у вас есть legacy-проект, куча тяжелых SQL-запросов в логах, и база под нагрузкой начинает плавно уходить в красную зону по CPU. Что делает классический разработчик?
Морщится, открывает консоль и начинает медитировать над EXPLAIN ANALYZE, пытаясь угадать, какой индекс спасет прод.
Но зачем делать руками то, что можно спихнуть на простейшие скрипты автоматизации?

Недавно я провел эксперимент по мотивам одного западного технического кейса. Вместо того чтобы вручную перебирать структуры таблиц, был написан генератор гипотез, который по шаблонам выплюнул сразу 131 вариант потенциальных индексов для пачки медленных запросов (одиночные, составные, частичные, индексы по условиям и т.д.).
Раньше на этом этапе любая автоматизация ломалась. Потому что сидеть, смотреть на этот огромный список и думать, какой индекс накатить, а какой нет, занимало часы тяжелой ручной работы. Ведь если бездумно бахнуть все 131 индекс на боевой VPS, ваш диск скажет вам до свидания, а производительность на запись упадет ниже плинтуса.
Но ведь судить качество гипотез должен тот, кому с этими индексами потом жить. То есть сам планировщик PostgreSQL.


Логика скрипта-валидатора на самом деле элементарная. Мы берем наш лог, в цикле создаем предложенный индекс, спрашиваем у планировщика (EXPLAIN), станет ли база его использовать в реальном запросе, записываем результат и тут же удаляем индекс. На проверку одной безумной идеи уходит ровно секунда.
Вот пример того, как этот конвейер выглядит на Python:

Код Выделить Развернуть

import json
import psycopg2

# Медленный запрос, который мы мучаем
query = "SELECT * FROM orders WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY user_id;"

# Список сгенерированных гипотез (наш 131 индекс)
proposed_indexes = [
    "CREATE INDEX IF NOT EXISTS idx_test_1 ON orders (status);",
    "CREATE INDEX IF NOT EXISTS idx_test_2 ON orders (status, created_at);",
    "CREATE INDEX IF NOT EXISTS idx_test_3 ON orders (status, created_at, user_id);",
    # ... и так далее по списку
]

conn = psycopg2.connect("dbname=test user=postgres password=secret")
cur = conn.cursor()

for idx_sql in proposed_indexes:
    idx_name = idx_sql.split("ON")[0].split("INDEX")[1].strip()
    try:
        # 1. Быстро создаем индекс
        cur.execute(idx_sql)
       
        # 2. Проверяем, задействует ли его планировщик
        cur.execute(f"EXPLAIN (FORMAT JSON) {query}")
        explain_result = cur.fetchone()[0]
       
        # Ищем упоминание нашего индекса в плане выполнения
        explain_str = json.dumps(explain_result)
        if idx_name in explain_str:
            print(f"[ПРОФИТ] База одобрила индекс: {idx_name}")
        else:
            print(f"[МУСОР] Индекс проигнорирован планировщиком: {idx_name}")
           
    finally:
        # 3. Моментально дропаем, чтобы не засорять систему
        cur.execute(f"DROP INDEX IF EXISTS {idx_name};")
        conn.commit()

cur.close()
conn.close()


В итоге Postgres сама выступила в роли жесткого автоматического судьи. Она отсеяла 95% мусорных вариантов и оставила только те 2–3 индекса, от которых кост (стоимость выполнения) реально упал, а страницы сайта начали генерироваться за миллисекунды.
В двух самых тяжелых случаях база честно выдала инсайт, который никакой ручной перебор индексов бы не показал. Планировщик просто проигнорировал все тестовые структуры, наглядно продемонстрировав, что у запросов нет проблем с индексами, там был косяк в кривой логике JOINов и архитектуре самой таблицы. База как бы намекнула: Иди переписывай код приложения и не мучай дисковую подсистему.

Вывод : не пытайтесь делать ревью за скриптами руками. Заставляйте СУБД саму тестировать сгенерированный код.