Читаем план запроса на практике

Давайте рассмотрим план запроса на примере двух таблиц  customers  и  orders .  Ниже представлена их структура. 

customers

customer_id	first_name	last_name	city
1	John	Doe	New York
2	Jane	Smith	Los Angeles

orders

order_id	customer_id	total_amount
1	1	100.00
2	1	50.00

Задача

Нам нужно найти топ-10 клиентов из Нью-Йорка по общей сумме их заказов.

EXPLAIN ANALYZE
SELECT c.customer_id, c.first_name, c.last_name, SUM(o.total_amount) as total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.city = 'New York'
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC
LIMIT 10;

Получаем план запроса...

                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1069.95..1069.97 rows=10 width=43) (actual time=12.743..12.745 rows=10 loops=1)
   ->  Sort  (cost=1069.95..1070.76 rows=324 width=43) (actual time=12.742..12.743 rows=10 loops=1)
         Sort Key: (sum(o.total_amount)) DESC
         Sort Method: top-N heapsort  Memory: 27kB
         ->  HashAggregate  (cost=1057.00..1060.24 rows=324 width=43) (actual time=12.624..12.697 rows=324 loops=1)
               Group Key: c.customer_id, c.first_name, c.last_name
               Batches: 1  Memory Usage: 145kB
               ->  Hash Right Join  (cost=574.80..1036.29 rows=4141 width=43) (actual time=2.495..10.989 rows=4141 loops=1)
                     Hash Cond: (o.customer_id = c.customer_id)
                     ->  Seq Scan on orders o  (cost=0.00..396.41 rows=20141 width=8) (actual time=0.007..2.583 rows=20141 loops=1)
                     ->  Hash  (cost=573.55..573.55 rows=100 width=39) (actual time=2.469..2.469 rows=100 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 14kB
                           ->  Seq Scan on customers c  (cost=0.00..573.55 rows=100 width=39) (actual time=0.012..2.434 rows=100 loops=1)
                                 Filter: ((city)::text = 'New York'::text)
                                 Rows Removed by Filter: 19900
 Planning Time: 0.467 ms
 Execution Time: 12.820 ms
(16 rows)
        

Как нужно читать план запроса?

1. Читайте план снизу вверх. Самая нижняя строка представляет первую операцию, выполняемую PostgreSQL.
2. Каждая строка представляет узел плана (операцию). Отступы показывают иерархию операций.
3. Обратите внимание на тип операции (например, Seq Scan, Hash Join, Sort и т.д.) и таблицу, к которой она применяется.
4. Значения cost и actual time дают представление о том, сколько времени, по оценке PostgreSQL, займет операция и сколько времени она фактически заняла.
5. Для операций соединения обратите внимание на условие соединения (например, Hash Cond: (o.customer_id = c.customer_id)).
6. Для агрегатных операций обратите внимание на Group Key, показывающий столбцы, по которым выполняется группировка.
7. Для операций фильтрации обратите внимание на условие фильтра и количество строк, удаленных фильтром.
8. Общее время планирования (Planning Time) и время выполнения (Execution Time) дают представление об общей производительности запроса. Но реальное время выполнения запроса может отличаться, так как план запроса - это просто предварительная оценка, а реальное выполнения запроса еще зависит от текущей нагрузки на сервер и очередей.