Планирование запросов

Конфигурация метода планирования

Эти параметры конфигурации предоставляют грубый метод воздействия на планы запросов, выбранные оптимизатором запросов. Если выбранный оптимизатором стандартный план конкретного запроса оказался неоптимален, в качестве временного решения можно использовать один из этих параметров конфигурации, чтобы заставить оптимизатор выбрать другой план. К более удачным способам улучшения качества выбираемых оптимизатором планов относятся корректировка констант стоимости для планировщика (см. подраздел Константы стоимости для планировщика), выполнение ANALYZE вручную, увеличение значения параметра конфигурации default_statistics_target, а также увеличение объема статистики, собираемой для отдельных столбцов, с помощью команды ALTER TABLE SET STATISTICS.

enable_async_append (boolean)

Включает или выключает использование планировщиком запросов планов с асинхронным добавлением данных. Значение по умолчанию — on (включен).

enable_bitmapscan (boolean)

Включает или выключает использование планировщиком запросов планов сканирования по битовой карте. Значение по умолчанию — on (включен).

enable_gathermerge (boolean)

Включает или выключает использование планировщиком запросов планов слияния посредством сбора. Значение по умолчанию — on (включен).

enable_hashagg (boolean)

Включает или выключает использование планировщиком запросов планов агрегирования по хешу. Значение по умолчанию — on (включен).

enable_hashjoin (boolean)

Включает или выключает использование планировщиком запросов планов соединения по хешу. Значение по умолчанию — on (включен).

enable_incremental_sort (boolean)

Включает или выключает использование планировщиком запросов шагов с инкрементальной сортировкой. Значение по умолчанию — on (включен).

enable_indexscan (boolean)

Включает или выключает использование планировщиком запросов планов сканирования по индексу. Значение по умолчанию — on (включен).

enable_indexonlyscan (boolean)

Включает или выключает использование планировщиком запросов планов сканирования только по индексу (см. раздел Сканирование только по индексу и покрывающие индексы). Значение по умолчанию — on (включен).

enable_material (boolean)

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

enable_memoize (boolean)

Включает или выключает использование планировщиком запросов планов с мемоизацией для кэширования результатов параметризованного сканирования внутри соединений с вложенным циклом. Этот тип плана позволяет пропускать сканирование нижележащих планов, когда результаты для текущих параметров уже находятся в кэше. Менее просматриваемые результаты могут быть удалены из кэша, когда требуется больше места для новых записей. Значение по умолчанию — on (включен).

enable_mergejoin (boolean)

Включает или выключает использование планировщиком запросов планов соединения слиянием. Значение по умолчанию — on (включен).

enable_nestloop (boolean)

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

enable_parallel_append (boolean)

Включает или выключает использование планировщиком запросов планов с распараллеливанием добавления данных. Значение по умолчанию — on (включен).

enable_parallel_hash (boolean)

Включает или выключает использование планировщиком запросов планов соединения по хешу с распараллеливанием хеширования. Не действует, если не включены планы соединения по хешу. Значение по умолчанию — on (включен).

enable_partition_pruning (boolean)

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

enable_partitionwise_join (boolean)

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

enable_partitionwise_aggregate (boolean)

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

enable_seqscan (boolean)

Включает или выключает использование планировщиком запросов планов последовательного сканирования. Полностью убрать последовательное сканирование невозможно, но выключение этого параметра не позволит планировщику использовать этот метод, если доступны другие. Значение по умолчанию — on (включен).

enable_sort (boolean)

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

enable_tidscan (boolean)

Включает или выключает использование планировщиком запросов планов сканирования TID. Значение по умолчанию — on (включен).

Константы стоимости для планировщика

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

Примечание
К сожалению, нет четко определенного метода определения идеальных значений для переменных стоимости. Лучше всего рассматривать их как средние показатели для всего набора запросов, которые получит конкретная СУБД. Это означает, что менять их на основании всего нескольких экспериментов очень рискованно.

seq_page_cost (floating point)

Задает для планировщика стоимость выборки одной страницы с диска, которая выполняется в серии последовательных выборок. Значение по умолчанию — 1.0. Это значение можно переопределить для таблиц и индексов в определенном табличном пространстве, установив одноименный параметр табличного пространства (см. ALTER TABLESPACE).

random_page_cost (floating point)

Задает для планировщика стоимость непоследовательной выборки одной страницы с диска. Значение по умолчанию — 4.0. Это значение можно переопределить для таблиц и индексов в определенном табличном пространстве, установив одноименный параметр табличного пространства (см. ALTER TABLESPACE).

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

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

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

Совет
Хотя система позволит вам установить random_page_cost меньше, чем seq_page_cost, это лишено физического смысла. Однако сделать их равными имеет смысл, если база данных полностью кэшируется в ОЗУ, поскольку в этом случае непоследовательное обращение к страницам не вызовет дополнительных издержек. Кроме того, в сильно кэшированной базе данных оба этих параметра необходимо снизить относительно параметров ЦП, поскольку стоимость извлечения страницы, уже находящейся в ОЗУ, намного меньше, чем обычно.

