Приведи пример индекса, с которым ты работал

Пример работы с составным индексов в PostgreSQL

В одном из проектов, связанных с платформой электронной коммерции, я работал с составным индексом (composite index) для оптимизации запросов к таблице заказов. Таблица orders имела следующую структуру:

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status VARCHAR(50) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    total_amount DECIMAL(10, 2) NOT NULL,
    -- другие поля...
);

Проблема и анализ запросов

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

-- Запрос 1: Получение активных заказов пользователя
SELECT * FROM orders 
WHERE user_id = 123 AND status = 'processing' 
ORDER BY created_at DESC;

-- Запрос 2: Получение всех заказов пользователя с пагинацией
SELECT * FROM orders 
WHERE user_id = 456 
ORDER BY created_at DESC 
LIMIT 10 OFFSET 0;

Без индексов эти запросы выполняли полное сканирование таблицы (sequential scan), что становилось проблемой при росте данных (более 5 млн записей).

Создание и применение составного индекса

После анализа селективности столбцов и порядка условий в WHERE, был создан следующий составной индекс:

CREATE INDEX idx_orders_user_status_created 
ON orders(user_id, status, created_at DESC);

Обоснование структуры индекса:

• user_id на первом месте: Имеет высокую селективность (много уникальных значений), что эффективно сужает поиск.
• status на втором месте: Часто используется в паре с user_id в фильтрах. Его положение после user_id позволяет эффективно выполнять запросы с условием по обоим полям.
• created_at DESC на третьем месте: Позволяет отказаться от сортировки (avoid sorting) для запросов с ORDER BY created_at DESC. Индекс уже хранит записи в нужном порядке для конкретного user_id (и status, если он указан).

Результаты и наблюдения

После внедрения индекса:

1. Скорость выполнения запросов увеличилась в 50-100 раз для целевых запросов. Вместо полного сканирования использовалось сканирование по индексу (index scan) с быстрым поиском по B-дереву.
2. Исключение операций сортировки: Для запроса SELECT ... WHERE user_id = ? ORDER BY created_at DESC оптимизатор PostgreSQL использовал Index Scan Backward, так как данные в индексе уже были упорядочены по created_at DESC. В плане запроса (EXPLAIN) исчезла операция Sort.
3. Покрывающий индекс (covering index): Для некоторых запросов, запрашивающих только id, user_id, status, created_at, индекс стал покрывающим (index-only scan). Данные читались непосредственно из индекса, без обращения к таблице (heap), что значительно ускорило выполнение.

Важные нюансы и ограничения

• Порядок столбцов критичен: Индекс (user_id, status, created_at) не будет эффективно использоваться для запроса с условием только по status или WHERE status = ? AND created_at > ?. B-дерево индекса строится от первого столбца к последующим.
• Не все запросы ускорились: Запросы с поиском только по created_at или с другими комбинациями полей по-прежнему требовали отдельных индексов или использовали другие методы доступа.
• Компромисс с производительностью записи: Каждый новый индекс замедляет операции INSERT, UPDATE, DELETE, так как необходимо обновлять дополнительные структуры данных. В нашем случае выигрыш в чтении значительно перевешивал потери на запись.
• Использование с IN: Индекс хорошо работал и для запросов с IN по user_id, например, WHERE user_id IN (?, ?) AND status = ?.

Этот пример наглядно показывает, как глубокое понимание структуры индексов, анализа реальных запросов и планов выполнения позволяет создавать высокоэффективные индексы, решающие конкретные проблемы производительности.