Понимаем планы PostgreSQL-запросов еще удобнее

    Полгода назад мы представили explain.tensor.ru — публичный сервис для разбора и визуализации планов запросов к PostgreSQL.



    За прошедшие месяцы мы сделали про него доклад на PGConf.Russia 2020, подготовили обобщающую статью по ускорению SQL-запросов на основе рекомендаций, которые он выдает… но самое главное — собирали ваши отзывы и смотрели за реальными use case.

    И теперь готовы рассказать о новых возможностях, которыми вы можете пользоваться.

    Поддержка разных форматов планов


    План из лога, вместе с запросом


    Прямо с консоли выделяем весь блок, начиная со строки с Query Text, со всеми лидирующими пробелами:

            Query Text: INSERT INTO  dicquery_20200604  VALUES ($1.*) ON CONFLICT (query)
                               DO NOTHING;
            Insert on dicquery_20200604  (cost=0.00..0.05 rows=1 width=52) (actual time=40.376..40.376 rows=0 loops=1)
              Conflict Resolution: NOTHING
              Conflict Arbiter Indexes: dicquery_20200604_pkey
              Tuples Inserted: 1
              Conflicting Tuples: 0
              Buffers: shared hit=9 read=1 dirtied=1
              ->  Result  (cost=0.00..0.05 rows=1 width=52) (actual time=0.001..0.001 rows=1 loops=1)
    

    … и закидываем все скопированное прямо в поле для плана, ничего не разделяя:



    На выходе получаем бонусом к разобранному плану еще и вкладку «контекст», где наш запрос представлен во всей красе:



    JSON и YAML


    EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
    SELECT * FROM pg_class;

    "[
      {
        "Plan": {
          "Node Type": "Seq Scan",
          "Parallel Aware": false,
          "Relation Name": "pg_class",
          "Alias": "pg_class",
          "Startup Cost": 0.00,
          "Total Cost": 1336.20,
          "Plan Rows": 13804,
          "Plan Width": 539,
          "Actual Startup Time": 0.006,
          "Actual Total Time": 1.838,
          "Actual Rows": 10266,
          "Actual Loops": 1,
          "Shared Hit Blocks": 646,
          "Shared Read Blocks": 0,
          "Shared Dirtied Blocks": 0,
          "Shared Written Blocks": 0,
          "Local Hit Blocks": 0,
          "Local Read Blocks": 0,
          "Local Dirtied Blocks": 0,
          "Local Written Blocks": 0,
          "Temp Read Blocks": 0,
          "Temp Written Blocks": 0
        },
        "Planning Time": 5.135,
        "Triggers": [
        ],
        "Execution Time": 2.389
      }
    ]"

    Хоть с внешними кавычками, как копирует pgAdmin, хоть без — кидаем в то же поле, на выходе — красота:



    Расширенная визуализация


    Planning Time / Execution Time


    Теперь лучше видно, куда ушло дополнительное время при выполнении запроса:



    I/O Timing


    Иногда приходится сталкиваться с ситуацией, когда в плане вроде и ресурсов читалось-писалось не слишком много, а вроде и время выполнения несообразно большое какое-то.

    Тут приходится говорить: "Ой, наверное, в тот момент диск на сервере был слишком перегружен, поэтому читалось так долго!" Но как-то это не слишком точно…

    Но можно это определить абсолютно достоверно. Дело в том, что среди опций конфигурации PG-сервера есть track_io_timing:
    Включает замер времени операций ввода/вывода. Этот параметр по умолчанию отключён, так как для этого требуется постоянно запрашивать текущее время у операционной системы, что может значительно замедлить работу на некоторых платформах. Для оценивания издержек замера времени на вашей платформе можно воспользоваться утилитой pg_test_timing. Статистику ввода/вывода можно получить через представление pg_stat_database, в выводе EXPLAIN (когда используется параметр BUFFERS) и через представление pg_stat_statements.
    Этот параметр можно включить и в рамках локальной сессии:

    SET track_io_timing = TRUE;

    Ну, а теперь самое приятное — мы научились понимать и отображать эти данные с учетом всех трансформаций дерева исполнения:



    Тут можно заметить, что из 0.790ms всего времени исполнения 0.718ms заняло чтение одной страницы данных, 0.044ms — запись ее же, а на всю остальную полезную активность было потрачено всего 0.028ms!

    Будущее с PostgreSQL 13


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

    Planning buffers


    Учет ресурсов, выделенных планировщику, нашел свое отражение еще в одном патче, не относящемуся к pg_stat_statements. EXPLAIN с опцией BUFFERS будет сообщать количество буферов, использованных на этапе планирования:

     Seq Scan on pg_class (actual rows=386 loops=1)
       Buffers: shared hit=9 read=4
     Planning Time: 0.782 ms
       Buffers: shared hit=103 read=11
     Execution Time: 0.219 ms



    Инкрементальная сортировка


    В случаях, когда необходима сортировка по многим ключам (k1, k2, k3…), планировщик теперь может воспользоваться знанием о том, что данные уже отсортированы по нескольким из первых ключей (например, k1 и k2). В этом случае можно не пересортировывать все данные заново, а разделить их на последовательные группы с одинаковыми значениями k1 и k2, и “досортировать” по ключу k3.

    Таким образом вся сортировка распадается на несколько последовательных сортировок меньшего размера. Это снижает объем необходимой памяти, а еще позволяет выдавать первые данные раньше, чем вся сортировка будет выполнена полностью.

     Incremental Sort (actual rows=2949857 loops=1)
       Sort Key: ticket_no, passenger_id
       Presorted Key: ticket_no
       Full-sort Groups: 92184 Sort Method: quicksort Memory: avg=31kB peak=31kB
       ->  Index Scan using tickets_pkey on tickets (actual rows=2949857 loops=1)
     Planning Time: 2.137 ms
     Execution Time: 2230.019 ms




    Улучшения UI/UX


    Скриншоты, они везде!


    Теперь на каждой вкладке появилась возможность быстро взять скриншот вкладки в буфер обмена на всю ширину и глубину вкладки — «прицел» справа-сверху:



    Собственно, большинство картинок для этой публикации получено именно так.

    Рекомендации на узлах


    Их не только стало больше, но и про каждую можно подробно прочитать в статье, перейдя по ссылке:



    Удаление из архива


    Некоторые очень просили добавить возможность удалять «совсем» даже непубликуемые в архиве планы — пожалуйста, достаточно нажать соответствующую иконку:



    Ну, и не забываем, что у нас есть группа поддержки, куда можно писать свои замечания и предложения.
    Тензор
    Разработчик системы СБИС

    Похожие публикации

    Комментарии 10

      +2

      Спасибо за explain.tensor.ru.
      Планируется ли английский интерфейс?

        0
        Планируется, но нескоро.
          +1

          А вы не хотите выложить проект в open source? Мы поможем перевести. Это ведь инструмент для PostgreSQL, ему положено быть открытым :)

            0
            Хотим, но это долгий путь. А пока можно свободно пользоваться как инструментом. :)
        0
        Отличный инструмент! Было бы хорошо добавить поддержку MySQL 8 и его explain analyze. Он весьма похож, чуть другие ноды плана.
        0
          0
          Если внезапно не починили, то там те же проблемы с CTE, что и у depesz: youtu.be/5XKbFb-l5Do?t=755
          +1
          Пользуюсь постоянно, супер инструмент!
            0
            У меня на домашней странице!

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

          Самое читаемое