Советы по производительности

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

Создание демонстрационных таблиц

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

CREATE TABLE tenk1 (
	unique1		int4,
	unique2		int4,
	two			int4,
	four		int4,
	ten			int4,
	twenty		int4,
	hundred		int4,
	thousand	int4,
	twothousand	int4,
	fivethous	int4,
	tenthous	int4,
	odd			int4,
	even		int4,
	stringu1	name,
	stringu2	name,
	string4		name
);

CREATE TABLE tenk2 (
	unique1 	int4,
	unique2 	int4,
	two 	 	int4,
	four 		int4,
	ten			int4,
	twenty 		int4,
	hundred 	int4,
	thousand 	int4,
	twothousand int4,
	fivethous 	int4,
	tenthous	int4,
	odd			int4,
	even		int4,
	stringu1	name,
	stringu2	name,
	string4		name
);

CREATE TABLE onek (
	unique1		int4,
	unique2		int4,
	two			int4,
	four		int4,
	ten			int4,
	twenty		int4,
	hundred		int4,
	thousand	int4,
	twothousand	int4,
	fivethous	int4,
	tenthous	int4,
	odd			int4,
	even		int4,
	stringu1	name,
	stringu2	name,
	string4		name
);

Для заполнения используйте SQL команды из приложения Данные для анализа производительности

ВНИМАНИЕ!!!
Приведённый выше файл, содержащий демонстрационные данные для анализа производительности, довольно большого размера и открытие его для просмотра и попытка выполнить, находящиесяв нём команды при помощи графических утилит может занять значительное время и закончится ошибкой поэтому рекомендуется скачать файл на диск и использовать утилиту qsql для загрузки данных из интерфейса командной строки.

После загрузки данных необходимо выполнить заполнение таблицы и создание необходимых индексов при помощи команд :

INSERT INTO tenk2 SELECT * FROM tenk1;

CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops);

CREATE INDEX tenk1_unique2 ON tenk1 USING btree(unique2 int4_ops);

CREATE INDEX tenk1_hundred ON tenk1 USING btree(hundred int4_ops);

CREATE INDEX tenk1_thous_tenthous ON tenk1 (thousand, tenthous);

CREATE INDEX tenk2_unique1 ON tenk2 USING btree(unique1 int4_ops);

CREATE INDEX tenk2_unique2 ON tenk2 USING btree(unique2 int4_ops);

CREATE INDEX tenk2_hundred ON tenk2 USING btree(hundred int4_ops);

CREATE INDEX onek_unique1 ON onek USING btree(unique1 int4_ops);

CREATE INDEX IF NOT EXISTS onek_unique1 ON onek USING btree(unique1 int4_ops);

CREATE INDEX IF NOT EXISTS ON onek USING btree(unique1 int4_ops);

CREATE INDEX onek_unique2 ON onek USING btree(unique2 int4_ops);

CREATE INDEX onek_hundred ON onek USING btree(hundred int4_ops);

CREATE INDEX onek_stringu1 ON onek USING btree(stringu1 name_ops);

Теперь необходим провести очистку таблиц при помощи комманд :

vacuum analyze tenk1;

vacuum analyze tenk2;

vacuum analyze onek;

Таблицы созданы и заполнены

Выполняя примеры запросов приведённые ниже в этом разделе вы сможете получить аналогичные результаты, но ваши предполагаемые оценки затрат (COST) и количества строк могут незначительно отличаться, потому что статистика ANALYZE является случайной выборкой, а не точной, и поскольку оценки затрат по своей природе в некоторой степени зависят от платформы.

Использование EXPLAIN

QHB строит план запросов для каждого запроса, который получен от пользователя. Выбор правильного плана, соответствующего структуре запроса и свойствам данных, абсолютно необходим для хорошей производительности, поэтому в систему входит комплексный планировщик, который пытается выбрать хорошие планы. Вы можете использовать команду EXPLAIN, чтобы увидеть, какой план запроса создает планировщик для любого запроса. Чтение планов - это искусство, которое требует некоторого опыта, чтобы овладеть им, и в этом разделе описаны только основы.

В примерах используется стандартный текстовый формат EXPLAIN, который компактен и удобен для чтения человеком. Если вы хотите передать выходные данные EXPLAIN в программу для дальнейшего анализа, вы должны использовать вместо этого один из машиночитаемых форматов вывода (XML, JSON или YAML).

EXPLAIN Основы

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

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

EXPLAIN SELECT * FROM tenk1;

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