cpu_tuple_cost (floating point)

Задает для планировщика стоимость обработки каждой строки при выполнении запроса. Значение по умолчанию — 0.01.

cpu_index_tuple_cost (floating point)

Задает для планировщика стоимость обработки каждой записи индекса при сканировании индекса. Значение по умолчанию — 0.005.

cpu_operator_cost (floating point)

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

parallel_setup_cost (floating point)

Задает для планировщика стоимость запуска параллельных рабочих процессов. Значение по умолчанию — 1000.

parallel_tuple_cost (floating point)

Задает для планировщика стоимость переноса одного кортежа из параллельного рабочего процесса в другой процесс. Значение по умолчанию — 0.1.

min_parallel_table_scan_size (integer)

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

min_parallel_index_scan_size (integer)

Задает минимальный объем данных индекса, подлежащий сканированию, при котором можно применить параллельное сканирование. Обратите внимание, что параллельное сканирование индекса обычно не затрагивает весь индекс; это число страниц, которое, по мнению планировщика, будет действительно затронуто при сканировании. Также этот параметр применяется для того, чтобы определить, может ли конкретный индекс участвовать в параллельной очистке. См. VACUUM. Если это значение указано без единиц измерения, оно считается заданным в блоках, размер которых равен BLCKSZ байт (обычно это 8 КБ). Значение по умолчанию — 512 килобайт (512kB).

effective_cache_size (integer)

Определяет предположение планировщика об эффективном размере дискового кэша, доступном для одного запроса. Это предположение учитывается при оценке стоимости использования индекса; чем выше это значение, тем больше вероятность того, что будет применено сканирование по индексу, чем ниже, тем больше вероятность того, что будет применено последовательное сканирование. При установке этого параметра следует учитывать как объем общих буферов QHB, так и процент дискового кэша ядра, который будут занимать файлы данных QHB, хотя некоторые данные могут находиться и там, и там. Также примите во внимание ожидаемое количество параллельных запросов к разным таблицам, так как им придется совместно использовать доступное пространство. Этот параметр не влияет на размер разделяемой памяти, выделяемой QHB, и не задает размер резервируемого дискового кэша ядра; он используется только для ориентировочной оценки. Кроме того, система не предполагает, что данные могут оставаться в дисковом кэше между запросами. Если это значение указано без единиц измерения, оно считается заданным в блоках, размер которых равен BLCKSZ байт (обычно это 8 КБ). Значение по умолчанию — 4 гигабайта (4GB). (Если BLCKSZ не равен 8 КБ, значение по умолчанию масштабируется пропорционально ему).

jit_above_cost (floating point)

Задает предел стоимости запроса, при превышении которого активируется JIT-компиляция, если она включена (см. главу JIT-компиляция). Применение JIT занимает время при планировании, но может ускорить выполнение запроса. При значении -1 JIT-компиляция выключается. Значение по умолчанию — 100000.

jit_inline_above_cost (floating point)

Задает предел стоимости запроса, при превышении которого в процессе JIT-компиляции возможно встраивание функций и операторов. Встраивание увеличивает время планирования, но может ускорить выполнение. Присваивать этому параметру значение меньше, чем jit_above_cost, не имеет смысла. При значении -1 встраивание выключается. Значение по умолчанию — 500000.

jit_optimize_above_cost (floating point)

Задает предел стоимости запроса, при превышении которого в процессе JIT-компиляции будут применяться дорогостоящие оптимизации. Такие оптимизации увеличивают время планирования, но могут ускорить выполнение. Присваивать этому параметру значение меньше, чем jit_above_cost, не имеет смысла, а значение больше, чем jit_inline_above_cost, вряд ли даст положительный эффект. При значении -1 дорогостоящие оптимизации выключаются. Значение по умолчанию — 500000.

Параметры генетического оптимизатора запросов

Генетический оптимизатор запросов (Genetic Query Optimizer, GEQO) — это алгоритм, который выполняет планирование запросов с помощью эвристического поиска. Это сокращает время планирования сложных запросов (тех, в которых соединяется множество отношений) за счет создания планов, иногда уступающих по качеству планам, выявляемым обычным алгоритмом полного перебора. Дополнительную информацию см. в главе Генетический оптимизатор запросов.

geqo (boolean)

Включает или выключает генетическую оптимизацию запросов. По умолчанию она включена. В производственной среде ее лучше не выключать; более детальное управление GEQO дает переменная geqo_threshold.

geqo_threshold (integer)

Задает минимальное число элементов во FROM, при котором для планирования запросов будет использоваться генетический оптимизатор запросов. (Обратите внимание, что конструкция FULL OUTER JOIN считается одним элементом FROM). Значение по умолчанию — 12. Для более простых запросов, как правило, лучше использовать обычный планировщик, выполняющий полный перебор, но для запросов со многими таблицами полный перебор занимает слишком много времени, зачастую больше, чем тратится на выполнение неоптимального плана. Таким образом, ограничение размера запроса является удобным способом управления GEQO.

