Разработчик
0,0
рейтинг
23 ноября 2013 в 09:38

Разработка → Оптимизация запросов. Основы 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, DELETE или UPDATE, ваши данные изменятся. Будьте внимательны! В таких случаях используйте команду ROLLBACK.
В выводе команды информации добавилось.
actual time — реальное время в миллисекундах, затраченное для получения первой строки и всех строк соответственно.
rows — реальное количество строк, полученных при Seq Scan.
loops — сколько раз пришлось выполнить операцию Seq Scan.
Total runtime — общее время выполнения запроса.

Далее...

Для первой части, наверное, достаточно.
Часть 2
Часть 3
@zoroda
карма
16,0
рейтинг 0,0
Разработчик
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Спецпроект

Самое читаемое Разработка

Комментарии (13)

  • +2
    Мне кажется, что для первой части уж больно банальной получилась статья. Стоит обсудить более сложные вопросы. Хабр, все же, не детсад…
    • 0
      Да, конечно, в продолжении будут более сложные вещи. Просто решил начать с самых основ, как в оригинале. Да не рассчитал с объёмом материала. Потому решил разделить на части.
  • +3
    только размечтался об интересностях и на тебе конец части. это заметка о синтаксисе команды EXPLAIN. даешь полную статью
    • 0
      Продолжение следует. Постараюсь ужать оригинал, чтобы уложиться в разумное количество частей.
  • 0
    Интересная тема. Было бы хорошо в следующих частях освятить оптимизацию запросов более подробно (join или подзапрос, тп), может быть рассказать про какие то типы колонок и как они влияют или не влияют на производительность (н-р CHAR, VARCHAR, TEXT).
    Еще есть пожелание добавить пару тройку (десятку) ссылок, которые относятся к теме. Может быть освятить опции оптимизатора в postgresql.conf.
    • 0
      Да, в оригинале есть интересные моменты. Полагаю, во второй части успеем разобрать некоторые тонкости.
  • 0
    Неясно, почему бы не сделать нормальный перевод. Зачем выкидывать кучу деталей из статьи, ориентированной на новичков? Один пример:

    Такой запрос будет исполняется реально. Так что если вы выполняете EXPLAIN (ANALYZE) для INSERT или UPDATE, ваши данные изменятся. Будьте внимательны! В таких случаях используйте команду ROLLBACK.

    Во-первых, без оборачивания в транзакцию от ROLLBACK не будет никакого толка, а во-вторых, ни слова про команду DELETE. Она не изменит данные что ли? Конечно, вещи очевидные, но не для новичков же. А не новички ничего нового не узнают (и им тем более нужны детали). В оригинале, при этом, все ок.

    Или еще: «width — средний размер одной строки». В попугаях? Опять надо лезть в оригинал (там все ок).

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

    В оригинале — 300*default_statistics_target. Четкая и простая зависимость.

    Неясно, зачем портить статьи. Советую всем прочитать оригинал.

    • –1
      Согласен с вами. За деталями однозначно — в оригинал.
      Своей целью считаю по возможности более краткое изложение оригинала. Некоторые моменты типа 300*default_statistics_target посчитал излишней информацией. Думаю, что для начала достаточно знать, что есть параметр, при помощи которого моно управлять глубиной анализа. Если кому нужна конкретика — она есть в оригинале.
      Использование транзакций не входит в тематику статьи. Полагаю, достаточно дать намёк на ROLLBACK. Возможно, напишу отдельную статью про использование транзакций при отладке.
      Команду DELETE мне ни разу не приходилось отлаживать, в отличие от UPDATE.
      В любом случае, за указание на неточности — спасибо. Подумаю, как лучше внести исправления.
  • +1
    Это не время, а некое сферическое в вакууме понятие, призванное оценить затратность операции.

    Если только он запросы делает не в вакууме. Это понятие граничит со временем. Вот что нужно знать про cost:

    • Costs are estimates of the time a node is expected to take
    • By default costs are in units of “time a sequential 8kb block read takes”
    • Each node has two costs, “startup” cost and “total” cost
    • Costs cumulative – parents assume their children's costs
    • Optimizer selects plans based on overall lowest startup and total cost


    width — средний размер одной строки.

    В байтах.

    Статья слишком простая. Нет не про типа scans (Index Scan, Index Only Scan, Bitmap Index Scan), не про типы Jons и сортировок при чтении EXPLAIN.
    • 0
      Не граничит оно никак со временем. Cost зависит от количества операций и их условной стоимости (planner cost constants). Эти константы, по уму, должны зависеть от особенностей машины, где работает постгрес (например, random_page_cost — разная для разных типов дисков, а effective_cache_size зависит от количества доступной памяти). В результате из всех возможных планов выбирается план с наименьшей стоимостью. А время тут ни при чем.

      В принципе, при грамотном выставлении planner cost constants теоретически можно добиться того, что cost будет прямо пропорционален затраченному времени, но сама по себе эта связь не обязательна.
      • 0
        Первый и второй пункт в моем коментарии четко говорят, что такое costs: «оценки времени узла, как ожидается, займет при чтении данных», один юнит в основном: время чтения 8kb данных при использовании seq_page_cost. Понятное дело, что он разный при выборе другого плана выполнения:

        seq_page_cost — 1.00 — единица
        random_page_cost — 4.00 — cost увеличился, поскольку планер берет в 4 раза больше на операцию, но единица измерения осталась та же
        cpu_tuple_cost — 0.01 — в 100 раз быстрее seq_page_cost
        cpu_operator_cost — 0.0025 — в 400 раз быстрее seq_page_cost
        cpu_index_tuple_cost — 0.005 — в 1000 раз быстрее seq_page_cost

        Везде говорится про время чтения с диска.
        • 0
          И? Если стоимость одного запроса в два раза больше стоимости другого запроса, это не значит, что первый запрос будет выполняться в два раза медленней. Можно неоптимально прописать эти константы, и никакой зависмости не будет. Что я пытаюсь сказать уже второй комментарий.

          единица измерения осталась та же

          А если в seq_page_cost прописать 3.14, то какой, по-вашему, будет единица измерения?
          • 0
            Так и останется 1. Увеличится число cost в EXPLAIN, где будет seq_page_cost.

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