Поскольку в этом запросе нет WHERE, необходимо просматривать все строки таблицы, поэтому планировщик решил использовать простой план последовательного сканирования (Seq Scan). Числа после (слева направо):

  • cost=0.00..445.00 rows=10000 width=244
    Ориентировочная стоимость запуска. Это время, затрачиваемое до начала фазы вывода, например, время на выполнение сортировки в узле сортировки.

  • cost=0.00..445.00 rows=10000 width=244
    Ориентировочная общая стоимость. Предполагается, что узел плана выполнен до конца, то есть извлечены все доступные строки. На практике родительский узел может прервать чтение всех доступных строк (см. Пример LIMIT ниже).

  • cost=0.00..445.00 rows=10000 width=244
    Расчетное количество строк, выводимых этим узлом плана. Опять же, предполагается, что узел полностью выполнился.

  • cost=0.00..445.00 rows=10000 width=244
    Расчетная средняя "ширина" строк, выводимых этим узлом плана (в байтах).

Затраты измеряются в произвольных единицах, определяемых параметрами затрат планировщика (см. раздел Константы стоимости планировщика). Как правило затрат выборки чтение страниц с диска то есть параметр 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 строк. Ориентировочная стоимость рассчитывается как (disk pages read * seq_page_cost ) + (rows scanned * cpu_tuple_cost ). По умолчанию seq_page_cost равно 1,0, а cpu_tuple_cost равно 0,01, поэтому предполагаемая стоимость составляет (445 * 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)
(2 rows)

Обратите внимание, что вывод EXPLAIN показывает WHERE, применяемое в качестве условия Filter, присоединённого к узлу плана 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)
(4 rows)

Здесь планировщик решил использовать двухэтапный план: дочерний узел плана просматривает индекс, чтобы найти местоположене строк в страницах таблицы, соответствующих условию индекса, а затем родительский узел плана фактически выбирает эти строки из самой таблицы. Извлечение строк по отдельности намного дороже, чем их последовательное чтение, но поскольку не все страницы таблицы нужно считывать, это все же дешевле, чем последовательное сканирование. (Причина использования двух уровней плана состоит в том, что узел верхнего плана сортирует местоположения строк, идентифицированные индексом, в некторый физический порядок перед их чтением, чтобы минимизировать стоимость отдельных выборок. Bitmap Index Scan, упомянутый в имени узлоа, - это и есть механизм, который выполняет сортировку).

Теперь давайте добавим еще одно условие к 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)
(5 rows)

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

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

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)
(2 rows)

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

Если в нескольких столбцах, на которые есть ссылки в 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)
(7 rows)

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

Вот пример, показывающий влияние 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)
(4 rows)

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

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

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)
(7 rows)

В этом плане у нас есть узел соединения с вложенным циклом (Nested Loop) с двумя дочерними сканированиями таблиц (tenk1 t1 и tenk2 t2) в качестве входных. Отступ строки сводки узла отражает структуру дерева плана. Первый, или «внешний», дочерний объект объединения - это Bitmap Index Scan, похожее на то, что мы видели ранее. Его стоимость и количество строк такие же, как мы получили бы из SELECT ... WHERE unique1 < 10 потому что мы применяем WHERE unique1 <> 10 для этого узла. Предложение t1.unique2 = t2.unique2 пока не актуально, поэтому оно не влияет на количество строк внешнего сканирования. Узел соединения с вложенным циклом (Nested Loop) будет запускать свой второй или «внутренний» дочерний элемент один раз для каждой строки, полученной из внешнего дочернего элемента. Значения столбцов из текущего внешней строки могут быть включены во внутреннее сканирование; здесь доступно значение 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)
(9 rows)

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

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

При работе с внешними объединениями вы можете увидеть узлы плана соединения с присоединенными фильтром объединения Join Filter . Условия фильтра объединения взяты из предложения ON внешнего соединения, поэтому строка, которая не соответствует условию фильтра соединения, все равно может быть выдана как строка с нулевым расширением.

Если мы немного изменим избирательность запроса, мы можем получить совсем другой план соединения:

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)
(8 rows)

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

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

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

                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Merge Join  (cost=95.11..164.54 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)
   ->  Sort  (cost=94.83..97.33 rows=1000 width=244)
         Sort Key: t2.unique2
         ->  Seq Scan on onek t2  (cost=0.00..45.00 rows=1000 width=244)
(7 rows)

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

Один из способов посмотреть на варианты планов - заставить планировщика игнорировать любую стратегию, которая, по его мнению, была самой дешевой, используя флаги включения / выключения, описанные в разделе Конфигурация метода планирования. (Это грубый, но полезный инструмент см. также раздел Управление планировщиком с помощью явных предложений JOIN). Например, если мы не уверены, что последовательное сканирование и сортировка - лучший способ справиться с таблицей onek в предыдущем примере, мы можем попробовать

