Структура плана выполнения запроса представляет собой дерево узлов плана. Узлы на нижнем уровне дерева являются узлами сканирования: они возвращают необработанные строки из таблицы. Существуют различные типы узлов сканирования для разных методов доступа к таблицам: последовательное сканирование, сканирование индекса и сканирование индекса по битовой карте. Существуют также источники строк, не являющиеся таблицами, такие как VALUES Предложения запроса и функции, возвращающие наборы данных, в Предложение FROM, которые имеют собственные типы узлов сканирования. Если запрос требует выполнения соединения, агрегирования, сортировки или других операций над исходными строками, то для их реализации над узлами сканирования размещаются дополнительные узлы. Опять же, обычно существует несколько возможных способов выполнения данных операций, поэтому здесь также могут встречаться различные типы узлов. Вывод команды EXPLAIN содержит по одной строке для каждого узла в дереве плана; в строке указывается базовый тип узла и оценки стоимости, сформированные планировщиком для выполнения данного узла плана. Могут отображаться дополнительные строки, расположенные с отступом от сводной строки узла, для вывода дополнительных свойств узла. В самой первой строке (сводной строке самого верхнего узла) приводится оценочная общая стоимость выполнения плана; именно это значение планировщик стремится минимизировать.

Ниже приведен элементарный пример для демонстрации формата вывода:

EXPLAIN SELECT * FROM tenk1;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)

Поскольку в данной команде SELECT отсутствует WHERE WHERE, требуется сканирование всех строк таблицы, вследствие чего планировщик выбрал план с использованием последовательного сканирования. Значения, указанные в скобках (слева направо), означают следующее:

Затраты измеряются в произвольных единицах, определяемых параметрами стоимости планировщика (см. Раздел 3.4.7.2). Согласно традиционной практике, стоимость измеряется в единицах чтения дисковых страниц; то есть, параметр seq_page_cost традиционно принимается равным 1.0 а остальные параметры стоимости устанавливаются относительно данного значения. Примеры в данном разделе приведены с использованием параметров стоимости по умолчанию.

Важно учитывать, что стоимость узла верхнего уровня включает в себя стоимость всех его дочерних узлов. Также необходимо понимать, что стоимость отражает только те факторы, которые учитываются планировщиком. В частности, стоимость не включает время, затраченное на преобразование выходных значений в текстовый формат или их передачу клиенту, что может существенно влиять на общее фактическое время выполнения; но планировщик игнорирует данные затраты, поскольку он не может изменить их посредством изменения плана выполнения запроса. (Предполагается, что каждый корректный план выполнения запроса будет возвращать идентичный набор строк.)

Значение строки параметра является специфическим, так как оно представляет собой не количество строк, обработанных или сканированных узлом плана выполнения запроса, а количество строк, возвращенных этим узлом. Данный показатель часто меньше количества сканированных строк вследствие фильтрации по условиям WHEREпредложения WHERE, применяемым в узле. В идеале оценка количества строк на верхнем уровне должна быть близка к количеству строк, фактически возвращенных, измененных в ходе операции UPDATE или удаленных запросом.

Возврат к рассматриваемому примеру:

EXPLAIN SELECT * FROM tenk1;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)

Данные значения вычисляются напрямую. При выполнении команды:

SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';

можно обнаружить, что tenk1 содержит 345 дисковых страниц и 10000 строк. Расчетная стоимость вычисляется как (количество прочитанных страниц диска * seq_page_cost) + (количество просканированных строк * cpu_tuple_cost). По умолчанию seq_page_cost составляет 1.0, а cpu_tuple_cost составляет 0.01, поэтому расчетная стоимость равна (345 * 1.0) + (10000 * 0.01) = 445.

Рассмотрим изменение запроса путем добавления WHERE условия:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..470.00 rows=7000 width=244)
   Filter: (unique1 < 7000)

Обратите внимание, что EXPLAIN в выводе представлено WHERE предложение, применяемое в качестве «фильтрующего» условия, прикрепленного к узлу плана Seq Scan. Это означает, что узел плана проверяет условие для каждой сканируемой строки и выводит только те строки, которые соответствуют условию. Оценка количества выходных строк была уменьшена вследствие применения WHERE предложения. Тем не менее, при сканировании все равно придется посетить все 10000 строк, поэтому стоимость не уменьшилась; на самом деле она даже немного возросла (на 10000 * cpu_operator_cost, если быть точным) ввиду учета дополнительного времени CPU, затраченного на проверку WHERE условия.

Фактическое количество строк, выбираемых данным запросом, составляет 7000, но строки оценка является лишь приблизительной. При попытке повторить этот эксперимент вы вполне можете получить несколько иную оценку; более того, она может изменяться после каждой ANALYZE команды, так как статистические данные, формируемые ANALYZE , берутся из случайной выборки таблицы.

