Оптимизация медленного SQL‑запроса

44. Оптимизировать медленный SQL-запрос

Условие задачи:
Даны таблицы customers и orders.

Код:

CREATE TABLE customers (
    customer_id NUMERIC(15) PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    registration_date TIMESTAMP,
    premium_member BOOLEAN
);

CREATE TABLE orders (
    order_id NUMERIC(15) PRIMARY KEY,
    customer_id NUMERIC(15) REFERENCES customers(customer_id),
    order_date TIMESTAMP,
    total_amount DECIMAL(12, 2),
    status VARCHAR(20)
);

Медленно выполняется запрос:

SELECT
    c.name,
    c.email,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.status = 'Processing'
  AND c.premium_member = TRUE
ORDER BY o.order_date DESC;

Необходимо предложить способы его оптимизации.

Спойлеры к решению

Подсказки

💡 Сначала посмотри реальный план выполнения через EXPLAIN ANALYZE.
💡 Основная фильтрация выполняется по orders.status и customers.premium_member.
💡 Также используются customer_id для JOIN и order_date для сортировки.
💡 Подбирай индексы под фильтрацию, соединение и сортировку.
💡 Наличие Seq Scan само по себе не означает проблему — на больших выборках он может быть оптимальным.

Решение

Сам запрос переписывать необязательно — его логика уже достаточно простая. В первую очередь нужно проверить план выполнения:

EXPLAIN ANALYZE
SELECT
    c.name,
    c.email,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.status = 'Processing'
  AND c.premium_member = TRUE
ORDER BY o.order_date DESC;

Для таблицы orders полезен составной индекс:

CREATE INDEX idx_orders_status_date_customer
    ON orders (
        status,
        order_date DESC,
        customer_id
    );

Он помогает:

отфильтровать строки по status;
получить строки в порядке order_date DESC;
использовать customer_id для соединения с customers.

В PostgreSQL, если запрос почти всегда ищет именно статус Processing, можно создать более компактный частичный индекс:

CREATE INDEX idx_orders_processing_date_customer
    ON orders (
        order_date DESC,
        customer_id
    )
    WHERE status = 'Processing';

Для премиум-клиентов также можно использовать частичный индекс:

CREATE INDEX idx_customers_premium
    ON customers (customer_id)
    WHERE premium_member = TRUE;

При этом customer_id уже является PRIMARY KEY, поэтому отдельный индекс на него создавать не нужно.

После создания индексов необходимо повторно проверить план:

EXPLAIN ANALYZE
SELECT
    c.name,
    c.email,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.status = 'Processing'
  AND c.premium_member = TRUE
ORDER BY o.order_date DESC;

Важно смотреть не только на Seq Scan или Index Scan, а на фактическое время выполнения, количество обработанных строк, способ соединения и наличие отдельной сортировки.

Для PostgreSQL также полезно обновить статистику:

ANALYZE customers;
ANALYZE orders;

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