SET enable_sort = off;

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

                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Hash Join  (cost=226.23..275.08 rows=10 width=488)
   Hash Cond: (t2.unique2 = t1.unique2)
   ->  Seq Scan on onek t2  (cost=0.00..45.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)
(8 rows)


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

EXPLAIN ANALYZE

Можно проверить точность оценок планировщика, используя опцию EXPLAIN ’s ANALYZE. С помощью этой опции 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.108..0.406 rows=10 loops=1)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244) (actual time=0.061..0.135 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.038..0.038 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.022..0.023 rows=1 loops=10)
         Index Cond: (unique2 = t1.unique2)
 Planning Time: 0.953 ms
 Execution Time: 0.517 ms
(10 rows)

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

В некоторых планах запросов узел подплана может выполняться более одного раза. Например, сканирование внутреннего индекса будет выполняться один раз для каждой внешней строки в приведенном выше плане вложенного цикла. В таких случаях значение цикла сообщает об общем количестве выполнений узла, а показанные фактические значения времени и строк представляют собой средние значения для каждого выполнения. Это сделано для того, чтобы сделать числа сопоставимыми с тем, как отображаются оценки затрат. Умножьте на значение цикла, чтобы получить общее время, фактически потраченное на узел. В приведенном выше примере мы потратили 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=713.05..713.30 rows=100 width=488) (actual time=4.184..4.195 rows=100 loops=1)
   Sort Key: t1.fivethous
   Sort Method: quicksort  Memory: 77kB
   ->  Hash Join  (cost=226.23..709.73 rows=100 width=488) (actual time=0.387..4.019 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.011..1.688 rows=10000 loops=1)
         ->  Hash  (cost=224.98..224.98 rows=100 width=244) (actual time=0.350..0.350 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.039..0.300 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.022..0.022 rows=100 loops=1)
                           Index Cond: (unique1 < 100)
 Planning Time: 1.542 ms
 Execution Time: 4.327 ms
(15 rows)

Узел 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.038..42.547 rows=7000 loops=1)
   Filter: (ten < 7)
   Rows Removed by Filter: 3000
 Planning Time: 0.176 ms
 Execution Time: 43.762 ms
(5 rows)

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

У 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.964..1.033 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.942..0.943 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.056..0.056 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.863..0.863 rows=999 loops=1)
               Index Cond: (unique2 > 9000)
               Buffers: shared hit=2 read=3
 Planning Time: 0.400 ms
 Execution Time: 1.113 ms
(14 rows)

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

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

BEGIN;

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

                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Update on tenk1  (cost=5.07..229.46 rows=101 width=250) (actual time=14.628..14.628 rows=0 loops=1)
   ->  Bitmap Heap Scan on tenk1  (cost=5.07..229.46 rows=101 width=250) (actual time=0.101..0.439 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.043..0.043 rows=100 loops=1)
               Index Cond: (unique1 < 100)
 Planning time: 0.079 ms
 Execution time: 14.727 ms

ROLLBACK;

Как видно из этого примера, когда запрос представляет собой команду INSERT, UPDATE или DELETE, фактическая работа по изменению данных таблицы выполняется узлом плана INSERT, UPDATE или DELETE. Узлы плана под этим узлом выполняют поиск строк и / или вычисляют новые данные. Итак, выше мы видим тот же Bitmap Index Scan, который мы уже видели, и его выходные данные передаются в узел update, в котором хранятся обновленные строки. Стоит отметить, что хотя узел, модифицирующий данные, может выполнятся значительное время (в этом примере он занимает основную долю времени) выполнения, планировщик в настоящее время ничего не добавляет к оценкам затрат для учета этой работы. Это связано с тем, что выполняемая работа одинакова для каждого правильного плана запроса, поэтому она не влияет на решения по планированию.

Предостережения

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

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

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

EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..14.28 rows=2 width=244) (actual time=1.196..1.321 rows=2 loops=1)
   ->  Index Scan using tenk1_unique2 on tenk1  (cost=0.29..70.27 rows=10 width=244) (actual time=1.194..1.319 rows=2 loops=1)
         Index Cond: (unique2 > 9000)
         Filter: (unique1 < 100)
         Rows Removed by Filter: 287
 Planning Time: 7.903 ms
 Execution Time: 1.557 ms
(7 rows)

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

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

BitmapAnd и BitmapOr nвсегда сообщают, что их фактическое число строк равно нулю из-за ограничений реализации.

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

Статистика, используемая планировщиком

Статистика по одному столбцу

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

Одним из компонентов статистики - общее количество записей в каждой таблице и индексе, а также количество дисковых блоков, занимаемых каждой таблицей и индексом. Эта информация хранится в таблице pg_class, в столбцах reltuples и relpages. Мы можем посмотреть на это с помощью запросов, похожих на этот:

SELECT relname, relkind, reltuples, relpages
FROM pg_class
WHERE relname LIKE 'tenk1%';

       relname        | relkind | reltuples | relpages 
----------------------+---------+-----------+----------
 tenk1                | r       |     10000 |      345
 tenk1_hundred        | i       |     10000 |       30
 tenk1_thous_tenthous | i       |     10000 |       30
 tenk1_unique1        | i       |     10000 |       30
 tenk1_unique2        | i       |     10000 |       30
(5 rows)

Здесь мы можем видеть, что tenk1 содержит 10000 строк, как и его индексы, но индексы (что неудивительно) намного меньше таблицы.

По соображениям эффективности reltuples и relpages не обновляются на лету, и поэтому они обычно содержат несколько устаревших значений. Они обновляются с помощью VACUUM, ANALYZE и нескольких команд DDL, таких как CREATE INDEX. Операция VACUUM или ANALYZE, которая не сканирует всю таблицу (что обычно имеет место), будет постепенно обновлять число reltuples на основе части таблицы, которую она просканировала, что приведет к приблизительному значению. В любом случае планировщик будет масштабировать значения, которые он находит в pg_class чтобы соответствовать текущему размеру физической таблицы, таким образом получая более точное приближение.

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

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

Объем информации, хранимой в pg_statistic функцией ANALYZE, в частности максимальное количество записей в массивах most_common_vals и histogram_bounds для каждого столбца, можно установить для каждого столбца с помощью команды ALTER TABLE SET STATISTICS или глобально, установив переменной конфигурации default_statistics_target. Предел по умолчанию в настоящее время составляет 100 записей. Повышение лимита может позволить сделать более точные оценки планировщика, особенно для столбцов с нерегулярным распределением данных, за счет того, что они потребляют больше места в pg_statistic и немного больше времени для вычисления оценок. И наоборот, нижний предел может быть достаточным для столбцов с простым распределением данных.

Более подробную информацию об использовании статистики планировщиком можно найти в главе Как планировщик использует статистику.

Расширенная статистика

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

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

Объекты статистики создаются с помощью команды CREATE STATISTICS . Создание такого объекта просто создает запись каталога, обозначающую интерес к статистике. Сбор фактических данных выполняется ANALYZE (либо ручной командой, либо в фоне привыполнении автоматических процессов). Собранные значения можно просмотреть в каталоге pg_statistic_ext_data.

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

В следующих подразделах описываются виды расширенной статистики, которые в настоящее время поддерживаются.

Функциональные зависимости

Самый простой вид расширенной статистики отслеживает функциональные зависимости, понятие, используемое в определениях нормальных форм базы данных. Мы говорим, что столбец b функционально зависит от столбца a если знание значения a достаточно для определения значения b, то есть нет двух строк, имеющих одинаковое значение a но разные значения b. В полностью нормализованной базе данных функциональные зависимости должны существовать только от первичных ключей и суперключей. Однако на практике многие наборы данных не полностью нормализованы по разным причинам - преднамеренная денормализация по причинам производительности является распространенным примером. Даже в полностью нормализованной базе данных между некоторыми столбцами может быть частичная корреляция, которая может быть выражена как частичная функциональная зависимость.

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

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

Вот пример сбора статистики функциональной зависимости:

CREATE STATISTICS stts (dependencies) ON city, zip FROM zipcodes;

ANALYZE zipcodes;

SELECT stxname, stxkeys, stxddependencies
  FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
  WHERE stxname = 'stts';
 stxname | stxkeys |             stxddependencies             
---------+---------+------------------------------------------
 stts    | 1 5     | {"1 => 5": 1.000000, "5 => 1": 0.423130}
(1 row)

Здесь видно, что столбец 1 (почтовый индекс) полностью определяет столбец 5 (город), поэтому коэффициент равен 1,0, тогда как город определяет почтовый индекс только в 42% случаев, что означает, что существует много городов (58%), которые представлены более чем одним почтовым индексом.

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

Ограничения функциональных зависимостей

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

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

SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '94105';

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

SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '90210';

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

Во многих практических ситуациях это предположение обычно выполняется; например, в приложении может быть графический интерфейс, который позволяет выбирать только совместимые значения города и почтового индекса для использования в запросе. Но если это не так, функциональные зависимости не могут быть приемлемым вариантом.

Многовариантное N-мерное число уникальных значений

Статистика по одному столбцу хранит количество различных значений в каждом столбце. Оценки количества различных значений при объединении нескольких столбцов (например, для GROUP BY a, b ) часто неверны, когда у планировщика есть только статистические данные из одного столбца, что приводит к выбору плохих планов.