Теперь сделаем условие более строгим:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

                                  QUERY PLAN
-------------------------------------------------------------------​ -----------
 Bitmap Heap Scan on tenk1  (cost=5.06..224.98 rows=100 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
         Index Cond: (unique1 < 100)

В данном случае планировщик выбрал двухэтапный план: дочерний узел плана обращается к индексу для поиска местоположения строк, соответствующих условию индекса, после чего вышестоящий узел плана осуществляет выборку этих строк непосредственно из таблицы. Построчная выборка строк обходится значительно дороже, чем их последовательное чтение, однако, поскольку обращение происходит не ко всем страницам таблицы, этот метод остается более экономичным, чем последовательное сканирование. (Причина использования двух уровней плана заключается в том, что вышестоящий узел плана выполняет сортировку адресов строк, определенных индексом, в физическом порядке перед их чтением, чтобы минимизировать затраты на отдельные операции выборки. «Битовая карта» , упоминаемая в названиях узлов, представляет собой механизм, выполняющий сортировку.)

Теперь добавим еще одно условие в WHERE предложение:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';

                                  QUERY PLAN
-------------------------------------------------------------------​ -----------
 Bitmap Heap Scan on tenk1  (cost=5.04..225.20 rows=1 width=244)
   Recheck Cond: (unique1 < 100)
   Filter: (stringu1 = 'xxx'::name)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
         Index Cond: (unique1 < 100)

Добавленное условие stringu1 = 'xxx' снижает оценку количества выходных строк, но не стоимость, так как все равно требуется обработка того же набора строк. Это связано с тем, что stringu1 предложение не может быть применено в качестве условия индекса, так как данный индекс построен только по столбцу unique1 column. Вместо этого оно применяется в качестве фильтра к строкам, полученным с помощью индекса. Таким образом, расчетная стоимость немного возрастает, что отражает затраты на выполнение этой дополнительной проверки.

В некоторых случаях планировщик выберет «Простой» План сканирования индекса:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 = 42;

                                 QUERY PLAN
-------------------------------------------------------------------​ ----------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.29..8.30 rows=1 width=244)
   Index Cond: (unique1 = 42)

При таком типе плана строки таблицы извлекаются в порядке индекса, что делает их чтение еще более затратным, однако их настолько мало, что дополнительные издержки на сортировку физического расположения строк не оправданы. Чаще всего данный тип плана встречается в запросах, извлекающих одну строку. Он также часто используется для запросов, содержащих ORDER BY условие, соответствующее порядку индекса, поскольку в этом случае для выполнения требований не требуется дополнительный этап сортировки ORDER BY. В данном примере добавление ORDER BY unique1 приведет к использованию того же плана, поскольку индекс уже неявно обеспечивает требуемую упорядоченность.

Планировщик может реализовывать ORDER BY предложение несколькими способами. Приведенный выше пример показывает, что подобное предложение упорядочивания может быть реализовано неявно. Планировщик также может добавить явный Sort step:

EXPLAIN SELECT * FROM tenk1 ORDER BY unique1;

                            ПЛАН ВЫПОЛНЕНИЯ ЗАПРОСА
-------------------------------------------------------------------
 Sort  (cost=1109.39..1134.39 rows=10000 width=244)
   Sort Key: unique1
   ->  Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)

Если часть плана гарантирует упорядочивание по префиксу требуемых ключей сортировки, планировщик может принять решение использовать инкрементальную сортировку step:

EXPLAIN SELECT * FROM tenk1 ORDER BY hundred, ten LIMIT 100;

                                              QUERY PLAN
-------------------------------------------------------------------​ -----------------------------
 Оператор LIMIT  (cost=19.35..39.49 rows=100 width=244)
   ->  Incremental Sort  (cost=19.35..2033.39 rows=10000 width=244)
         Sort Key: hundred, ten
         Предварительно отсортированный ключ: hundred
         ->  Сканирование индекса с использованием tenk1_hundred по tenk1  (cost=0.29..1574.20 rows=10000 width=244)

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

При наличии отдельных индексов для нескольких столбцов, на которые ссылается WHERE, планировщик может выбрать использование комбинации индексов с применением операторов AND или OR:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;

                                     QUERY PLAN
-------------------------------------------------------------------​ ------------------
 Bitmap Heap Scan on tenk1  (cost=25.07..60.11 rows=10 width=244)
   Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
   ->  Узел BitmapAnd  (cost=25.07..25.07 rows=10 width=0)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
               Index Cond: (unique1 < 100)
         ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..19.78 rows=999 width=0)
               Index Cond: (unique2 > 9000)

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

Ниже приведен пример, демонстрирующий влияние LIMIT:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;

                                     QUERY PLAN
