PostgreSQL Explain | PostgreSQL для разработчика 🐘

Mark

Привет, я решил сам основательно разобраться в том, как работают разные штуки в PostgreSQL на практике, и самый лучший способ что-то понять – это попытаться научить других! Поэтому я решил оформить каждую тему в отдельную статью.

Важно❗️

Я не эксперт в PostgreSQL, уверен, что есть огромное количество людей которые знают про эту технологию сильно больше меня. Я лишь хочу поделиться теми знаниями, которые точно пригодятся вам в работе и на собеседовании.

Важно (2)❗️

Большинство материалов по PostgreSQL не имеют никакой практической части, я же хочу сделать упор именно на практику и очень прошу вас повторять все за мной!

Для этого нужно взять учебную базу данных по ссылке и создать ее локально:

createdb users_postgres_tips
psql -d users_postgres_tips -f <путь до users_postgres_tips.sql>

PostgreSQL Explain

В вакансиях часто (99% вакансий) есть такое требование: "Оптимизация SQL запросов", а на собеседованиях не менее часто спрашивают: "Как понять почему SQL запрос работает медленно?". Как раз для этого и нужен EXPLAIN это функция, которая показывает как планировщик будет выполнять запрос.

Планировщик – специальная программа, которая отвечает за то, чтобы наш запрос выполнялся наиболее эффективным способом

Логичным вопросом будет: "зачем тогда мне что-то делать с запросом, если планировщик и так будет выбирать самый лучший вариант выполнения?" Очевидно, что планировщик не всесилен и "плохой" запрос он будет выполнять плохо с максимальной эффективностью 🤓

В данном контексте плохой запрос – это медленный запрос. Медленный запрос – запрос, который выполняется > 100мс

Как читать вывод EXPLAIN? | Seq Scan

Давайте уже начнем: в нашей базе есть таблица users попробуем ее заселектить полностью!

EXPLAIN
SELECT * from users;

                           QUERY PLAN
-----------------------------------------------------------
Seq Scan on users (cost=0.00..344446.32 rows=9999432 width=163)

Что заесть вообще написано?

Seq Scan on users: (sequence scan) это значит то, что планировщик читает всю таблицу users и после этого отдает результат
cost=0.00: Какая примерная стоимость старта сканирования таблицы (Seq Scan on users).
cost=344446.32: Какая примерная стоимость выполнения сканирования таблицы (Seq Scan on users).
rows=9999432: Приблизительное количество строк, которое вернет запрос
width=163: Приблизительный средний размер одной строки в байтах

EXPLAIN ANALYSE

Зачем мне все приблизительное 😡! Согласен 😅! EXPLAIN показывает как планировщик мог бы выполнять запрос, но не выполняет его на самом деле! Чтобы получить реальные данные, нужно передать в функцию EXPLAINаргумент ANALYSE, а все примерные значения я предпочитаю скрывать параметром COSTS OFF

EXPLAIN (ANALYSE) - Реально выполняет запрос! Поэтому не спешите проверять время выполнения своих INSERT или UPDATE запросов, дочитайте этот гайд до конца!

Обычный EXPLAIN без аргументов тоже может быть полезен! Например, когда запросы ооочень жирные, нет возможности их выполнить и нужно хотя бы примерно понимать почему так происходит.

EXPLAIN (ANALYSE, COSTS OFF)
SELECT * from users;

                           QUERY PLAN
-----------------------------------------------------------
Seq Scan on users (actual time=0.051..1290.516 rows=10000000 loops=1)
Planning Time: 0.105 ms
Execution Time: 1501.098 ms

actual time=0.051: Реальное время в миллисекундах, которое потребовалось на то, чтобы начать сканировать таблицу (Seq Scan on users).
actual time=1290.516: Реальное время в миллисекундах, которое потребовалось на выполнение сканирования таблицы (Seq Scan on users).
Planning Time: 0.105 ms: Сколько планировалось потратить времени на запрос
Execution Time: 1501.098 ms: Сколько времени запрос действительно выполнялся
rows=10000000: Количество строк, которое вернет запрос. У нас 10М записей в базе, это правда.
loops=1: Количество раз, выполнения конкретно этого узла запроса. В этом контексте – узел это одна строчка нашего плана.

Кстати, видно, что наш запрос выполнялся 1500 миллисекунд! По нашей договоренности это “плохой” запрос! Давайте что-то с этим сделаем! ☝️

Limit

Предыдущий запрос возвращает ВСЕ строки нашей таблицы, а это вообще-то 10 миллионов! Возвращать сразу много записей мы быстро не сможем, никогда! Чтобы “улучшить” этот запрос, надо просто забирать не все записи сразу, а по чуть-чуть, например, по 10 тысяч

EXPLAIN (ANALYSE, COSTS OFF)
SELECT * from users
LIMIT 10000;

                           QUERY PLAN
-----------------------------------------------------------
Limit (actual time=0.053..5.424 rows=10000 loops=1)
  ->  Seq Scan on users (actual time=0.051..4.587 rows=10000 loops=1)
Planning Time: 0.121 ms
Execution Time: 5.915 ms

rows=10000: Мы сканируем только 10000 строк
Execution Time: 5.915 ms: Другое дело 5мс это крайне быстро!

Index Scan

