Большая часть оптимизаций запросов к базам PostgreSQL может выполняться "механически", следуя разного рода маркерам в плане выполнения запроса, которые подскажут, что и как можно ускорить. Но "глубинные" переработки алгоритма, вроде описанных в статье про DBA-детектив, требуют от разработчика детального понимания используемой структуры логических связей.

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

А ведь она уже и так находится "под ногами" в момент анализа плана запроса - надо только лишь удобно увидеть ее!

Давайте проведем анализ на примере совсем простого запроса:

EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM pg_stat_user_tables WHERE schemaname = 'public';
GroupAggregate (actual time=15.756..17.407 rows=173 loops=1)
  Group Key: c.oid, n.nspname, c.relname
  ->  Sort (actual time=4.490..4.771 rows=2806 loops=1)
        Sort Key: c.oid, c.relname
        Sort Method: quicksort  Memory: 842kB
        ->  Nested Loop Left Join (actual time=0.537..3.538 rows=2806 loops=1)
              ->  Nested Loop (actual time=0.526..1.228 rows=173 loops=1)
                    Join Filter: (c.relnamespace = n.oid)
                    Rows Removed by Join Filter: 201
                    ->  Index Scan using pg_namespace_nspname_index on pg_namespace n (actual time=0.017..0.018 rows=1 loops=1)
                          Index Cond: (nspname = 'public'::name)
                          Filter: (nspname !~ '^pg_toast'::text)
                    ->  Seq Scan on pg_class c (actual time=0.009..1.168 rows=374 loops=1)
                          Filter: (relkind = ANY ('{r,t,m}'::"char"[]))
                          Rows Removed by Filter: 3187
              ->  Index Scan using pg_index_indrelid_index on pg_index i (actual time=0.002..0.010 rows=16 loops=173)
                    Index Cond: (c.oid = indrelid)
Planning time: 2.398 ms
Execution time: 17.660 ms

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

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

Итак, мы получили следующую информацию:

  • при выполнении запроса к системному представлению pg_stat_user_tables задействуются три таблицы: pg_namespace (алиас n), pg_class (алиас c) и pg_index (алиас i)

  • дополнительно мы получили информацию о существовании в них полей c.oid, c.relname, c.relnamespace, c.relkind, n.oid, n.nspname, i.indrelid

  • также мы видим информацию о значениях некоторых из них, что позволяет сделать выводы об их типе:

    • n.nspname = 'public'::name => nspname::name

    • c.relkind = ANY ('{r,t,m}'::"char"[]) => relkind::"char"

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

    • c.relnamespace = n.oid

    • c.oid = i.indrelid

Теперь нам остается только аккуратно отразить на вкладке "Структура" в нашем сервисе полученную информацию - таблицы и использованные индексы, их поля и связи:

Визуализация связей объектов базы
Визуализация связей объектов базы

Хотим знать больше полей? Используем VERBOSE:

EXPLAIN (ANALYZE, VERBOSE, COSTS OFF)
SELECT * FROM pg_stat_user_tables WHERE schemaname = 'public';
GroupAggregate (actual time=26.777..29.790 rows=173 loops=1)
  Output: c.oid, n.nspname, c.relname, pg_stat_get_numscans(c.oid), pg_stat_get_tuples_returned(c.oid), (sum(pg_stat_get_numscans(i.indexrelid)))::bigint, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)), pg_stat_get_tuples_inserted(c.oid), pg_stat_get_tuples_updated(c.oid), pg_stat_get_tuples_deleted(c.oid), pg_stat_get_tuples_hot_updated(c.oid), pg_stat_get_live_tuples(c.oid), pg_stat_get_dead_tuples(c.oid), pg_stat_get_mod_since_analyze(c.oid), pg_stat_get_last_vacuum_time(c.oid), pg_stat_get_last_autovacuum_time(c.oid), pg_stat_get_last_analyze_time(c.oid), pg_stat_get_last_autoanalyze_time(c.oid), pg_stat_get_vacuum_count(c.oid), pg_stat_get_autovacuum_count(c.oid), pg_stat_get_analyze_count(c.oid), pg_stat_get_autoanalyze_count(c.oid)
  Group Key: c.oid, n.nspname, c.relname
  ->  Sort (actual time=13.829..14.284 rows=2806 loops=1)
        Output: c.oid, n.nspname, c.relname, i.indexrelid
        Sort Key: c.oid, c.relname
        Sort Method: quicksort  Memory: 842kB
        ->  Nested Loop Left Join (actual time=0.720..11.421 rows=2806 loops=1)
              Output: c.oid, n.nspname, c.relname, i.indexrelid
              ->  Hash Join (actual time=0.660..1.490 rows=173 loops=1)
                    Output: c.oid, c.relname, n.nspname
                    Inner Unique: true
                    Hash Cond: (c.relnamespace = n.oid)
                    ->  Seq Scan on pg_catalog.pg_class c (actual time=0.015..1.376 rows=374 loops=1)
                          Output: c.oid, c.relname, c.relnamespace
                          Filter: (c.relkind = ANY ('{r,t,m}'::"char"[]))
                          Rows Removed by Filter: 3187
                    ->  Hash (actual time=0.024..0.024 rows=1 loops=1)
                          Output: n.nspname, n.oid
                          Buckets: 1024  Batches: 1  Memory Usage: 9kB
                          ->  Index Scan using pg_namespace_nspname_index on pg_catalog.pg_namespace n (actual time=0.020..0.021 rows=1 loops=1)
                                Output: n.nspname, n.oid
                                Index Cond: (n.nspname = 'public'::name)
                                Filter: (n.nspname !~ '^pg_toast'::text)
              ->  Index Scan using pg_index_indrelid_index on pg_catalog.pg_index i (actual time=0.026..0.054 rows=16 loops=173)
                    Output: i.indexrelid, i.indrelid, i.indnatts, i.indisunique, i.indisprimary, i.indisexclusion, i.indimmediate, i.indisclustered, i.indisvalid, i.indcheckxmin, i.indisready, i.indislive, i.indisreplident, i.indkey, i.indcollation, i.indclass, i.indoption, i.indexprs, i.indpred
                    Index Cond: (c.oid = i.indrelid)