-------------------------------------------------------------------​ ------------------
 Limit  (cost=0.29..14.28 rows=2 width=244)
   ->  Index Scan using tenk1_unique2 on tenk1  (cost=0.29..70.27 rows=10 width=244)
         Index Cond: (unique2 > 9000)
         Filter: (unique1 < 100)

Это тот же запрос, что и выше, но в него добавлен LIMIT чтобы исключить необходимость извлечения всех строк; при этом планировщик пересмотрел стратегию выполнения. Обратите внимание, что общая стоимость и количество строк для узла «Сканирование индекса» указаны так, как если бы он был выполнен до конца. Однако ожидается, что узел LIMIT прекратит работу после получения лишь пятой части этих строк, поэтому его общая стоимость составляет только одну пятую от исходной, что и является фактической оценочной стоимостью запроса. Данный план предпочтительнее добавления узла LIMIT к предыдущему плану, так как узел LIMIT не позволил бы избежать затрат на запуск сканирования битовой карты, в результате чего общая стоимость при таком подходе превысила бы 25 единиц.

Рассмотрим соединение двух таблиц с использованием столбцов, упомянутых ранее:

EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;

                                      ПЛАН ВЫПОЛНЕНИЯ ЗАПРОСА
-------------------------------------------------------------------​ -------------------
 Nested Loop  (cost=4.65..118.50 rows=10 width=488)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)
         Recheck Cond: (unique1 < 10)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)
               Index Cond: (unique1 < 10)
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244)
         Index Cond: (unique2 = t1.unique2)

В данном плане представлен узел соединения вложенным циклом с двумя узлами сканирования таблиц в качестве входных данных (дочерних узлов). Величина отступа строк с описанием узлов отражает древовидную структуру плана. Первый, или «внешний», дочерний узел представляет собой сканирование по битовой карте, аналогичное рассмотренным ранее. Его стоимость и количество строк соответствуют значениям, которые были бы получены из SELECT ... WHERE unique1 < 10 поскольку применяется параметр WHERE предложение unique1 < 10 at that node. The t1.unique2 = t2.unique2 предложение еще не является актуальным, поэтому оно не влияет на количество строк внешнего сканирования. Узел соединения вложенным циклом выполнит свой второй или «внутренний» , дочерний узел по одному разу для каждой строки, полученной из внешнего дочернего узла. Значения столбцов из текущей внешней строки могут быть подставлены во внутреннее сканирование; здесь доступно значение t1.unique2 доступно значение из внешней строки, поэтому формируемый план и стоимость аналогичны приведенным выше для простого команда SELECT ... WHERE t2.unique2 = константа случае. (Расчетная стоимость в действительности несколько ниже, чем указанная выше, вследствие кэширования, ожидаемого при повторных сканированиях индекса для t2.) Стоимость узла цикла затем рассчитывается на основе стоимости внешнего сканирования плюс одно повторение внутреннего сканирования для каждой внешней строки (в данном случае 10 * 7.90), а также небольшого объема процессорного времени на обработку соединения.

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

EXPLAIN SELECT *
Предложение FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;

                                         QUERY PLAN
-------------------------------------------------------------------​ --------------------------
 Nested Loop  (cost=4.65..49.36 rows=33 width=488)
   Join Filter: (t1.hundred < t2.hundred)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)
         Recheck Cond: (unique1 < 10)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)
               Index Cond: (unique1 < 10)
   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)
         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)
               Index Cond: (unique2 < 10)

Условие t1.hundred < t2.hundred не может быть проверено при помощи tenk2_unique2 индекса, поэтому оно применяется в узле соединения. Это уменьшает расчетное количество выходных строк узла соединения, но не изменяет параметры входящих узлов сканирования.

Обратите внимание, что в данном случае планировщик выбрал «материализацию» внутреннего отношения соединения, поместив над ним узел плана Materialize. Это означает, что t2 сканирование индекса будет выполнено всего один раз, даже если узлу соединения вложенным циклом (nested-loop join node) потребуется прочитать эти данные десять раз (по одному разу для каждой строки из внешнего отношения). Узел Materialize кэширует данные в памяти по мере их чтения, а затем возвращает их из памяти при каждом последующем проходе.

При работе с внешними соединениями в узлах плана могут присутствовать как условия «Join Filter» так и обычные условия «Filter» прикрепленные условия. Условия Join Filter определяются в предложении внешнего соединения Предложение ON предложения, поэтому строка, не соответствующая условию Join Filter, все еще может быть выведена как строка, дополненная значениями NULL. Однако обычное условие Filter применяется после правил внешнего соединения и, таким образом, безусловно удаляет строки. При внутреннем соединении семантическая разница между этими типами фильтров отсутствует.

Незначительное изменение селективности запроса может привести к существенному изменению плана соединения:

EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                        ПЛАН ВЫПОЛНЕНИЯ ЗАПРОСА
-------------------------------------------------------------------​ -----------------------
 Hash Join  (cost=226.23..709.73 rows=100 width=488)
   Hash Cond: (t2.unique2 = t1.unique2)
   ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244)
   ->  Hash  (cost=224.98..224.98 rows=100 width=244)
         ->  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244)
               Recheck Cond: (unique1 < 100)
               ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
                     Index Cond: (unique1 < 100)

В данном случае планировщик выбрал использование соединения по хешу (hash join), при котором строки одной таблицы заносятся в хеш-таблицу в оперативной памяти, после чего сканируется вторая таблица и в хеш-таблице выполняется поиск совпадений для каждой строки. Снова обратите внимание на то, как отступы отражают структуру плана выполнения запроса: сканирование по битовой карте в tenk1 служит входными данными для узла Hash, который формирует хеш-таблицу. Затем данные возвращаются в узел соединения Hash Join, который считывает строки из своего внешнего дочернего плана и выполняет поиск каждой из них в хеш-таблице.

Другим возможным типом соединения является соединение слиянием (merge join), показанное в данном примере:

EXPLAIN SELECT *
FROM tenk1 t1, onek t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                        ПЛАН ВЫПОЛНЕНИЯ ЗАПРОСА
-------------------------------------------------------------------​ -----------------------
 Merge Join  (cost=0.56..233.49 rows=10 width=488)
   Merge Cond: (t1.unique2 = t2.unique2)
   ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..643.28 rows=100 width=244)
         Filter: (unique1 < 100)
   ->  Index Scan using onek_unique2 on onek t2  (cost=0.28..166.28 rows=1000 width=244)

Соединение слиянием (merge join) требует, чтобы входные данные были отсортированы по ключам соединения. В данном примере каждый набор входных данных сортируется путем сканирования индекса для обращения к строкам в правильном порядке; однако также могут быть использованы последовательное сканирование и сортировка. (Последовательное сканирование с последующей сортировкой часто эффективнее сканирования индекса при сортировке большого количества строк из-за необходимости произвольного доступа к диску при сканировании индекса.)

Одним из способов рассмотрения альтернативных планов является принудительное игнорирование планировщиком любой стратегии, которую он счел наиболее оптимальной, с помощью параметров включения/выключения, описанных в Раздел 3.4.7.1. (Это грубый, но полезный инструмент. См. также Раздел 2.11.3.) Например, если нет уверенности в том, что соединение слиянием является лучшим типом соединения для предыдущего примера, можно попробовать

SET enable_mergejoin = off;

EXPLAIN SELECT *
FROM tenk1 t1, onek t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                        ПЛАН ВЫПОЛНЕНИЯ ЗАПРОСА
-------------------------------------------------------------------​ -----------------------
 Hash Join  (cost=226.23..344.08 rows=10 width=488)
   Hash Cond: (t2.unique2 = t1.unique2)
   ->  Seq Scan on onek t2  (cost=0.00..114.00 rows=1000 width=244)
   ->  Hash  (cost=224.98..224.98 rows=100 width=244)
         ->  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244)
               Recheck Cond: (unique1 < 100)
               ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
                     Index Cond: (unique1 < 100)

это показывает, что, по оценке планировщика, узел соединения Hash Join будет почти на 50% более затратным, чем соединение методом слияния в данном случае. Разумеется, далее возникает вопрос, верна ли эта оценка. Исследовать это можно с помощью EXPLAIN ANALYZE, как описано ниже.

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

EXPLAIN VERBOSE SELECT unique1
FROM tenk1 t
WHERE t.ten < ALL (SELECT o.ten FROM onek o WHERE o.four = t.four);

                               ПЛАН ВЫПОЛНЕНИЯ ЗАПРОСА
-------------------------------------------------------------------​------
 Seq Scan on public.tenk1 t  (cost=0.00..586095.00 rows=5000 width=4)
   Output: t.unique1
   Filter: (ALL (t.ten < (SubPlan 1).col1))
   SubPlan 1
     ->  Seq Scan on public.onek o  (cost=0.00..116.50 rows=250 width=4)
           Output: o.ten
           Filter: (o.four = t.four)

Этот довольно искусственный пример служит для иллюстрации нескольких моментов: значения с внешнего уровня плана могут передаваться в подплан (в данном случае, t.four передаются на нижние уровни), и результаты подзапроса становятся доступны внешнему плану. Данные значения отображаются с помощью EXPLAIN с обозначениями вида (subplan_name).colN, которое относится к N-му выходному столбцу подзапросаSELECT.

В приведенном выше примере оператор ALL повторно запускает подплан для каждой строки внешнего запроса (что объясняет высокую расчетную стоимость). В некоторых запросах во избежание этого может использоваться хэшированный подплан: to avoid that:

EXPLAIN SELECT *
FROM tenk1 t
WHERE t.unique1 NOT IN (SELECT o.unique1 FROM onek o);

                                         QUERY PLAN
-------------------------------------------------------------------​ -------------------------
 Seq Scan on tenk1 t  (cost=61.77..531.77 rows=5000 width=244)
   Filter: (NOT (ANY (unique1 = (hashed SubPlan 1).col1)))
   SubPlan 1
     ->  Index Only Scan using onek_unique1 on onek o  (cost=0.28..59.27 rows=1000 width=4)
(4 rows)

В данном случае подплан выполняется однократно, а его выходные данные загружаются в хеш-таблицу в оперативной памяти, которая затем опрашивается внешним ANY operator. Для этого требуется, чтобы под-SELECT не содержал ссылок на переменные внешнего запроса, а его ANYоператор сравнения поддерживал хеширование.

Если, помимо отсутствия ссылок на переменные внешнего запроса, под-SELECT не может возвращать более одной строки, он может быть реализован как initplan:

EXPLAIN VERBOSE SELECT unique1
FROM tenk1 t1 WHERE t1.ten = (SELECT (random() * 10)::integer);

                             QUERY PLAN
------------------------------------------------------------​ --------
 Seq Scan on public.tenk1 t1  (cost=0.02..470.02 rows=1000 width=4)
   Output: t1.unique1
   Filter: (t1.ten = (InitPlan 1).col1)
   InitPlan 1
     ->  Result  (cost=0.00..0.02 rows=1 width=4)
           Вывод: ((random() * '10'::double precision))::integer

Узел initplan выполняется только один раз за время выполнения внешнего плана, а его результаты сохраняются для повторного использования в последующих строках внешнего плана. Таким образом, в данном примере random() вычисляется только один раз, и все значения t1.ten сравниваются с одним и тем же выбранным случайным образом целым числом. Это существенно отличается от того, что произошло бы без использования данной конструкцииSELECT подзапроса.

Проверить точность оценок планировщика можно с помощью команды EXPLAINпараметра ANALYZE параметр. С этим параметром, EXPLAIN фактически выполняет запрос, а затем отображает фактическое количество строк и фактическое время выполнения, накопленное в каждом узле плана, наряду с теми же оценками, которые выдает обычная EXPLAIN EXPLAIN. Например, можно получить следующий результат:

EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;

                                                           ПЛАН ВЫПОЛНЕНИЯ ЗАПРОСА
-------------------------------------------------------------------​ --------------------------------------------------------------
 Nested Loop  (cost=4.65..118.50 rows=10 width=488) (actual time=0.017..0.051 rows=10 loops=1)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244) (actual time=0.009..0.017 rows=10 loops=1)
         Recheck Cond: (unique1 < 10)
         Heap Blocks: exact=10
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0) (actual time=0.004..0.004 rows=10 loops=1)
               Index Cond: (unique1 < 10)
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244) (actual time=0.003..0.003 rows=1 loops=10)
         Index Cond: (unique2 = t1.unique2)
 Planning time: 0.485 ms
 Execution time: 0.073 ms

Следует учитывать, что «фактическое время» значения указаны в миллисекундах реального времени, тогда как стоимостные оценки выражены в условных единицах; следовательно, они вряд ли будут совпадать. Как правило, наиболее важным фактором является то, насколько расчетное количество строк близко к фактическому. В данном примере все оценки оказались абсолютно точными, что довольно редко встречается на практике.

В некоторых планах выполнения запроса узел подплана может выполняться несколько раз. Например, во фрагменте плана с соединением вложенным циклом (nested-loop) внутреннее сканирование индекса выполняется один раз для каждой строки внешнего набора. В таких случаях циклы параметр loops содержит общее количество выполнений узла, а значения фактического времени и количества строк (rows) представляют собой средние показатели за одно выполнение. Это выполняется для обеспечения сопоставимости чисел со способом представления оценок стоимости. Умножьте на циклы значение для получения общего времени, фактически затраченного в узле. В приведенном выше примере на выполнение сканирования индекса в tenk2.

В некоторых случаях EXPLAIN ANALYZE команда выводит дополнительные статистические данные выполнения, помимо времени выполнения узлов плана и количества строк. Например, узлы Sort и Hash предоставляют дополнительную информацию:

EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;

                                                                 ПЛАН ВЫПОЛНЕНИЯ ЗАПРОСА
