Оптимизация запросов. Основы EXPLAIN в PostgreSQL

Почему запрос выполняется так долго? Почему не используются индексы?
Наверное, все слышали об EXPLAIN в PostgreSQL. Но не так много тех, кто понимает, как его использовать. Сам длительное время не мог найти доступного для понимания учебника (плохо искал?).
Надеюсь, эта статья поможет желающим разобраться с этим замечательным инструментом.

Это не перевод, а скорее авторская переработка материалов Understanding EXPLAIN от Guillaume Lelarge. Часть информации опущено, так что настоятельно рекомендую ознакомиться с оригиналом.

Не так страшен чёрт

Для оптимизации запросов очень важно понимать логику работы ядра PostgreSQL.
Постараюсь объяснить. На самом деле всё не так сложно. EXPLAIN выводит информацию, необходимую для понимания, что же делает ядро при каждом конкретном запросе. Будем рассматривать вывод команды EXPLAIN, параллельно разбираясь, что же происходит внутри PostgreSQL. Описанное применимо к PostgreSQL 9.2 и выше.

Наши задачи:

• научиться читать и понимать вывод команды EXPLAIN
• понять, что же происходит в PostgreSQL при выполнении запроса

Первые шаги

Тренироваться будем на кошках тестовой таблице в миллион строк

CREATE TABLE foo (c1 integer, c2 text);
INSERT INTO foo
  SELECT i, md5(random()::text)
  FROM generate_series(1, 1000000) AS i;

Попробуем прочитать данные

EXPLAIN SELECT * FROM foo;

QUERY PLAN
— Seq Scan on foo (cost=0.00..18334.00 rows=1000000 width=37)
(1 row)

Чтение данных из таблицы может выполняться несколькими способами. В нашем случае EXPLAIN сообщает, что используется Seq Scan — последовательное, блок за блоком, чтение данных таблицы foo.
Что такое cost? Это не время, а некое сферическое в вакууме понятие, призванное оценить затратность операции. Первое значение 0.00 — затраты на получение первой строки. Второе — 18334.00 — затраты на получение всех строк.
rows — приблизительное количество возвращаемых строк при выполнении операции Seq Scan. Это значение возвращает планировщик. В моём случае оно совпадает с реальным количеством строк в таблице.
width — средний размер одной строки.
Попробуем добавить 10 строк.

INSERT INTO foo
  SELECT i, md5(random()::text)
  FROM generate_series(1, 10) AS i;
EXPLAIN SELECT * FROM foo;

QUERY PLAN
— Seq Scan on foo (cost=0.00..18334.00 rows=1000000 width=37)
(1 row)

Значение rows не изменилось. Статистика по таблице старая. Для обновления статистики вызываем команду ANALYZE.

ANALYZE foo;
EXPLAIN SELECT * FROM foo;

QUERY PLAN
— Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37)
(1 row)

Теперь rows отображает правильное количество строк.

Что происходит при выполнении ANALYZE?

• Считывается определённое количество строк таблицы, выбранных случайным образом
• Собирается статистика значений по каждой из колонок таблицы:

Сколько строк будет считывать ANALYZE — зависит от параметра default_statistics_target.

Реальные данные

Всё, что мы видели выше в выводе команды EXPLAIN — только ожидания планировщика. Попробуем сверить их с результатами на реальных данных. Используем EXPLAIN (ANALYZE) .

EXPLAIN (ANALYZE) SELECT * FROM foo;

QUERY PLAN
— Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.012..61.524 rows=1000010 loops=1)
Total runtime: 90.944 ms
(2 rows)

Такой запрос будет исполняется реально. Так что если вы выполняете EXPLAIN (ANALYZE) для INSERT или UPDATE, ваши данные изменятся. Будьте внимательны! В таких случаях используйте команду ROLLBACK.
В выводе команды информации добавилось.
actual time — реальное время в миллисекундах, затраченное для получения первой строки и всех строк соответственно.
rows — реальное количество строк, полученных при Seq Scan.
loops — сколько раз пришлось выполнить операцию Seq Scan.
Total runtime — общее время выполнения запроса.

Далее...

Для первой части, наверное, достаточно. Если сообществу будет интересно (спасибо за инвайт), далее продолжу о то том, что происходит на уровне чтения из файла, использования кэша и индексов.

Автор: zoroda

Источник