Чтобы улучшить такие оценки, ANALYZE может собирать n-различных статистических данных для групп столбцов. Как и прежде, это нецелесообразно делать для каждой возможной группировки столбцов, поэтому данные собираются только для тех групп столбцов, которые появляются вместе в объекте статистики, определенном с помощью опции ndistinct. Данные будут собираться для каждой возможной комбинации двух или более столбцов из набора перечисленных столбцов.

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

CREATE STATISTICS stts2 (ndistinct) ON city, state, zip FROM zipcodes;

ANALYZE zipcodes;

SELECT stxkeys AS k, stxdndistinct AS nd
  FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
  WHERE stxname = 'stts2';
-[ RECORD 1 ]--------------------------------------------------------
k  | 1 2 5
nd | {"1, 2": 33178, "1, 5": 33178, "2, 5": 27435, "1, 2, 5": 33178}
(1 row)

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

Целесообразно создавать ndistinct объекты статистики только по тем комбинациям столбцов, которые фактически используются для группировки и для которых неправильная оценка количества групп приводит к плохим планам. В противном случае расчёты статистики внутри ANALYZE будут напрасны.

Многомерные списки MCV

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

Чтобы улучшить такие оценки, ANALYZE может собирать списки MCV по комбинациям столбцов. Подобно функциональным зависимостям и n-мерным уникальным значениям, это нецелесообразно делать для каждой возможной группировки столбцов. Тем более для MCV, поскольку список MCV (в отличие от функциональных зависимостей и n-различных коэффициентов) хранит общие значения столбцов. Таким образом, данные собираются только для тех групп столбцов, которые появляются вместе в объекте статистики, определенном с опцией mcv .

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

CREATE STATISTICS stts3 (mcv) ON city, state FROM zipcodes;

ANALYZE zipcodes;

SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid),
                pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3';

 index |         values         | nulls | frequency | base_frequency
-------+------------------------+-------+-----------+----------------
     0 | {Washington, DC}       | {f,f} |  0.003467 |        2.7e-05
     1 | {Apo, AE}              | {f,f} |  0.003067 |        1.9e-05
     2 | {Houston, TX}          | {f,f} |  0.002167 |       0.000133
     3 | {El Paso, TX}          | {f,f} |     0.002 |       0.000113
     4 | {New York, NY}         | {f,f} |  0.001967 |       0.000114
     5 | {Atlanta, GA}          | {f,f} |  0.001633 |        3.3e-05
     6 | {Sacramento, CA}       | {f,f} |  0.001433 |        7.8e-05
     7 | {Miami, FL}            | {f,f} |    0.0014 |          6e-05
     8 | {Dallas, TX}           | {f,f} |  0.001367 |        8.8e-05
     9 | {Chicago, IL}          | {f,f} |  0.001333 |        5.1e-05
   ...
(99 rows)

Это указывает на то, что наиболее распространенной комбинацией города и штата является Вашингтон, округ Колумбия, с фактической частотой (в выборке) около 0,35%. Базовая частота комбинации (рассчитанная по простым частотам на столбец) составляет всего 0,0027%, что приводит к заниженным оценкам на два порядка.

Желательно создавать объекты статистики MCV только на комбинациях столбцов, которые фактически используются в условиях вместе, и для которых неправильная оценка количества групп приводит к плохим планам. В противном случае расчёты статистики внутри ANALYZE будут напрасны.

Управление планировщиком с помощью явных предложений JOIN

Можно в определенной степени управлять планировщиком запросов, используя явный синтаксис JOIN. Чтобы понять, почему это важно, нам сначала нужно немного предыстории.

В простом запросе с соедиенением, таком как:

SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;

Планировщик может соединять таблицы в любом порядке. Например, он может сгенерировать план запроса, который соединяет A с B, используя условие WHERE a.id = b.id, а затем присоединить C к этой объединенной таблице, используя другое условие WHERE. Или он может соединить B с C, а затем присоединить A к этому результату. Или он может соединить A с C, а затем соединить их с B - но это будет неэффективно, поскольку должно быть сформировано полное декартово произведение A и C, поскольку в WHERE нет применимого условия, позволяющего оптимизировать объединение., (Все объединения в исполнителе происходят между двумя входными таблицами, поэтому необходимо создать результат одним или другим из этих способов). Важным моментом является то, что эти различные возможности объединения дают семантически эквивалентные результаты, но могут иметь очень разные затраты на выполнение. Поэтому планировщик изучит все из них, чтобы попытаться найти наиболее эффективный план запроса.