-------------------------------------------------------------------​ -------------------------------------------------------------------​------
 Sort  (cost=713.05..713.30 rows=100 width=488) (actual time=2.995..3.002 rows=100 loops=1)
   Sort Key: t1.fivethous
   Sort Method: quicksort  Memory: 74kB
   ->  Узел соединения Hash Join  (cost=226.23..709.73 rows=100 width=488) (actual time=0.515..2.920 rows=100 loops=1)
         Hash Cond: (t2.unique2 = t1.unique2)
         ->  Seq Scan на tenk2 t2  (cost=0.00..445.00 rows=10000 width=244) (actual time=0.026..1.790 rows=10000 loops=1)
         ->  Hash  (cost=224.98..224.98 rows=100 width=244) (actual time=0.476..0.477 rows=100 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 35kB
               ->  Bitmap Heap Scan на tenk1 t1  (cost=5.06..224.98 rows=100 width=244) (actual time=0.030..0.450 rows=100 loops=1)
                     Recheck Cond: (unique1 < 100)
                     Heap Blocks: exact=90
                     ->  Bitmap Index Scan на tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.013..0.013 rows=100 loops=1)
                           Index Cond: (unique1 < 100)
 Planning time: 0.187 ms
 Execution time: 3.036 ms

Узел Sort показывает используемый метод сортировки (в частности, выполнялась ли сортировка в памяти или на диске) и объем требуемой памяти или дискового пространства. Узел Hash показывает количество корзин и пакетов хеша, а также пиковый объем памяти, использованный для хеш-таблицы. (Если количество пакетов превышает единицу, будет также задействовано дисковое пространство, но эти сведения не отображаются.)

Еще одним видом дополнительной информации является количество строк, исключенных фильтром:

EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7;

                                               QUERY PLAN
-------------------------------------------------------------------​ --------------------------------------
 Seq Scan on tenk1  (cost=0.00..470.00 rows=7000 width=244) (actual time=0.030..1.995 rows=7000 loops=1)
   Filter: (ten < 7)
   строки, исключенные фильтром: 3000
 Planning time: 0.102 ms
 Execution time: 2.145 ms

Данные показатели могут быть особенно полезны для условий фильтрации, применяемых в узлах соединения. «Rows Removed» выводится только в том случае, если условие фильтрации отклоняет хотя бы одну считанную строку или, в случае узла соединения, потенциальную пару соединения.

Ситуация, аналогичная применению условий фильтрации, возникает при «неточном» сканировании индекса. Например, рассмотрим поиск многоугольников, содержащих определенную точку:

EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';

                                              QUERY PLAN
-------------------------------------------------------------------​ -----------------------------------
 Seq Scan on polygon_tbl  (cost=0.00..1.09 rows=1 width=85) (actual time=0.023..0.023 rows=0 loops=1)
   Filter: (f1 @> '((0.5,2))'::polygon)
   строки, исключенные фильтром: 7
 Planning time: 0.039 ms
 Execution time: 0.033 ms

Планировщик считает (вполне обоснованно), что данная тестовая таблица слишком мала для использования сканирования индекса, поэтому выполняется обычное последовательное сканирование, при котором все строки были отсеяны условием фильтрации. Но если принудительно использовать сканирование индекса, мы увидим следующее:

SET enable_seqscan TO off;

EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';

                                                        QUERY PLAN
-------------------------------------------------------------------​ -------------------------------------------------------
 Index Scan using gpolygonind on polygon_tbl  (cost=0.13..8.15 rows=1 width=85) (actual time=0.074..0.074 rows=0 loops=1)
   Index Cond: (f1 @> '((0.5,2))'::polygon)
   Rows Removed by Index Recheck: 1
 Planning Time: 0.039 ms
 Execution Time: 0.098 ms

Здесь видно, что индекс вернул одну потенциальную строку, которая затем была отклонена при повторной проверке условия индекса. Это происходит вследствие того, что индекс GiST является «неточном» для проверок на вхождение полигонов: фактически он возвращает строки с полигонами, пересекающимися с целевым объектом, после чего для данных строк требуется выполнение точной проверки на вхождение.

EXPLAIN имеет BUFFERS параметр, используемый с ANALYZE для получения более детальной статистики времени выполнения:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;

                                                           ПЛАН ВЫПОЛНЕНИЯ ЗАПРОСА
-------------------------------------------------------------------​ --------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=25.07..60.11 rows=10 width=244) (actual time=0.105..0.114 rows=10 loops=1)
   Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
   Heap Blocks: exact=10
   Buffers: shared hit=14 read=3
   ->  BitmapAnd  (cost=25.07..25.07 rows=10 width=0) (actual time=0.100..0.101 rows=0 loops=1)
         Буферы: shared hit=4 read=3
         ->  Bitmap Index Scan по tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.027..0.027 rows=100 loops=1)
               Index Cond: (unique1 < 100)
               Буферы: shared hit=2
         ->  Bitmap Index Scan по tenk1_unique2  (cost=0.00..19.78 rows=999 width=0) (actual time=0.070..0.070 rows=999 loops=1)
               Index Cond: (unique2 > 9000)
               Буферы: shared hit=2 read=3
 Планирование:
   Буферы: shared hit=3
 Planning time: 0.162 ms
 Execution time: 0.143 ms