Planning time: 3.151 ms
Execution time: 30.219 ms

Теперь, зная структуру связей в базе, мы можем написать более эффективный запрос, если нам не нужна информация из самой схемы:

EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM pg_class WHERE relnamespace = (
  SELECT oid FROM pg_namespace WHERE nspname = 'public'
);
Seq Scan on pg_class (actual time=0.020..1.002 rows=2992 loops=1)
  Filter: (relnamespace = $0)
  Rows Removed by Filter: 569
  InitPlan 1 (returns $0)
    ->  Index Scan using pg_namespace_nspname_index on pg_namespace (actual time=0.010..0.011 rows=1 loops=1)
          Index Cond: (nspname = 'public'::name)
Planning time: 0.110 ms
Execution time: 1.185 ms

И да, связь со вложенным InitPlan мы тоже увидим при визуализации структуры:

Отражение InitPlan на структуре базы
Отражение InitPlan на структуре базы

Поделиться анализом плана с иностранными коллегами стало еще проще - у нашего сервиса появилось англоязычное "зеркало" explain-postgresql.com.

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


  1. baldr
    17.01.2022 18:36

    Так вот для чего вы завели этот сервис! Чтобы собирать планы запросов со всех концов света, а потом реверсить и строить схемы баз!

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

    А вообще говоря, сервис у вас крутой, спасибо что развиваете и прислушиваетесь к фича-реквестам - я просил английский перевод и еще раз спасибо за него!

    Кстати, у вас там на фавиконке кто-то что-то непотребное нарисовал


    1. Kilor Автор
      17.01.2022 18:48
      +1

      у меня клиент запретил использовать ваш сервис для визуализации плана чтобы избежать утечки запросов и все такое

      Будет-будет (когда-то) возможность локального разворота, но, скорее всего, уже платно.

      у вас там на фавиконке

      Это же слоник! )) https://explain.tensor.ru/about/


      1. baldr
        17.01.2022 18:56
        +1

        Будет-будет (когда-то) возможность локального разворота, но, скорее всего, уже платно.

        Логично. Но вряд ли я лично его буду использовать платно.

        Это же слоник! ))

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

        Вы лучше бы его спереди нарисовали тогда


  1. rrrad
    17.01.2022 19:34

    Попробовал открыть для одного не самого сложного запроса. Очень долго крутилась картинка прогресса (фактически подвесив вкладку). Когда я уже собирался написать сюда, структура таки построилась.

    В общем, пока-что это больше прототип, чем рабочее решение. Нарисовал что-то невразумительное на кучу экранов, включающее кучу псевдорелейшнов SP_nn, а также CTE-шки из запроса (уж их то можно было детектировать... или так было задумано?).

    один из экранов

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


    1. Kilor Автор
      17.01.2022 19:39

      Рисовать SP/IP/CTE с содержимым - задумано, а вот если черное оно, как и повторное рисование - баг. Можно ссылочку?


      1. rrrad
        17.01.2022 20:07

        отправил в личку


  1. GravityTwoG
    18.01.2022 06:57

    А есть похожие инструменты не для отображения существующей схемы, а для проектирования? Чтобы можно было создавать таблицы, задавать отношения визуально?


    1. des1roer
      18.01.2022 09:49

      вот https://www.mysql.com/products/workbench/ оно правда для mysql, но в целом DDL получится примерно тот же