Skip to content

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

Лабораторная работа №1: Оптимизация запросов с использованием EXPLAIN и ANALYZЕ

Теоретические сведения

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

Оптимизация запросов направлена на уменьшение времени выполнения и ресурсозатрат. PostgreSQL использует планировщик запросов, который генерирует оптимальный план выполнения на основе статистики таблиц и индексов. Ключевые инструменты для анализа: - EXPLAIN — показывает предполагаемый план выполнения без запуска запроса. - EXPLAIN ANALYZE — выполняет запрос и выводит реальный план с метриками (время, строки).

2. Основные метрики в выводе EXPLAIN
  • Cost (стоимость):
  • startup cost — стоимость начала выполнения узла плана.
  • total cost — общая стоимость выполнения узла.
  • Rows (rows) — ожидаемое количество строк.
  • Width (width) — средний размер строки в байтах.
  • Actual Time — реальное время выполнения (в миллисекундах).
3. Индексы и их влияние
  • Индексы ускоряют поиск данных, но замедляют вставку/обновление. Основные типы:
  • B-tree — для диапазонных запросов и сортировки.
  • Hash — для точного поиска по равенству.
  • GIN/GiST — для сложных типов данных (JSONB, массивы, геоданные).

Примеры выполнения

Структура базы данных

Создайте базу данных для интернет-магазина:

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2),
    category_id INT REFERENCES categories(id)
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    registration_date DATE
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    quantity INT
);

Заполнение тестовыми данными (можно использовать generate_series):

-- Добавление категорий
INSERT INTO categories (name) VALUES ('Электроника'), ('Книги'), ('Одежда');

-- Добавление 1000 товаров
INSERT INTO products (name, price, category_id)
SELECT 
    'Товар ' || i,
    (RANDOM() * 1000)::DECIMAL(10, 2),
    (RANDOM() * 2 + 1)::INT
FROM generate_series(1, 1000) AS i;

-- Добавление 500 пользователей
INSERT INTO users (name, email, registration_date)
SELECT 
    'Пользователь ' || i,
    'user' || i || '@example.com',
    CURRENT_DATE - (RANDOM() * 365)::INT
FROM generate_series(1, 500) AS i;

-- Добавление 1000 заказов
INSERT INTO orders (user_id, order_date, total_amount)
SELECT 
    (RANDOM() * 499 + 1)::INT,
    CURRENT_DATE - (RANDOM() * 365)::INT,
    (RANDOM() * 1000)::DECIMAL(10, 2)
FROM generate_series(1, 1000) AS i;

-- Добавление 5000 позиций в заказах
INSERT INTO order_items (order_id, product_id, quantity)
SELECT 
    (RANDOM() * 999 + 1)::INT,
    (RANDOM() * 999 + 1)::INT,
    (RANDOM() * 10 + 1)::INT
FROM generate_series(1, 5000) AS i;

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

  1. Запрос с JOIN и подзапросом:
SELECT 
    u.name, 
    SUM(o.total_amount) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.registration_date > '2023-01-01'
GROUP BY u.name
HAVING SUM(o.total_amount) > 500;
  1. Анализ плана выполнения:
EXPLAIN ANALYZE
SELECT ...; -- Вставить запрос выше

Вывод до оптимизации:

HashAggregate  (cost=... rows=... width=...)
  Group Key: u.name
  Filter: (sum(o.total_amount) > 500.00)
  ->  Hash Join  (cost=... rows=... width=...)
        Hash Cond: (o.user_id = u.id)
        ->  Seq Scan on orders o  (cost=... rows=... width=...)
        ->  Hash  (cost=... rows=... width=...)
              ->  Seq Scan on users u  (cost=... rows=... width=...)
                    Filter: (registration_date > '2023-01-01')
  1. Оптимизация через индексы:
CREATE INDEX idx_users_registration_date ON users(registration_date);
CREATE INDEX idx_orders_user_id ON orders(user_id);
  1. Повторный анализ:
HashAggregate  (cost=... rows=... width=...) (actual time=...)
  ->  Nested Loop  (cost=... rows=...) (actual time=...)
        ->  Index Scan using idx_users_registration_date on users u ...
        ->  Index Scan using idx_orders_user_id on orders o ...

Практические задания

Задание 1: Анализ запроса с JOIN
  1. Напишите запрос, который выводит:
  2. Название категории.
  3. Среднюю цену товаров в категории.
  4. Общее количество заказанных товаров из этой категории.
  5. Используйте EXPLAIN ANALYZE для анализа плана выполнения.
  6. Определите «узкие места» (например, Seq Scan, высокий cost).
Задание 2: Создание индексов
  1. Создайте индексы для полей, участвующих в JOIN и WHERE.
  2. Например: category_id в products, product_id в order_items.
  3. Повторно выполните EXPLAIN ANALYZE и сравните метрики.
Задание 3: Оптимизация подзапроса
  1. Напишите запрос, который находит пользователей, сделавших заказы на сумму больше 1000 руб. за последний месяц.
  2. Замените подзапрос на JOIN или CTE (Common Table Expression).
  3. Сравните производительность до и после оптимизации.

Контрольные вопросы

  1. Что показывает параметр cost в выводе EXPLAIN?
  2. Почему Seq Scan может быть медленнее Index Scan?
  3. Когда использование индекса неэффективно?
  4. Как влияет количество строк (rows) на выбор плана выполнения?

Работа позволяет освоить методы анализа и оптимизации запросов на реальных данных. Для углубленного изучения можно добавить работу с оконными функциями или более сложными индексами (частичными, составными).