Показатели, предоставляемые BUFFERS помогают определить наиболее интенсивные с точки зрения ввода-вывода части запроса.

Следует учитывать, что поскольку EXPLAIN ANALYZE фактически выполняет запрос, любые побочные эффекты сохраняются, несмотря на то, что результаты выполнения запроса игнорируются ради вывода EXPLAIN данные. Для анализа запроса, изменяющего данные, без изменения таблиц можно выполнить откат команды после её завершения, например:

BEGIN;

EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;

                                                           ПЛАН ВЫПОЛНЕНИЯ ЗАПРОСА
-------------------------------------------------------------------​ -------------------------------------------------------------
 Операция UPDATE для tenk1  (cost=5.06..225.23 rows=0 width=0) (actual time=1.634..1.635 rows=0 loops=1)
   ->  Bitmap Heap Scan для tenk1  (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100 loops=1)
         Recheck Cond: (unique1 < 100)
         Heap Blocks: exact=90
         ->  Сканирование индекса (Bitmap Index Scan) для tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100 loops=1)
               Index Cond: (unique1 < 100)
 Planning time: 0.151 ms
 Execution time: 1.856 ms

ROLLBACK;

Как видно из данного примера, когда запрос является INSERT, UPDATE, DELETEили MERGE , фактические действия по внесению изменений в таблицу выполняются узлом плана верхнего уровня (Insert, Update, Delete или Merge). Дочерние узлы плана выполняют поиск старых строк и/или вычисление новых данных. Выше представлен аналогичный рассмотренному ранее тип сканирования таблицы по битовой карте, результаты которого передаются узлу Update, осуществляющему сохранение обновленных строк. Следует отметить, что хотя выполнение узла изменения данных может занимать значительное время (в данном примере — основную часть времени), планировщик в настоящее время не включает эти затраты в оценки стоимости. Это обусловлено тем, что объем данной работы идентичен для любого корректного плана выполнения запроса и не влияет на процесс принятия решений планировщиком.

Когда UPDATE, DELETEили MERGE команда затрагивает секционированную таблицу или иерархию наследования, результат может иметь следующий вид:

EXPLAIN UPDATE gtest_parent SET f1 = CURRENT_DATE WHERE f2 = 101;

                                       ПЛАН ВЫПОЛНЕНИЯ ЗАПРОСА
-------------------------------------------------------------------​ ---------------------
 Update on gtest_parent  (cost=0.00..3.06 rows=0 width=0)
   Update on gtest_child gtest_parent_1
   Update on gtest_child2 gtest_parent_2
   Update on gtest_child3 gtest_parent_3
   ->  Append  (cost=0.00..3.06 rows=3 width=14)
         ->  Seq Scan on gtest_child gtest_parent_1  (cost=0.00..1.01 rows=1 width=14)
               Filter: (f2 = 101)
         ->  Seq Scan on gtest_child2 gtest_parent_2  (cost=0.00..1.01 rows=1 width=14)
               Filter: (f2 = 101)
         ->  Seq Scan on gtest_child3 gtest_parent_3  (cost=0.00..1.01 rows=1 width=14)
               Filter: (f2 = 101)

В данном примере узлу Операция UPDATE необходимо обработать три дочерние таблицы, но не упомянутую изначально секционированную таблицу (так как в ней никогда не хранятся данные). Таким образом, формируется три входных подплана сканирования, по одному для каждой таблицы. Для ясности узел операции UPDATE аннотирован с указанием конкретных целевых таблиц, подлежащих обновлению, в том же порядке, что и соответствующие подпланы.

Значение Время планирования отображаемое в Команда EXPLAIN ANALYZE — это время, затраченное на формирование плана выполнения запроса на основе разобранного запроса и его оптимизацию. Оно не включает время синтаксического анализа и переписывания.

Значение Время выполнения отображаемое в Команда EXPLAIN ANALYZE включает время запуска и завершения процесса выполнения, а также время работы всех вызванных триггеров, но не включает время синтаксического анализа, переписывания или время планирования. Время, затраченное на выполнение BEFORE триггеров (при их наличии) включается в время выполнения соответствующего узла Команда INSERT, Операция UPDATE или Delete; но время, затраченное на выполнение AFTER триггеров в нем не учитывается, поскольку AFTER триггеры срабатывают после завершения выполнения всего плана. Общее время, затраченное на каждый триггер (как BEFORE так и AFTER) также отображается отдельно. Обратите внимание, что триггеры отложенных ограничений не будут выполнены до завершения транзакции и, следовательно, вообще не учитываются EXPLAIN ANALYZE.

