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

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

EXPLAIN SELECT * FROM tenk1;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..458.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..458.00 rows=10000 width=244)

Эти числа вычисляются очень просто. Если вы выполните:

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

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

Теперь давайте изменить запрос, чтобы добавить условие WHERE:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..483.00 rows=7001 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.07..229.20 rows=101 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 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..229.43 rows=1 width=244)
   Recheck Cond: (unique1 < 100)
   Filter: (stringu1 = 'xxx'::name)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 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 four, ten LIMIT 100;
                                              QUERY PLAN
-------------------------------------------------------------------​-----------------------------------
 Limit  (cost=521.06..538.05 rows=100 width=244)
   ->  Incremental Sort  (cost=521.06..2220.95 rows=10000 width=244)
         Sort Key: four, ten
         Presorted Key: four
         ->  Index Scan using index_tenk1_on_four on tenk1  (cost=0.29..1510.08 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.08..60.21 rows=10 width=244)
   Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
   ->  BitmapAnd  (cost=25.08..25.08 rows=10 width=0)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 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.48 rows=2 width=244)
   ->  Index Scan using tenk1_unique2 on tenk1  (cost=0.29..71.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.62 rows=10 width=488)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 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.91 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.91, в данном случае), плюс немного времени ЦП для обработки соединения.

В этом примере количество строк в результате соединения равно произведению количества строк в двух сканированиях, но это не всегда верно, потому что могут быть дополнительные предложения 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.46 rows=33 width=488)
   Join Filter: (t1.hundred < t2.hundred)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 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=230.47..713.98 rows=101 width=488)
   Hash Cond: (t2.unique2 = t1.unique2)
   ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244)
   ->  Hash  (cost=229.20..229.20 rows=101 width=244)
         ->  Bitmap Heap Scan on tenk1 t1  (cost=5.07..229.20 rows=101 width=244)
               Recheck Cond: (unique1 < 100)
               ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 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=198.11..268.19 rows=10 width=488)
   Merge Cond: (t1.unique2 = t2.unique2)
   ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..656.28 rows=101 width=244)
         Filter: (unique1 < 100)
   ->  Sort  (cost=197.83..200.33 rows=1000 width=244)
         Sort Key: t2.unique2
         ->  Seq Scan on onek t2  (cost=0.00..148.00 rows=1000 width=244)

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

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

SET enable_sort = off;

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

                                        QUERY PLAN
-------------------------------------------------------------------​-----------------------
 Merge Join  (cost=0.56..292.65 rows=10 width=488)
   Merge Cond: (t1.unique2 = t2.unique2)
   ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..656.28 rows=101 width=244)
         Filter: (unique1 < 100)
   ->  Index Scan using onek_unique2 on onek t2  (cost=0.28..224.79 rows=1000 width=244)

что показывает, что планировщик считает, что сортировка onek с использованием индексного сканирования примерно на 12% дороже, чем последовательное сканирование и сортировка. Конечно, следующий вопрос заключается в том, правильно ли это. можно исследовать это с помощью EXPLAIN ANALYZE, как обсуждалось ниже.

Возможно проверить точность оценок планировщика, используя опцию 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.62 rows=10 width=488) (actual time=0.128..0.377 rows=10 loops=1)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244) (actual time=0.057..0.121 rows=10 loops=1)
         Recheck Cond: (unique1 < 10)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0) (actual time=0.024..0.024 rows=10 loops=1)
               Index Cond: (unique1 < 10)
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.91 rows=1 width=244) (actual time=0.021..0.022 rows=1 loops=10)
         Index Cond: (unique2 = t1.unique2)
 Planning time: 0.181 ms
 Execution time: 0.501 ms

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

В некоторых планах запросов возможно выполнение узла подзапроса более одного раза. Например, внутреннее сканирование индекса будет выполняться один раз для каждой внешней строки в указанном выше плане с вложенными циклами. В таких случаях значение loops указывает общее количество выполнений узла, а показанные фактическое время и количество строк являются средними значениями для каждого выполнения. Это сделано для сравнения чисел с показанными оценками стоимости. Умножьте значение loops на время, затраченное на выполнение узла. В приведенном выше примере мы потратили общее время в 0,220 миллисекунд на выполнение сканирования индексов на 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=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)
   Sort Key: t1.fivethous
   Sort Method: quicksort  Memory: 77kB
   ->  Hash Join  (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 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.007..2.583 rows=10000 loops=1)
         ->  Hash  (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 28kB
               ->  Bitmap Heap Scan on tenk1 t1  (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)
                     Recheck Cond: (unique1 < 100)
                     ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)
                           Index Cond: (unique1 < 100)
 Planning time: 0.194 ms
 Execution time: 8.008 ms

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

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

EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7;

                                               QUERY PLAN
-------------------------------------------------------------------​--------------------------------------
 Seq Scan on tenk1  (cost=0.00..483.00 rows=7000 width=244) (actual time=0.016..5.107 rows=7000 loops=1)
   Filter: (ten < 7)
   Rows Removed by Filter: 3000
 Planning time: 0.083 ms
 Execution time: 5.905 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.05 rows=1 width=32) (actual time=0.044..0.044 rows=0 loops=1)
   Filter: (f1 @> '((0.5,2))'::polygon)
   Rows Removed by Filter: 4
 Planning time: 0.040 ms
 Execution time: 0.083 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=32) (actual time=0.062..0.062 rows=0 loops=1)
   Index Cond: (f1 @> '((0.5,2))'::polygon)
   Rows Removed by Index Recheck: 1
 Planning time: 0.034 ms
 Execution time: 0.144 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.08..60.21 rows=10 width=244) (actual time=0.323..0.342 rows=10 loops=1)
   Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
   Buffers: shared hit=15
   ->  BitmapAnd  (cost=25.08..25.08 rows=10 width=0) (actual time=0.309..0.309 rows=0 loops=1)
         Buffers: shared hit=7
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0) (actual time=0.043..0.043 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.227..0.227 rows=999 loops=1)
               Index Cond: (unique2 > 9000)
               Buffers: shared hit=5
 Planning time: 0.088 ms
 Execution time: 0.423 ms

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

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

BEGIN;

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

                                                           QUERY PLAN
-------------------------------------------------------------------​-------------------------------------------------------------
 Update on tenk1  (cost=5.08..230.08 rows=0 width=0) (actual time=3.791..3.792 rows=0 loops=1)
   ->  Bitmap Heap Scan on tenk1  (cost=5.08..230.08 rows=102 width=10) (actual time=0.069..0.513 rows=100 loops=1)
         Recheck Cond: (unique1 < 100)
         Heap Blocks: exact=90
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.05 rows=102 width=0) (actual time=0.036..0.037 rows=300 loops=1)
               Index Cond: (unique1 < 100)
 Planning Time: 0.113 ms
 Execution Time: 3.850 ms

ROLLBACK;

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

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

EXPLAIN UPDATE parent SET f2 = f2 + 1 WHERE f1 = 101;
                                              QUERY PLAN
-------------------------------------------------------------------​-----------------------------------
 Update on parent  (cost=0.00..24.59 rows=0 width=0)
   Update on parent parent_1
   Update on child1 parent_2
   Update on child2 parent_3
   Update on child3 parent_4
   ->  Result  (cost=0.00..24.59 rows=4 width=14)
         ->  Append  (cost=0.00..24.54 rows=4 width=14)
               ->  Seq Scan on parent parent_1  (cost=0.00..0.00 rows=1 width=14)
                     Filter: (f1 = 101)
               ->  Index Scan using child1_pkey on child1 parent_2  (cost=0.15..8.17 rows=1 width=14)
                     Index Cond: (f1 = 101)
               ->  Index Scan using child2_pkey on child2 parent_3  (cost=0.15..8.17 rows=1 width=14)
                     Index Cond: (f1 = 101)
               ->  Index Scan using child3_pkey on child3 parent_4  (cost=0.15..8.17 rows=1 width=14)
                     Index Cond: (f1 = 101)

В этом примере узел Update должен учитывать три дочерние таблицы, а также изначально упомянутую родительскую таблицу. Таким образом, имеется четыре подзапроса для сканирования входных данных, по одному на каждую таблицу. Для ясности, узел Update аннотирован для показа конкретных целевых таблиц, которые будут обновлены, в том же порядке, что и соответствующие подзапросы.

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

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

Существует два значительных способа, которыми время выполнения, измеренное с помощью EXPLAIN ANALYZE, может отличаться от нормального выполнения того же запроса. Во-первых, поскольку клиенту не передаются выходные строки, в стоимость передачи по сети и преобразования ввода-вывода не включаются. Во-вторых, дополнительная нагрузка на измерения, добавленные 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.71 rows=2 width=244) (actual time=0.177..0.249 rows=2 loops=1)
   ->  Index Scan using tenk1_unique2 on tenk1  (cost=0.29..72.42 rows=10 width=244) (actual time=0.174..0.244 rows=2 loops=1)
         Index Cond: (unique2 > 9000)
         Filter: (unique1 < 100)
         Rows Removed by Filter: 287
 Planning time: 0.096 ms
 Execution time: 0.336 ms

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

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

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

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