SQL план запроса: углубленный анализ
sync.study

Еще немного попрактикуемся с чтением плана запроса

Найдем средний размер заказа для каждого города, где средний размер заказа превышает 100, и сортирует результаты по среднему размеру заказа в порядке убывания.

EXPLAIN ANALYZE
SELECT c.city, AVG(o.total_amount) as avg_order_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.city
HAVING AVG(o.total_amount) > 100
ORDER BY avg_order_amount DESC;

План запроса

Sort (cost=137.38..137.39 rows=1 width=40) (actual time=1.112..1.113 rows=2 loops=1)
  Sort Key: (avg(o.total_amount)) DESC
  Sort Method: quicksort Memory: 25kB
  -> HashAggregate (cost=137.34..137.36 rows=1 width=40) (actual time=1.098..1.100 rows=2 loops=1)
        Group Key: c.city
        Filter: (avg(o.total_amount) > 100::numeric)
        Batches: 1 Memory Usage: 24kB
        -> Hash Join (cost=29.80..137.24 rows=20 width=12) (actual time=0.032..0.980 rows=600 loops=1)
              Hash Cond: (o.customer_id = c.customer_id)
              -> Seq Scan on orders o (cost=0.00..103.00 rows=6000 width=8) (actual time=0.005..0.505 rows=6000 loops=1)
              -> Hash (cost=18.00..18.00 rows=1000 width=12) (actual time=0.021..0.021 rows=100 loops=1)
                    Buckets: 1024 Batches: 1 Memory Usage: 12kB
                    -> Seq Scan on customers c (cost=0.00..18.00 rows=1000 width=12) (actual time=0.003..0.011 rows=100 loops=1)
Planning Time: 0.158 ms
Execution Time: 1.158 ms
(15 rows)

План выполнения запроса с комментариями

План выполнения запроса Комментарии
Sort (cost=137.38..137.39 rows=1 width=40) (actual time=1.112..1.113 rows=2 loops=1) Финальная сортировка результатов по среднему размеру заказа в порядке убывания. Это последний шаг перед возвратом результатов.
Sort Key: (avg(o.total_amount)) DESC Указывает, что сортировка выполняется по среднему значению total_amount в порядке убывания (DESC).
Sort Method: quicksort Memory: 25kB PostgreSQL использует алгоритм быстрой сортировки (quicksort) и затрачивает 25kB памяти для этой операции.
-> HashAggregate (cost=137.34..137.36 rows=1 width=40) (actual time=1.098..1.100 rows=2 loops=1) Агрегация данных с использованием хеш-таблицы для группировки по городу и вычисления среднего значения.
Group Key: c.city Указывает, что группировка выполняется по полю city из таблицы customers.
Filter: (avg(o.total_amount) > 100::numeric) Применение условия HAVING для фильтрации групп, где средний размер заказа превышает 100.
Batches: 1 Memory Usage: 24kB Для агрегации используется 1 пакет данных и затрачивается 24kB памяти.
-> Hash Join (cost=29.80..137.24 rows=20 width=12) (actual time=0.032..0.980 rows=600 loops=1) Соединение таблиц с использованием хеш-таблицы. Это эффективный способ соединения для больших наборов данных.
Hash Cond: (o.customer_id = c.customer_id) Условие соединения: customer_id в таблице orders должен соответствовать customer_id в таблице customers.
-> Seq Scan on orders o (cost=0.00..103.00 rows=6000 width=8) (actual time=0.005..0.505 rows=6000 loops=1) Последовательное сканирование таблицы orders. Это означает, что PostgreSQL просматривает все строки в таблице.
-> Hash (cost=18.00..18.00 rows=1000 width=12) (actual time=0.021..0.021 rows=100 loops=1) Создание хеш-таблицы из результатов сканирования таблицы customers для использования в Hash Join.
Buckets: 1024 Batches: 1 Memory Usage: 12kB Хеш-таблица использует 1024 корзины, 1 пакет данных и затрачивает 12kB памяти.
-> Seq Scan on customers c (cost=0.00..18.00 rows=1000 width=12) (actual time=0.003..0.011 rows=100 loops=1) Последовательное сканирование таблицы customers. Это первая операция, выполняемая в плане запроса.
Planning Time: 0.158 ms Время, затраченное PostgreSQL на планирование запроса (анализ и выбор оптимального плана).
Execution Time: 1.158 ms Общее время выполнения запроса после планирования.

Анализ плана выполнения

В этом плане выполнения запроса PostgreSQL:

  1. Начинает с последовательного сканирования (Seq Scan) таблицы customers.
  2. Создает хеш-таблицу (Hash) из результатов сканирования customers.
  3. Выполняет последовательное сканирование (Seq Scan) таблицы orders.
  4. Использует соединение хешей (Hash Join) для объединения таблиц по условию o.customer_id = c.customer_id.
  5. Группирует результаты (HashAggregate) по полю c.city и вычисляет среднее значение total_amount.
  6. Применяет фильтр HAVING (avg(o.total_amount) > 100) к сгруппированным результатам.
  7. Сортирует отфильтрованные результаты (Sort) по среднему размеру заказа в порядке убывания.

Общее время выполнения запроса составляет всего 1.158 мс, что указывает на высокую эффективность. Однако, если бы таблицы были больше,  то операция Seq Scan  была бы уже неэффективна и можно было бы рассмотреть создание индексов на столбцах соединения (customer_id) для улучшения производительности.