Когда в запросе используются только две или три таблицы, не о чем беспокоиться. Но количество возможных порядков соединения растет по экспоненте по мере увеличения количества таблиц. После десяти или более входных таблиц более нецелесообразно проводить исчерпывающий поиск всех возможностей, и даже для шести или семи таблиц планирование может занять очень много времени. Когда входных таблиц слишком много, планировщик QHB переключается с полного поиска на генетический вероятностный поиск с ограниченным числом возможностей. (Порог переключения задается параметром времени выполнения geqo_threshold). Генетический поиск занимает меньше времени, но он не обязательно находит наилучший возможный план.

Когда запрос включает внешние объединения, у планировщика имеется меньше свободы, чем для простых (внутренних) объединений. Например, рассмотрим:

SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

Хотя ограничения этого запроса внешне похожи на предыдущий пример, семантика отличается, потому что для каждой строки A, которая не имеет совпадающей строки в соединении B и C, должна быть выделена строка. Поэтому у планировщика нет выбора порядка соединения здесь : он должен соединить B с C и затем присоединить A к этому результату. Соответственно, этот запрос занимает меньше времени для планирования, чем предыдущий запрос. В других случаях планировщик может определить, что более одного варианта соединения является достаточным. Например, учитывая:

SELECT * FROM a LEFT JOIN b ON (a.bid = b.id) LEFT JOIN c ON (a.cid = c.id);

сначала можно присоединить A к B или C. В настоящее время только FULL JOIN полностью ограничивает порядок соединения. Большинство практических случаев, связанных с LEFT JOIN или RIGHT JOIN могут быть в некоторой степени перестроены.

Явный синтаксис внутреннего соединения ( INNER JOIN, CROSS JOIN или JOIN ) семантически аналогичен перечислению входных отношений в FROM, поэтому он не ограничивает порядок соединения.

Несмотря на то, что большинство видов JOIN не полностью ограничивают порядок соединения, можно поручить планировщику запросов QHB обрабатывать все предложения JOIN как ограничивающие порядок соединения. Например, эти три запроса логически эквивалентны:

SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

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

Чтобы планировщик следовал порядку соединения, установленному явными соединениями JOIN, установите для параметра времени выполнения join_collapse_limit значение 1. (Другие возможные значения обсуждаются ниже).

Вам не нужно полностью ограничивать порядок соединения, чтобы сократить время поиска, потому что можно использовать операторы JOIN в элементах простого списка FROM. Например, рассмотрим:

SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...;

Значение параметра join_collapse_limit = 1 заставляет планировщика соединять A с B, прежде чем соединять их с другими таблицами, но не ограничивает его выбор в противном случае. В этом примере количество возможных способов объединения уменьшается в 5 раз.

Ограничение таким способом поиска вариантов для планировщика является полезным методом как для сокращения времени планирования, так и для "направления" планировщика к хорошему плану запросов. Если планировщик выбирает неправильный порядок соединения по умолчанию, вы можете заставить его выбрать лучший порядок с помощью синтаксиса JOIN - при условии, что вы знаете лучший порядок.

Тесно связанной проблемой, которая влияет на время планирования, является объединение подзапросов в их родительский запрос. Например, рассмотрим:

SELECT *
FROM x, y,
    (SELECT * FROM a, b, c WHERE something) AS ss
WHERE somethingelse;

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

SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;

Это обычно приводит к лучшему плану, чем планирование подзапроса отдельно. (Например, внешние условия WHERE могут быть такими, что при присоединении X к A сначала удаляются многие строки из A, что позволяет избежать необходимости формировать полный логический вывод подзапроса), но в то же время мы увеличили время планирования - здесь мы имеем проблему пятистороннего соединения, заменяющую две отдельные проблемы трехстороннего соединения. Из-за экспоненциального роста числа возможностей соединения это имеет большое значение. Планировщик старается не зацикливаться на огромных проблемах поиска объединений, не сворачивая подзапрос, если в родительском запросе больше таблиц чем указано в параметре from_collapse_limit. Вы можете соотнести время планирования с качеством плана, настроив уменьшив или увеличив этот параметр времени выполнения.

from_collapse_limit и join_collapse_limit имеют похожие имена, потому что они выполняют почти одно и то же: один контролирует, когда планировщик "сгладит" подзапросы, а другой контролирует, когда он "сгладит" явные объединения. Обычно вы либо устанавливаете join_collapse_limit равным from_collapse_limit (чтобы явные объединения и подзапросы действовали аналогично), либо устанавливаете join_collapse_limit 1 (если вы хотите управлять порядком соединения с помощью явных указаний). Но вы можете установить их по-разному, если вы пытаетесь настроить компромисс между временем планирования и временем выполнения.

Заполнение базы данных

