Реверс-инжинирим структуру БД PostgreSQL по плану запроса к ней

Большая часть оптимизаций запросов к базам 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.

Читайте так же:

  • WordPress спасет поисковик Creative Commons от закрытияWordPress спасет поисковик Creative Commons от закрытия Глава Automattic Мэтт Мулленвег сообщил, что поисковик бесплатных изображений СС Search от Creative Commons станет частью WordPress.org. Это позволит уберечь его от закрытия. По словам Мулленвега, он решил взять проект «под свое крыло» после того, как услышал о том. Что он может быть […]
  • Яндекс.Маркет упростил возврат товаров через Почту РоссииЯндекс.Маркет упростил возврат товаров через Почту России Пользователи Яндекс.Маркета теперь могут легко вернуть товары через Почту России. Бесплатный возврат можно оформить за считанные минуты в личном кабинете на маркетплейсе, а затем без очереди сдать товары в любом почтовом отделении. Вернуть Маркету покупки с помощью Почты России […]
  • Исследование: холестерин в головном мозге регулирует развитие болезни АльцгеймераИсследование: холестерин в головном мозге регулирует развитие болезни Альцгеймера Группа нейрофизиологов Исследовательского института Скриппса (TSRI) представила новую точку зрения на природу возникновения болезни Альцгеймера. Исследования показали, что холестерин регулирует производство связанного с болезнью Альцгеймера белка бета-амилоида (Aβ).Образования […]
  • Смартфоны iPhone 13 получат поддержку быстрой зарядки мощностью 25 ВтСмартфоны iPhone 13 получат поддержку быстрой зарядки мощностью 25 Вт Согласно сообщениям из цепочки поставок Apple, некоторые модели iPhone 13 будут поддерживать технологию быстрой зарядки мощностью 25 Вт. Можно предположить, что речь идёт о старших моделях: iPhone 13 Pro и iPhone 13 Pro Max. Конечно же, это далеко не рекордная мощность зарядки, если […]