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

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