Может потребоваться вставить большой объем данных при первом заполнении базы данных. Этот раздел содержит некоторые предложения о том, как сделать этот процесс максимально эффективным.

Отключить автокоммит

При использовании нескольких INSERT выключите автокоммит и просто сделайте один коммит в конце. (В обычном SQL это означает команду BEGIN в начале и COMMIT в конце. Некоторые клиентские библиотеки могут делать это во внутренней реализации, и в этом случае вам нужно убедиться, что библиотека делает это, только когда вы этого хотите). Дополнительным преимуществом выполнения всех вставок в одной транзакции является то, что если при вставке одной строки произошел сбой, вставка всех строк, вставленных до этой точки, будет откатываться, поэтому вы не получите базу данных с частично загруженными данными.

Используйте COPY

Используйте команду COPY, чтобы загрузить все строки в одной команде, вместо использования серии команд INSERT. Команда COPY оптимизирована для загрузки большого количества строк; он менее гибок, чем INSERT, но значительно снижает накладные расходы при большой загрузке данных. Поскольку COPY - это одна команда, нет необходимости отключать автокоммит, если вы используете этот метод для заполнения таблицы.

Если вы не можете использовать COPY, это может помочь использовать PREPARE для создания подготовленного оператора INSERT, а затем использовать EXECUTE столько раз, сколько требуется. Это позволяет избежать некоторых накладных расходов, связанных с многократным анализом и планированием INSERT. Различные интерфейсы предоставляют эту возможность по-разному; ищите « подготовленные заявления » в документации интерфейса.

Обратите внимание, что загрузка большого количества строк с помощью COPY почти всегда быстрее, чем с помощью INSERT, даже если используется PREPARE и несколько вставок объединяются в одну транзакцию.

COPY быстрее всего используется в той же транзакции, что и предыдущая команда CREATE TABLE или TRUNCATE. В таких случаях не нужно писать в WAL, потому что в случае ошибки файлы, содержащие вновь загруженные данные, все равно будут удалены. Однако это соображение применимо только в том случае, если wal_level minimal для однораздельных таблиц, так как в противном случае все команды должны писать WAL.

Удалить индексы

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

Если вы добавляете большие объемы данных в существующую таблицу, может быть выгодно удалить индексы, загрузить таблицу, а затем воссоздать индексы. Конечно, производительность базы данных для других пользователей может снизиться во время отсутствия индексов. Следует проявить осторожность, прежде чем отбрасывать уникальный индекс, поскольку проверка ошибок, предоставляемая ограничением уникальности, будет потеряна, пока индекс отсутствует.

Удалить ограничения внешнего ключа

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

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

Увеличьте maintenance_work_mem

Временное увеличение параметра конфигурации maintenance_work_mem при загрузке больших объемов данных может привести к повышению производительности. Это поможет ускорить выполнение команд CREATE INDEX и ALTER TABLE ADD FOREIGN KEY. Это не будет иметь большого значения для самой COPY, поэтому этот совет полезен только при использовании одного или обоих из перечисленных методов.

Увеличить max_wal_size

Временное увеличение переменной конфигурации max_wal_size также может ускорить загрузку больших объемов данных. Это связано с тем, что загрузка большого количества данных в QHB приводит к тому, что контрольные точки появляются чаще, чем обычная частота контрольных точек (указывается в переменной конфигурации checkpoint_timeout). Всякий раз, когда возникает контрольная точка, все грязные страницы должны быть сброшены на диск. Временное увеличение max_wal_size во время массовой загрузки данных позволяет уменьшить количество необходимых контрольных точек.

Отключить архивацию WAL и потоковую репликацию

При загрузке больших объемов данных в установку, которая использует архивирование WAL или потоковую репликацию, может быть быстрее создать новую базовую резервную копию после завершения загрузки, чем обрабатывать большой объем добавочных данных WAL. Чтобы предотвратить добавочное ведение журнала WAL при загрузке, отключите архивацию и потоковую репликацию, установив для wal_level значение minimal, для параметра archive_mode off, а для max_wal_senders - ноль. Но учтите, что изменение этих настроек требует перезагрузки сервера.

Помимо сокращения времени обработки данных WAL архиватором или отправителем WAL, выполнение этого на самом деле ускорит выполнение определенных команд, поскольку они вообще не предназначены для записи WAL, если wal_level minimal. (Они могут гарантировать безопасность при fsync дешевле, выполнив fsync в конце, чем написав WAL). Это относится к следующим командам:

  • CREATE TABLE AS SELECT

  • CREATE INDEX (и варианты, такие как ALTER TABLE ADD PRIMARY KEY )

  • ALTER TABLE SET TABLESPACE

  • CLUSTER

  • COPY FROM, когда целевая таблица была создана или усечена ранее в той же транзакции

