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

Вот тривиальный пример, чтобы показать, как выглядит вывод:

EXPLAIN SELECT * FROM tenk1;

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

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

Все затраты измеряются в произвольных единицах, определяемых параметрами стоимости планировщика (см. Раздел 19.7.2). Традиционная практика заключается в измерении затрат в единицах чтения дисковых страниц; то есть, параметр seq_page_cost обычно устанавливается в 1.0, а остальные параметры стоимости устанавливаются относительно этого значения. Примеры в этом разделе выполняются с использованием параметров стоимости по умолчанию.

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

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

Вернемся к нашему примеру:

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, чтобы быть точным), чтобы отразить дополнительное время ЦП, затраченное на проверку условия WHERE.

Фактическое количество строк, которые выберет этот запрос, составляет 7000, но оценка rows является лишь приблизительной. Если вы попытаетесь повторить этот эксперимент, вы, возможно, получите немного другую оценку; более того, она может измениться после каждой команды 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. Вместо этого оно применяется как фильтр на строках, извлеченных с использованием индекса. Таким образом, стоимость фактически немного увеличилась, чтобы отразить эту дополнительную проверку.

В некоторых случаях планировщик предпочтет “простой” план сканирования индекса:

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:

EXPLAIN SELECT * FROM tenk1 ORDER BY unique1;

                            QUERY PLAN
-------------------------------------------------------------------
 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)

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

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
         Presorted Key: hundred
         ->  Index Scan using tenk1_hundred on 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, чтобы не все строки нужно было извлекать, и планировщик изменил свое решение о том, что делать. Обратите внимание, что общая стоимость и количество строк узла Index Scan показаны так, как если бы он был выполнен полностью. Однако узел Limit остановиться после извлечения только пятой части этих строк, поэтому его общая стоимость составляет только пятую часть от этого, и это фактическая оценочная стоимость запроса. Этот план предпочтительнее, чем добавление узла Limit к предыдущему плану, потому что Limit не мог избежать платы за стартовую стоимость сканирования по битовой карте, поэтому общая стоимость с этим подходом составила бы более 25 единиц.

Давайте попробуем объединить две таблицы, используя столбцы, о которых мы говорили:

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

                                      QUERY PLAN
-------------------------------------------------------------------​-------------------
 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 в этом узле. Условие t1.unique2 = t2.unique2 пока не имеет значения, поэтому оно не влияет на количество строк внешнего сканирования. Узел соединения с вложенными циклами будет запускать своего второго, или “внутреннего” дочернего элемента один раз для каждой строки, полученной от внешнего дочернего элемента. Значения столбцов из текущей внешней строки могут быть подставлены во внутреннее сканирование; здесь значение t1.unique2 из внешней строки доступно, поэтому мы получаем план и затраты, аналогичные тем, которые мы видели выше для простого случая SELECT ... WHERE t2.unique2 = constant. (Оценочная стоимость на самом деле немного ниже, чем та, что была видна выше, в результате кэширования, которое ожидается во время повторяющихся сканирований индекса на 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 будет выполнено только один раз, даже если узел соединения вложенным циклом должен прочитать эти данные десять раз, один раз для каждой строки из внешнего отношения. Узел 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;

                                        QUERY PLAN
-------------------------------------------------------------------​-----------------------
 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)

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

Еще один возможный тип соединения - это соединение слиянием (merge join), приведенное здесь:

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

                                        QUERY PLAN
-------------------------------------------------------------------​-----------------------
 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)

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

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

SET enable_mergejoin = off;

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

                                        QUERY PLAN
-------------------------------------------------------------------​-----------------------
 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)

что показывает, что планировщик считает, что хеш-соединение будет почти на 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);

                               QUERY PLAN
-------------------------------------------------------------------​------
 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 выполняет подзапрос заново для каждой строки внешнего запроса (что объясняет высокую оценочную стоимость). Некоторые запросы могут использовать хешированный подзапрос, чтобы этого избежать:

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. Это требует, чтобы под-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)
           Output: ((random() * '10'::double precision))::integer

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

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

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

                                                           QUERY PLAN
-------------------------------------------------------------------​--------------------------------------------------------------
 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

Обратите внимание, что значения “фактического времени” указаны в миллисекундах реального времени, в то время как оценка стоимости cost выражены в произвольных единицах; поэтому они могут не совпадать. Самое важное, на что обычно следует обратить внимание, - это то, насколько оценки количества строк близки к реальности. В этом примере все оценки были точными, но на практике это весьма необычно.

В некоторых планах запросов возможно, что узел подзапроса будет выполнен более одного раза. Например, внутреннее индексное сканирование будет выполняться один раз на каждую внешнюю строку в приведенном выше плане вложенных циклов. В таких случаях значение loops сообщает общее количество выполнений узла, а фактические значения времени и строк показаны как средние за выполнение. Это сделано для того, чтобы числа были сопоставимы с тем, как показаны оценки стоимости. Умножьте на значение loops, чтобы получить общее время, фактически затраченное на узел. В приведенном выше примере мы потратили в общей сложности 0.030 миллисекунд на выполнение индексных сканирований на 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;

                                                                 QUERY PLAN
-------------------------------------------------------------------​-------------------------------------------------------------------​------
 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 on 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 on 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 on 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)
   Rows Removed by Filter: 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)
   Rows Removed by Filter: 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;

                                                           QUERY PLAN
-------------------------------------------------------------------​--------------------------------------------------------------
 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)
         Buffers: shared hit=4 read=3
         ->  Bitmap Index Scan on 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)
               Buffers: shared hit=2
         ->  Bitmap Index Scan on 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)
               Buffers: shared hit=2 read=3
 Planning:
   Buffers: 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;

                                                           QUERY PLAN
-------------------------------------------------------------------​-------------------------------------------------------------
 Update on tenk1  (cost=5.06..225.23 rows=0 width=0) (actual time=1.634..1.635 rows=0 loops=1)
   ->  Bitmap Heap Scan on 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 on 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;

                                       QUERY PLAN
-------------------------------------------------------------------​---------------------
 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 аннотирован, чтобы показать конкретные целевые таблицы, которые будут обновлены, в том же порядке, что и соответствующие подзапланы.

Planning time, отображаемое командой EXPLAIN ANALYZE, представляет собой время, затраченное на генерацию плана запроса из разобранного и оптимизированного запроса. Оно не включает разбор или переписывание.

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

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

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

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

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

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

                                                          QUERY PLAN
-------------------------------------------------------------------​------------------------------------------------------------
 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)
         Rows Removed by Filter: 287
 Planning Time: 0.077 ms
 Execution Time: 0.086 ms

Оценочная стоимость и количество строк для узла Index Scan отображаются так, как если бы он был выполнен до конца. Но на самом деле узел Limit остановил запрос строк после получения двух, поэтому фактическое количество строк составляет только 2, а время выполнения меньше, чем предполагает оценка стоимости. Это не ошибка оценки, а только расхождение в способе отображения оценок и фактических значений.

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

Узлы BitmapAnd и BitmapOr всегда сообщают о нулевом количестве строк в результате из-за ограничений реализации.

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