Индексы и производительность

Индексы и производительность PostgreSQL¶

Индекс — это отдельная структура данных, которая ускоряет поиск строк в таблице. Без индекса PostgreSQL вынужден читать каждую строку таблицы (Seq Scan). С индексом — находит нужные строки за O(log n) или даже O(1).

Аналогия: представь книгу в 1000 страниц. Без оглавления ты листаешь каждую. Оглавление (индекс) сразу отправляет на нужную страницу.

Когда PostgreSQL использует индекс¶

PostgreSQL сам решает, использовать индекс или нет. Это делает планировщик (planner) на основе статистики:

Если нужно вернуть < ~10-15% строк таблицы — Index Scan выгоднее
Если нужно > 30-50% строк — Seq Scan быстрее (последовательное чтение диска дешевле случайного)
Между 10% и 30% — зависит от размера таблицы, корреляции данных, настроек random_page_cost

Индекс не гарантирует ускорение

Если запрос возвращает большую часть таблицы, индекс замедлит выполнение: случайные чтения из индекса дороже последовательного Seq Scan.

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

B-tree — основной и по умолчанию¶

SQL

CREATE INDEX idx_customers_city ON customers (city);
-- Эквивалентно:
CREATE INDEX idx_customers_city ON customers USING btree (city);

Поддерживает: =, <, >, <=, >=, BETWEEN, IN, IS NULL, LIKE 'prefix%'

Не поддерживает: LIKE '%middle%', функции от столбца (WHERE LOWER(city) = 'москва')

Как работает: сбалансированное дерево с глубиной обычно 3-4 уровня. Каждый узел — страница 8 KB. Для таблицы в 10 млн строк нужно ~3-4 чтения страниц вместо полного сканирования.

Text Only

              [Казань | Москва | СПб]           ← корень
             /         |          \
    [Екб | Казань]  [Москва]  [Самара | СПб]    ← промежуточные
       /     |        |          |       \
     [...]  [...]   [строки]   [...]   [...]    ← листья → указатели на heap

Hash¶

SQL

CREATE INDEX idx_customers_email_hash ON customers USING hash (email);

Поддерживает: только =

Преимущество: чуть компактнее B-tree для equality-запросов.

Ограничение: не поддерживает диапазоны, сортировку, IS NULL. До PostgreSQL 10 hash-индексы не были WAL-safe (не реплицировались).

Когда использовать Hash

Почти никогда. B-tree покрывает те же случаи и поддерживает больше операций. Hash может быть на 10-20% быстрее для pure equality на очень больших таблицах.

GIN — Generalized Inverted Index¶

SQL

-- Полнотекстовый поиск
CREATE INDEX idx_reviews_comment_gin ON reviews USING gin (to_tsvector('russian', comment));

-- JSONB
CREATE INDEX idx_products_attrs_gin ON products USING gin (attributes);

-- Массивы
CREATE INDEX idx_tags_gin ON articles USING gin (tags);

Поддерживает: @>, ?, ?|, ?& (JSONB), @@ (полнотекст), &&, @> (массивы)

Как работает: для каждого значения (ключ JSON, слово в тексте, элемент массива) хранит список строк, где оно встречается. «Инвертированный индекс» — как Google индексирует слова → документы.

Text Only

"Apple"   → [product_id: 1, 4, 7]
"Samsung" → [product_id: 2]
"Xiaomi"  → [product_id: 3, 25]

Особенности:

Быстрый поиск, медленная вставка (нужно обновить множество записей)
jsonb_path_ops — компактнее на ~30%, но поддерживает только @>

SQL

-- Компактный вариант для @> запросов
CREATE INDEX idx_products_attrs_pathops
ON products USING gin (attributes jsonb_path_ops);

-- Пример: товары бренда Apple
SELECT name, price FROM products
WHERE attributes @> '{"brand": "Apple"}';

GiST — Generalized Search Tree¶

SQL

-- Геоданные (PostGIS)
CREATE INDEX idx_locations_gist ON locations USING gist (geom);

-- Диапазоны
CREATE INDEX idx_bookings_period ON bookings USING gist (period);

-- Полнотекст (альтернатива GIN, меньше по размеру)
CREATE INDEX idx_reviews_gist ON reviews USING gist (to_tsvector('russian', comment));

Поддерживает: <<, >>, &&, <@, @>, ~= (геометрия, диапазоны)

GIN vs GiST для полнотекста:

Критерий	GIN	GiST
Скорость поиска	Быстрее в ~3 раза	Медленнее
Скорость вставки	Медленнее	Быстрее
Размер индекса	Больше	Меньше
Лучше для	Read-heavy нагрузки	Write-heavy нагрузки

BRIN — Block Range Index¶

SQL

CREATE INDEX idx_events_date_brin ON events USING brin (event_date);

Поддерживает: =, <, >, <=, >=

Как работает: хранит MIN/MAX значения для каждого блока страниц (по умолчанию 128 страниц). Если искомое значение не попадает в диапазон блока — весь блок пропускается.

Преимущества: индекс крошечный (в 100-1000 раз меньше B-tree).

Ограничение: эффективен только если данные физически упорядочены на диске (корреляция близка к 1.0). Для таблицы событий с автоинкрементом event_id и event_date по возрастанию — идеально.

SQL

-- Проверить корреляцию
SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename = 'events' AND attname = 'event_date';

-- correlation = 0.98 → BRIN будет эффективен
-- correlation = 0.15 → BRIN бесполезен, используй B-tree

Продвинутые возможности¶

Partial Index — индекс с условием¶

Индексирует только строки, удовлетворяющие WHERE. Меньше размер → быстрее обновление → быстрее поиск.

SQL