Запустите ANALYZE

Всякий раз, когда вы значительно изменили распределение данных в таблице, настоятельно рекомендуется использовать ANALYZE. Изменение распределения включает в себя в том числе и массовую загрузку больших объемов данных в таблицу. Запуск ANALYZE (или VACUUM ANALYZE ) гарантирует, что планировщик будет иметь актуальную статистику о таблице. При отсутствии статистики или устаревшей статистики планировщик может принимать неправильные решения во время планирования запросов, что приведёт к низкой производительности для любых таблиц с неточной или несуществующей статистикой. Обратите внимание, что если фоновый процесс autovacuum включен, он может автоматически запустить ANALYZE, см. разделы Обновление статистики планировщика и Процесс «Автовакуум» для получения дополнительной информации.

Некоторые заметки о qhb_dump

Сценарии дампа, сгенерированные qhb_dump, автоматически применяют несколько, но не все, из приведенных выше рекомендаций. Чтобы загрузить дамп qhb_dump как можно быстрее, вам нужно сделать несколько дополнительных действий вручную. (Обратите внимание, что эти пункты применяются при восстановлении дампа, а не при его создании. Те же пункты применяются при загрузке текстового дампа с помощью qsql или использовании qhb_restore для загрузки из файла архива qhb_dump).

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

  • Установить соответствующие (т. max_wal_size, чем обычно) значения для maintenance_work_mem и max_wal_size .

  • Если используется архивация WAL или потоковая репликация, рассмотреть возможность их отключения во время восстановления. Для этого установить для параметра archive_mode значение off, для wal_level значение minimal, а для max_wal_senders - ноль, прежде чем загружать дамп. После этого установить для них правильные значения и создайть новую базовую резервную копию.

  • Поэкспериментируйте с режимами параллельного дампа и восстановления qhb_dump и qhb_restore и найдите оптимальное количество одновременных заданий для использования. Параллельное сохранение и восстановление с помощью опции -j должен обеспечить значительно более высокую производительность по сравнению с последовательным режимом.

  • Подумайте, должен ли весь дамп быть восстановлен как одна транзакция. Для этого передайте параметр командной строки -1 или --single-transaction в qsql или qhb_restore. При использовании этого режима даже самые маленькие ошибки будут отменять все восстановление, возможно, отбрасывая многие часы обработки. В зависимости от того, насколько взаимосвязаны данные, это может показаться предпочтительным или нет. Команды COPY будут выполняться быстрее, если вы используете одну транзакцию и отключили архивирование WAL.

  • Если на сервере базы данных доступно несколько процессоров, попробуйте использовать параметр --jobs в qhb_restore. Это позволяет одновременно загружать данные и создавать индексы.

  • Запустите ANALYZE после всех операций.

Дамп только для данных будет по-прежнему использовать COPY, но он не удаляет и не воссоздает индексы и обычно не касается внешних ключей1. Таким образом, при загрузке дампа только для данных, вы можете удалить и воссоздать индексы и внешние ключи, если вы хотите использовать эти методы. По-прежнему полезно увеличивать max_wal_size при загрузке данных, но не беспокойтесь об увеличении maintenance_work_mem; скорее, вы сделаете это при ручном воссоздании индексов и внешних ключей впоследствии. И не забудьте ANALYZE когда вы закончите; см. разделы Обновление статистики планировщика и Процесс «Автовакуум» для получения дополнительной информации.

Настройки снижающие надежность

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

  • Поместите каталог данных кластера базы данных в файловую систему с поддержкой памяти (то есть RAM- диск). Это исключает все операции ввода-вывода на диске базы данных, но ограничивает объем хранения данных до объема доступной памяти (и, возможно, подкачки).

  • Выключите fsync - нет необходимости сбрасывать данные на диск.

  • Выключите synchronous_commit - может не потребоваться форсировать запись WAL на диск при каждом коммите. Этот параметр создает риск потери транзакции (но не повреждения данных) в случае сбоя базы данных.

  • Отключите full_page_writes - нет необходимости принимать меры против частичной записи страницы.

  • Увеличьте max_wal_size и checkpoint_timeout - это уменьшает частоту контрольных точек, но увеличивает требования к хранилищу /pg_wal.

  • Создавайте unlogged tables, чтобы избежать записи WAL, хотя это сделает таблицы не защищенными от сбоев.

1

Вы можете получить эффект отключения внешних ключей, используя опцию --disable-triggers но понимайте, что это устраняет, а не просто откладывает, проверку внешнего ключа, и поэтому можно вставлять неверные данные, если вы используете эту опцию.