Время, указанное для узла верхнего уровня, не включает время, необходимое для преобразования выходных данных запроса в формат отображения или для их отправки клиенту. Хотя EXPLAIN ANALYZE никогда не отправляет данные клиенту, с помощью нее можно выполнить преобразование выходных данных запроса в формат отображения и измерить необходимое для этого время, указав параметр SERIALIZE option. Это время будет показано отдельно; оно также включено в общее Время выполнения.

Существует два важных аспекта, из-за которых время выполнения, измеренное командой EXPLAIN ANALYZE может отличаться от времени фактического выполнения того же запроса. Во-первых, поскольку выходные строки не передаются клиенту, затраты на сетевую передачу данных не учитываются. Затраты на преобразование данных ввода-вывода также не учитываются, если не SERIALIZE задан соответствующий параметр. Во-вторых, накладные расходы на измерения, добавляемые командой Команда EXPLAIN ANALYZE могут быть значительными, особенно на системах с медленными gettimeofday() системными вызовами. Вы можете использовать инструмент pg_test_timing инструмент для измерения накладных расходов на замер времени в вашей системе.

EXPLAIN полученные результаты не следует экстраполировать на ситуации, существенно отличающиеся от условий тестирования; например, нельзя полагать, что результаты, полученные на демонстрационной таблице, применимы к большим таблицам. Стоимостные оценки планировщика не являются линейными, поэтому он может выбрать иной план для таблицы большего или меньшего размера. В качестве примера можно привести ситуацию, когда для таблицы, занимающей всего одну страницу диска, практически всегда будет выбираться последовательное сканирование, независимо от наличия индексов. Планировщик учитывает, что для обработки таблицы в любом случае потребуется чтение одной страницы с диска, поэтому выполнение дополнительных операций чтения для обращения к индексу нецелесообразно. (Подобная ситуация наблюдалась в polygon_tbl приведенном выше примере.)

Существуют случаи, когда фактические и оценочные значения значительно расходятся, однако это не указывает на наличие ошибки. Один из таких случаев возникает, когда выполнение узла плана прерывается LIMIT или аналогичным действием. Например, в LIMIT команде SELECT, использованной ранее,

EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;

                                                          ПЛАН ВЫПОЛНЕНИЯ ЗАПРОСА
-------------------------------------------------------------------​ ------------------------------------------------------------
 Limit  (cost=0.29..14.33 rows=2 width=244) (actual time=0.051..0.071 rows=2 loops=1)
   ->  Index Scan using tenk1_unique2 on tenk1  (cost=0.29..70.50 rows=10 width=244) (actual time=0.051..0.070 rows=2 loops=1)
         Index Cond: (unique2 > 9000)
         Filter: (unique1 < 100)
         строки, исключенные фильтром: 287
 Planning time: 0.077 ms
 Execution time: 0.086 ms

расчетная стоимость и количество строк для узла «Сканирование индекса» отображаются так, как если бы сканирование было выполнено до конца. Но в действительности оператор LIMIT прекратил запрашивать строки после получения двух, поэтому фактическое количество строк равно 2, а время выполнения меньше, чем предполагает предварительная оценка стоимости. Это не является ошибкой планировщика, а представляет собой лишь расхождение в способе отображения оценочных и реальных значений.

Соединения слиянием также имеют артефакты измерения, которые могут ввести в заблуждение. Соединение слиянием прекращает чтение одного входного набора данных, если исчерпан другой и следующее значение ключа в первом наборе больше последнего значения ключа в другом; в таком случае дальнейшие совпадения невозможны, и сканирование остальной части первого набора данных не требуется. Это приводит к тому, что считывается не вся одна дочерняя таблица, с результатами, подобными упомянутым для LIMIT. Кроме того, если внешний (первый) дочерний узел содержит строки с дублирующимися значениями ключа, внутренний (второй) дочерний узел буферизуется и сканируется повторно в части тех строк, которые соответствуют данному значению ключа. EXPLAIN ANALYZE учитывает данные повторные выдачи одних и тех же внутренних строк как дополнительные строки. При наличии большого количества дубликатов во внешнем отношении сообщаемое фактическое количество строк для дочернего узла внутреннего плана может значительно превышать количество строк, фактически содержащихся во внутреннем отношении.

В связи с ограничениями реализации узлы BitmapAnd и BitmapOr всегда выводят нулевое значение фактического количества строк.

Обычно EXPLAIN выводит каждый узел плана, созданный планировщиком. Однако существуют случаи, когда на основе значений параметров, недоступных во время планирования, исполнитель может определить, что определенные узлы не требуют выполнения, так как они не могут вернуть ни одной строки. (В настоящее время это возможно только для дочерних узлов узла Append или MergeAppend при сканировании секционированной таблицы.) В таких случаях данные узлы плана исключаются из EXPLAIN вывода, и Subplans Removed: N вместо этого появляется аннотация.