Колонка id – PRIMARY KEY таблицы users ⇒ у нас есть индекс по этой колонке. Попробуем получить строку в таблице по id, надеюсь не для кого не будет сюрпризом, что это будет супер быстро 🚀

EXPLAIN (ANALYSE, COSTS OFF)
SELECT * from users
WHERE id = 1234567;

                           QUERY PLAN
-----------------------------------------------------------
Index Scan using users_pkey on users (actual time=0.020..0.021 rows=1 loops=1)
  Index Cond: (id = 1234567)
Planning Time: 0.105 ms
Execution Time: 0.037 ms

Index Scan: Когда вы видите такое в выводе EXPLAIN — значит все хорошо. Мы использовали индекс, получили ссылку на строку в таблице, и вернули ее пользователю. Обычно операции с индексами работают очень быстро и сильно помогают ускорить наши запросы

Еще новая штука Index Cond: это условие индексного сканирования.

Index Only Scan

А теперь представим, что нам нужно забрать только индексируемое значение: id

EXPLAIN (ANALYSE, COSTS OFF)
SELECT id from users
WHERE id = 1234567;

                           QUERY PLAN
-----------------------------------------------------------
Index Only Scan using users_pkey on users (actual time=0.041..0.042 rows=1 loops=1)
  Index Cond: (id = 1234567)
  Heap Fetches: 0
Planning Time: 0.087 ms
Execution Time: 0.058 ms

Index Only Scan: Это то же самое что и Index Scan, только нам даже не нужно идти за данными в таблицу. Мы сразу возвращаем id прямиком из индекса (в индексе лежат значения) и нам не нужно читать с диска!

Parallel Seq Scan

Теперь перейдем к более интересным вариантам

Получаем всех пользователей с именем ‘Mark’ (вот так вот я люблю себя, да 🌚)

EXPLAIN (ANALYSE, COSTS OFF)
SELECT * from users
WHERE name = 'Mark'

                           QUERY PLAN
-----------------------------------------------------------
Gather (actual time=4.837..2790.685 rows=72661 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on users (actual time=0.285..2766.218 rows=24220 loops=3)
        Filter: (name = 'Mark'::text)
        Rows Removed by Filter: 3309113
Planning Time: 0.106 ms
Execution Time: 2792.305 ms

Здесь много чего интересного:

Появились какие-то Workers – это значит, что запрос выполнялся параллельно несколькими процессами. А Gather просто означает, что мы потом собрали все результаты вместе
Parallel Seq Scan: То же самое что и Seq Scan просто планировщик разбил таблицу на несколько частей и каждый процесс скандирует своя часть таблицы
loops=3: Получается что мы разбили таблицы на 3 части и каждый процесс взял свою часть. Тут интересно! Хоть и написано что: Workers Launched: 2 На самом деле процесса три! 1 главный + 2 запущенных дополнительно
Filter: это условие по которому фильтруется выборка, применяется уже после того как мы выбрали данные и нам пришлось пройтись по ним всем и применить какой-то фильтр

За распараллеливание запросов отвечает планировщик и происходит это с большими таблицами.

Bitmap Heap Scan

А что если мы попробуем создать индекс по колонке name? Запрос же должен ускориться?

CREATE INDEX users_name_idx on users(name);

И выполним запрос:

EXPLAIN (ANALYSE, COSTS OFF)
SELECT * from users
WHERE name = 'Mark'

                           QUERY PLAN
-----------------------------------------------------------
Bitmap Heap Scan on users (actual time=6.331..1156.083 rows=72661 loops=1)
  Recheck Cond: (name = 'Mark'::text)
  Heap Blocks: exact=21400
  ->  Bitmap Index Scan on users_name_idx (actual time=2.643..2.644 rows=72661 loops=1)
        Index Cond: (name = 'Mark'::text)
Planning Time: 0.169 ms
Execution Time: 1756.708 ms

Bitmap Index Scan: это значит, что при сканировании индексов нужно выбрать много записей. Из вывода видно что “Марков” целых 72661 штук! Планировщик использует индекс, для того чтобы найти все записи, а потом с помощью специального алгоритма читает сразу много записей из базы. Хоть и оптимизация очень крутая, но запрос все равно медленный 😭

Вообще Bitmap Scan это крутая оптимизация, когда нам нужно прочитать сразу много строк из базы, но, как мы видим, запрос достаточно медленный и нам, как разработчикам можно сделать вывод: “Мы построили индекс по не самой вариативной колонке, возможно, стоило построить какой-то другой индекс”

Возможно, нам достаточно первых 100 записей и тогда нам не придется 72661 раз проходиться по индексу:

EXPLAIN (ANALYSE, COSTS OFF)
SELECT * from users
WHERE name = 'Mark'
LIMIT 100;

                           QUERY PLAN
-----------------------------------------------------------
Limit (actual time=0.554..6.926 rows=100 loops=1)
  ->  Index Scan using users_name_idx on users (actual time=0.553..6.910 rows=100 loops=1)
        Index Cond: (name = 'Mark'::text)
Planning Time: 0.177 ms
Execution Time: 6.959 ms

Что дальше

А мы еще не закончили! Во второй части посмотрим на более сложные случаи: какие стратегии join, как выполняются агрегирующие функции и как быть с операциями вставки! 😎