-- Индексируем только активных клиентов (90% запросов к ним)
CREATE INDEX idx_customers_active
ON customers (city)
WHERE is_active = true;

-- Только незавершённые заказы
CREATE INDEX idx_orders_pending
ON orders (customer_id, order_date)
WHERE status IN ('new', 'processing');

WHERE в запросе должен совпадать

PostgreSQL использует partial index только если WHERE запроса подразумевает условие индекса. Запрос WHERE city = 'Москва' AND is_active = true → использует idx_customers_active. Запрос WHERE city = 'Москва' без is_active → не использует.

Expression Index — индекс на выражении¶

SQL

-- Поиск без учёта регистра
CREATE INDEX idx_customers_lower_email
ON customers (LOWER(email));

-- Теперь работает:
SELECT * FROM customers WHERE LOWER(email) = 'ivanov@mail.ru';

-- Индекс на год из даты
CREATE INDEX idx_orders_year
ON orders (EXTRACT(YEAR FROM order_date));

Covering Index (INCLUDE)¶

Добавляет столбцы в индекс без индексации, чтобы обеспечить Index-Only Scan.

SQL

-- Часто запрашиваем имя и email по городу
CREATE INDEX idx_customers_city_incl
ON customers (city)
INCLUDE (name, email);

-- Теперь этот запрос не обращается к таблице вообще:
SELECT name, email FROM customers WHERE city = 'Москва';
-- Все данные берутся из индекса → Index-Only Scan

Отличие от составного индекса: INCLUDE-столбцы не участвуют в сортировке и поиске, но доступны для чтения без обращения к heap.

Составной (multicolumn) индекс¶

SQL

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date DESC);

Правило «левого префикса»: составной индекс (A, B, C) работает для:

WHERE A = ? ✅
WHERE A = ? AND B = ? ✅
WHERE A = ? AND B = ? AND C = ? ✅
WHERE B = ? ❌ (A не указан)
WHERE A = ? AND C = ? ⚠️ (работает для A, но C не ускоряется)

Index-Only Scan vs Index Scan vs Bitmap Scan¶

Index Scan¶

Идёт по индексу → для каждой найденной записи обращается к таблице (heap) за остальными столбцами.

Index-Only Scan¶

Все нужные столбцы есть в индексе → таблица не читается вообще. Самый быстрый вариант.

Условие: VACUUM должен был пройти, чтобы Visibility Map была актуальной. Иначе PostgreSQL всё равно обращается к heap для проверки видимости строк.

Bitmap Index Scan¶

Используется когда индекс возвращает много строк или когда нужно комбинировать несколько индексов:

Text Only

Bitmap Index Scan → строит битовую карту страниц
    ↓
Bitmap Heap Scan → читает страницы в последовательном порядке

SQL

-- Два отдельных индекса на city и is_active
-- PostgreSQL комбинирует их через BitmapAnd:
EXPLAIN SELECT * FROM customers
WHERE city = 'Москва' AND is_active = true;

-- → BitmapAnd
--     → Bitmap Index Scan on idx_city
--     → Bitmap Index Scan on idx_active
--   → Bitmap Heap Scan

Обслуживание индексов¶

Размер индексов¶

SQL

-- Размер всех индексов таблицы
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = 'orders'
ORDER BY pg_relation_size(indexname::regclass) DESC;

Неиспользуемые индексы¶

SQL

-- Индексы, которые ни разу не использовались с последнего pg_stat_reset()
SELECT schemaname, relname, indexrelname,
       idx_scan,           -- сколько раз использовался
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Не удаляй вслепую

idx_scan = 0 не всегда значит «бесполезен». Индекс может использоваться для UNIQUE constraint или для редких, но критичных запросов (например, ежемесячный отчёт). Проверь: SELECT conindid FROM pg_constraint WHERE conindid = 'idx_name'::regclass;

REINDEX и bloat¶

При интенсивных UPDATE/DELETE индексы «раздуваются» — мёртвые записи занимают место. VACUUM помечает их, но не уменьшает физический размер.

SQL

-- Перестроить индекс (блокирует таблицу!)
REINDEX INDEX idx_orders_customer_date;

-- Перестроить без блокировки (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_orders_customer_date;

Стратегия индексирования¶

Пошаговый алгоритм¶

Не создавай индекс заранее. Начни без индексов (кроме PK и UNIQUE).
Найди медленные запросы: pg_stat_statements, auto_explain, log_min_duration_statement.
Посмотри EXPLAIN ANALYZE — если видишь Seq Scan с rows=1000000 и actual rows=5 → нужен индекс.
Создай индекс и проверь, что планировщик его использует.
Мониторь размер и использование индексов регулярно.

Антипаттерны¶

Антипаттерн	Почему плохо
Индекс на каждый столбец	Замедляет INSERT/UPDATE, занимает место
Индекс на Boolean	Селективность 50/50 — PostgreSQL не использует
Составной индекс `(A, B)` при запросах только по `B`	Бесполезен (правило левого префикса)
Индекс на маленькую таблицу (< 1000 строк)	Seq Scan быстрее
Дублирующие индексы: `(A)` + `(A, B)`	Индекс `(A, B)` покрывает запросы по `A`

Попробуй сам¶

Исследуй индексы на живой базе. Создай индекс, посмотри как меняется план запроса.

Проверь себя¶

Источники¶

PostgreSQL: Indexes — официальная документация по всем типам индексов
PostgreSQL: Index Types — B-tree, Hash, GiST, GIN, BRIN
PostgreSQL: Partial Indexes — индексы с условием WHERE
PostgreSQL: Expression Indexes — индексы на выражениях
PostgreSQL: Index-Only Scans — covering indexes и INCLUDE