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

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

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

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

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;

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

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

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

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

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

Основы EXPLAIN

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

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

EXPLAIN SELECT * FROM tenk1;

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

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

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

Стоимость измеряется в произвольных единицах, определяемых параметрами затрат планировщика (см. подраздел Константы стоимости для планировщика). Традиционно единицей стоимости считается операция извлечения страницы с диска, то есть в параметре 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)

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

Теперь давайте добавим к предложению 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 к предыдущему плану, потому что при этом не удалось бы избежать затрат на запуск сканирования битовой карты, поэтому общая стоимость при таком подходе превышала бы 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 = константа. (На самом деле ожидаемая стоимость немного ниже, чем было показано ранее, из-за кэширования, которое предположительно произойдет во время повторного сканирования индекса в 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.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 будет выполнено только один раз, даже если узел соединения с вложенным циклом должен прочитать эти данные десять раз, по одному разу для каждой строки из внешнего отношения. Узел Materialise сохраняет считанные данные в памяти, а затем возвращает их при каждом последующем проходе.

При работе с внешними объединениями вы можете увидеть узлы плана соединения с добавленными условиями «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, который читает строки из узла своего внешнего дочернего плана и проверяет каждую из них по хеш-таблице.

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

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

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

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. В приведенном выше примере на выполнение сканирования индекса в tenk2 мы потратили в общей сложности 0,220 миллисекунды.

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

SELECT attname, inherited, n_distinct,
       array_to_string(most_common_vals, E'\n') as most_common_vals
FROM pg_stats
WHERE tablename = 'road';

 attname | inherited | n_distinct |          most_common_vals
---------+-----------+------------+------------------------------------
 name    | f         |  -0.363388 | I- 580                        Ramp+
         |           |            | I- 880                        Ramp+
         |           |            | Sp Railroad                       +
         |           |            | I- 580                            +
         |           |            | I- 680                        Ramp
 name    | t         |  -0.284859 | I- 880                        Ramp+
         |           |            | I- 580                        Ramp+
         |           |            | I- 680                        Ramp+
         |           |            | I- 580                            +
         |           |            | State Hwy 13                  Ramp
(2 rows)

Обратите внимание, что для одного столбца отображаются две строки: одна соответствует полной иерархии наследования, начинающейся в таблице road (inherited=t), а другая включает только саму таблицу road (inherited=f).

Объем информации, сохраняемой в 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 (zip code, почтовый индекс) полностью определяет столбец 5 (city, город), поэтому коэффициент равен 1.0, тогда как город определяет почтовый индекс только в 42% случаев, то есть существует много городов (58%), которые представлены более чем одним почтовым индексом.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Многовариантные списки MCV

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

Чтобы улучшить такие оценки, ANALYZE может собирать списки MCV по комбинациям столбцов. Подобно функциональным зависимостям и коэффициентам уникальных значений, их нецелесообразно делать для всех возможных группировок столбцов. В данном случае этот постулат особенно верен, поскольку список MCV (в отличие от функциональных зависимостей и коэффициентов уникальных значений) хранит общие значения столбцов. Поэтому данные собираются только для тех групп столбцов, которые находятся вместе в объекте статистики, определенном параметром 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)

Эти данные указывают на то, что наиболее распространенной комбинацией города и штата является Washington и DC, с фактической частотой (в выборке) около 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 происходят между двумя входными таблицами, поэтому результат нужно получить одним из этих способов). Важным моментом является то, что эти различные возможности соединения дают семантически равнозначные результаты, но затраты на их выполнение могут сильно различаться. Поэтому планировщик изучит их все, чтобы попытаться найти наиболее эффективный план запроса.

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

сначала можно соединить А с 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. Уменьшая или увеличивая этот параметр времени выполнения, можно подобрать оптимальное соотношение времени планирования и качества плана.

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

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

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

Выключите автоматическую фиксацию

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

Используйте команду COPY

Вместо серии команд INSERT используйте команду COPY, чтобы загрузить все строки в одной команде. Команда 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_level равен minimal и текущая субтранзакция (или транзакция верхнего уровня) создала или опустошила таблицу или индекс, которые они изменяют, эти команды вообще ничего не протоколируют в WAL. (Они могут гарантировать безопасность данных при сбое, не протоколируя в WAL, а выполнив в конце fsync, что гораздо дешевле.)

Запустите в конце команду ANALYZE

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

Несколько замечаний о qhb_dump

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

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

Установить соответствующие (т. е. более высокие, чем обычно) значения для maintenance_work_mem и max_wal_size.
Если используется архивация WAL или потоковая репликация, рассмотреть возможность их выключения во время восстановления. Для этого прежде чем загружать дамп, установить для параметра archive_mode значение off, для wal_level — minimal, а для max_wal_senders — ноль. Завершив восстановление, вернуть им правильные значения и создать новую резервную копию базы.
Поэкспериментировать с режимами параллельного резервного копирования и восстановления qhb_dump и qhb_restore и найти оптимальное количество параллельных заданий. Параллельное копирование и восстановление посредством параметра -j должно обеспечить гораздо более высокую производительность, чем последовательный режим.
Рассмотреть возможность восстановления всего дампа за одну транзакцию. Для этого передать в psql или qhb_restore параметр командной строки -1 или --single-transaction. При использовании этого режима даже самые маленькие ошибки будут отменять все восстановление, возможно, сводя на нет многие часы обработки. В зависимости от того, насколько взаимосвязаны данные, этот вариант может оказаться предпочтительнее ручной очистки (или нет). Команды COPY будут выполняться быстрее, если вы используете одну транзакцию и выключили архивацию WAL.
Если на сервере баз данных доступно несколько процессоров, попробовать использовать в qhb_restore параметр --jobs. Это позволит одновременно загружать данные и создавать индексы.
По завершении всех операций запустить ANALYZE.

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

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

Настройки, снижающие прочность

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

Поместите каталог данных кластера базы данных в файловую систему, размещенную в памяти (т. е. в RAM-диск). Это исключает все операции ввода/вывода на диске базы данных, но ограничивает объем хранения данных до объема доступной памяти (и, возможно, файла подкачки).
Выключите fsync — нет необходимости сбрасывать данные на диск.
Выключите synchronous_commit — возможно, принудительно записывать WAL на диск при фиксации каждой транзакции не потребуется. Однако это создает риск потери транзакции (но не повреждения данных) в случае сбоя базы данных.
Выключите full_page_writes — нет необходимости принимать меры против частичной записи страницы.
Увеличьте max_wal_size и checkpoint_timeout — это уменьшает частоту контрольных точек, но увеличивает объем хранилища /pg_wal.
Создавайте нежурналируемые таблицы, чтобы избежать протоколирования в WAL (однако это лишит таблицы защиты от сбоя).