geqo_effort (integer)

Управляет балансом между временем планирования и качеством плана запроса в GEQO. Эта переменная должна задаваться целым числом в диапазоне от 1 до 10. Значение по умолчанию — пять. Значения выше этого увеличивают время, затрачиваемое на планирование запросов, но при этом повышают вероятность выбора эффективного плана запросов.

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

geqo_pool_size (integer)

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

geqo_generations (integer)

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

geqo_selection_bias (floating point)

Управляет смещением выбора, используемым GEQO. Смещение выбора — это избирательное давление в популяции. Допустимые значения лежат в диапазоне от 1.50 до 2.00 (это значение по умолчанию).

geqo_seed (floating point)

Управляет начальным значением генератора случайных чисел, используемого GEQO для выбора случайных путей в пространстве поиска порядка соединений. Значение может варьироваться от нуля (по умолчанию) до единицы. Изменение значения меняет набор анализируемых путей соединения и может привести к тому, что будет найден либо более, либо менее оптимальный путь.

Другие параметры планировщика

default_statistics_target (integer)

Задает целевой показатель статистики по умолчанию для столбцов таблицы, у которых командой ALTER TABLE SET STATISTICS не заданы целевые значения. Большие значения увеличивают время, необходимое для выполнения ANALYZE, но при этом могут улучшить качество оценок планировщика. Значение по умолчанию — 100. Дополнительную информацию об использовании статистики планировщиком запросов QHB см. в разделе Статистика, используемая планировщиком.

constraint_exclusion (enum)

Управляет использованием в планировщике запросов ограничений таблицы для оптимизации запросов. Допустимые значения constraint_exclusion: on (включен) — проверять ограничения для всех таблиц, off (выключен) — никогда не проверять ограничения и partition (партиции) — проверять ограничения только для дочерних таблиц и подзапросов UNION ALL. По умолчанию установлен вариант partition. Он часто применяется с традиционными деревьями наследования для улучшения производительности.

Когда данный параметр разрешает это для конкретной таблицы, планировщик сравнивает условия запроса с ограничениями CHECK этой таблицы и не сканирует ее, если условия противоречат ограничениям. Например:

CREATE TABLE parent(key integer, ...);
CREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent);
CREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent);
...
SELECT * FROM parent WHERE key = 2400;

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

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

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

cursor_tuple_fraction (floating point)

Задает для планировщика оценку процента строк, которые будут извлечены через курсор. Значение по умолчанию — 0.1. При уменьшении значения этого параметра планировщик будет склонен использовать для курсоров планы «быстрого запуска», которые будут быстро извлекать первые несколько строк, хотя для извлечения всех строк потребуется много времени. При увеличении значения акцент сместится в сторону оптимизации общего расчетного времени запроса. При максимальном значении, равном 1,0, работа с курсорами планируется так же, как и обычные запросы, с учетом только общего расчетного времени, а не скорости доставки первых строк.

from_collapse_limit (integer)

Задает максимальное число элементов в итоговом списке FROM, до которого планировщик будет объединять подзапросы с запросами верхнего уровня. При меньших значениях сокращается время планирования, но планы запросов могут стать менее эффективными. Значение по умолчанию — восемь. Дополнительную информацию см. в разделе Управление планировщиком с помощью явных предложений JOIN.

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

jit (boolean)

Определяет, может ли QHB задействовать JIT-компиляцию, если та поддерживается (см. главу JIT-компиляция). Значение по умолчанию — on (включен).

join_collapse_limit (integer)

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

По умолчанию значение этой переменной равно значению from_collapse_limit, что приемлемо в большинстве случаев. При значении 1 порядок явных предложений JOIN меняться не будет. Таким образом, явно заданный в запросе порядок соединений определит фактический порядок, в котором будут соединяться отношения. Поскольку планировщик запросов не всегда выбирает оптимальный порядок соединений, опытные пользователи могут временно задать для этой переменной значение 1, а затем явно указать желаемый порядок соединения. Дополнительную информацию см. в разделе Управление планировщиком с помощью явных предложений JOIN.

plan_cache_mode (enum)

Подготовленные операторы (явно подготовленные или неявно сгенерированные, например, в PL/pgSQL) могут выполняться с использованием специализированных или общих планов. Специализированные планы создаются заново для каждого выполнения с определенным набором значений параметров, в то время как общие планы не зависят от значений параметров и могут использоваться повторно. Таким образом, общий план экономит время планирования, но может быть неэффективным, если идеальный план во многом зависит от значений параметров. Выбор между этими параметрами обычно производится автоматически, но его можно переопределить с помощью параметра plan_cache_mode. Допустимые значения: auto (по умолчанию), force_custom_plan (принудительно использовать специализированные планы) и force_generic_plan (принудительно использовать общие планы). Этот параметр учитывается при выполнении кэшированного плана, а не при его подготовке. Дополнительную информацию см. на справочной странице команды PREPARE.

recursive_worktable_factor (floating point)

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