#6 – Индексы, оптимизация и анализ запросов

Когда база данных растет, запросы начинают выполняться медленнее. Чтобы ускорить их, разработчики используют индексы — специальные структуры данных, которые позволяют быстро находить нужные строки, не перебирая всю таблицу.

Что такое индекс?

Индекс в PostgreSQL — это вспомогательная таблица, которая хранит отсортированные значения определенного столбца и ссылки на реальные строки в основной таблице. Принцип работы похож на указатель в книге: вместо того чтобы читать все страницы, вы сразу переходите к нужной.

Создать индекс можно с помощью команды:

CREATE INDEX idx_orders_price ON orders(price);

Теперь PostgreSQL сможет использовать этот индекс, когда вы фильтруете данные по price:

SELECT * FROM orders WHERE price > 1000;

Важно помнить, что индекс не ускоряет все запросы. Он эффективен только при фильтрации, сортировке или поиске по колонке, на которую он создан.

Типы индексов

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

• BTREE — стандартный тип, используется по умолчанию (для <, >, =).
• HASH — быстрый поиск по равенству, но редко применяется.
• GIN / GiST — для полнотекстового поиска, JSON и сложных структур данных.

Когда индекс не помогает?

Если таблица маленькая, PostgreSQL может решить, что быстрее прочитать всю таблицу (Seq Scan), чем использовать индекс. Индексы также не используются, если в запросе участвуют функции над полем, например:

SELECT * FROM users WHERE LOWER(email) = '[email protected]';

В таких случаях можно создать функциональный индекс:

CREATE INDEX idx_users_lower_email ON users(LOWER(email));

Как узнать, используется ли индекс?

Для анализа запросов в PostgreSQL есть команды EXPLAIN и EXPLAIN ANALYZE.

EXPLAIN SELECT * FROM orders WHERE price > 1000;

Эта команда покажет план выполнения запроса. Чтобы увидеть реальные цифры (время и количество строк), используйте:

EXPLAIN ANALYZE SELECT * FROM orders WHERE price > 1000;

Если вы видите Index Scan — значит, индекс используется. Если Seq Scan — PostgreSQL выполняет последовательное чтение всей таблицы.

Оптимизация запросов

Чтобы запросы работали быстро, придерживайтесь нескольких правил:

• Создавайте индексы только на те колонки, которые часто участвуют в фильтрах и сортировках.
• Используйте EXPLAIN ANALYZE для анализа долгих запросов.
• Не злоупотребляйте индексами — каждый индекс замедляет операции INSERT и UPDATE.
• Периодически выполняйте VACUUM ANALYZE для обновления статистики оптимизатора.