Указания для планировщика запросов СУБД (hints)

Содержание раздела

Поддерживаемые запросы и СУБД
Возможности системы
Синтаксис
1. Указания для установки параметров исполнения запроса (ADP, ADB, ADQM)
2. Указания для передачи в расширение pg_hint_plan (ADP, ADB)
Обработка указаний
Указания логических представлений
Ограничения
1. Ограничения СУБД
2. Другие ограничения
Примеры

Указание — комментарий специального формата, который задается в запросе и изменяет план его исполнения, не меняя конфигурацию датасорса. Например, можно задать наиболее оптимальный для запроса метод сканирования или соединения таблиц.

Возможности системы в части указаний зависят от СУБД.

Поддерживаемые запросы и СУБД

Указания поддерживаются в следующих запросах к ADP, ADB и ADQM:

SELECT, включая потоковое чтение и чтение с табличным параметром;
INSERT INTO download_external_table;
CREATE VIEW*;
ALTER VIEW*.

* Указания задаются в CREATE VIEW/ALTER VIEW, а применяются — при исполнении SELECT-запроса к представлению.

В остальных видах запросов к ADP, ADB и ADQM, а также в любых запросах к ADG указания игнорируются.

Возможности системы

Возможности системы в части указаний зависят от СУБД и перечислены в таблице ниже.

Возможность	Доступность возможности	Поддерживаемые СУБД	Виды указаний	Формат задания указаний
Установка параметров исполнения запроса в СУБД	По умолчанию; не требует настройки	ADP, ADB, ADQM	Set	Единый формат для всех СУБД (преобразуется в формат СУБД перед передачей запроса в нее)
Передача указаний в СУБД в формате расширения pg_hint_plan	При включении опции** в конфигурации нод и наличии расширения `pg_hint_plan` в СУБД	ADP, ADB	Указания расширения `pg_hint_plan`, включая Set	Формат расширения pg_hint_plan

** Опция включается с помощью установки значения параметра ADP_PG_HINT_PLAN_EXTENSION_ENABLED (для ADP) или ADB_PG_HINT_PLAN_EXTENSION_ENABLED (для ADB) равным true.

Синтаксис

Указание состоит из следующих элементов:

имя,
пара «параметр и значение» или список значений (например, имен таблиц).

Параметр и значение или значения задаются после имени указания в круглых скобках и разделяются пробелами, например так: Set(random_page_cost 2.0). Состав и количество параметров и значений зависят от вида указания.

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

Указания для установки параметров исполнения запроса (ADP, ADB, ADQM)

Указание, устанавливающее параметр исполнения запроса, задается в следующем формате:

/*+ Set(<имя_параметра_СУБД> <значение_параметра_СУБД>) */

Значения параметров должны быть указаны строго в формате СУБД:

-- пример для ADP/ADB
/*+ Set(random_page_cost 2.0)
    Set(enable_indexscan off) */

-- пример для ADQM
/*+ Set(distributed_product_mode global) 
    Set(use_query_cache true) 
    Set(query_cache_ttl 120)
    Set(max_execution_time 30) */

Если значение указания содержит закрывающую скобку, ее необходимо экранировать символом обратного слеша. Например, Set(additional_result_filter number % 2 = 0 AND (number < 2 OR number > 6\)).

Указание Set(distributed_product_mode ...) в запросах к ADQM задает режим соединения сущностей. От выбранного режима зависит поддержка JOIN и [NOT] IN.

Указания для передачи в расширение pg_hint_plan (ADP, ADB)

Указания, передаваемые в pg_hint_plan, задаются в формате этого расширения.

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

Чтобы посмотреть обогащенный запрос и имена физических таблиц в нем, выполните SELECT-запрос с ESTIMATE_ONLY.

Обработка указаний

Перед передачей указаний в СУБД система подготавливает их итоговый список:

объединяет указания всех блоков, заданных в запросе;
разрешает конфликты между указаниями Set;
исключает неподдерживаемые указания и преобразует оставшиеся в формат СУБД, если необходимо — в зависимости от СУБД и конфигурации нод.

Список указаний (до финального преобразования в формат СУБД) можно получить, выполнив:

[для SELECT] сам запрос с ESTIMATE_ONLY;
[для INSERT INTO download_external_table] SELECT-подзапрос запроса с ESTIMATE_ONLY;
[для CREATE/ALTER VIEW] запрос GET_ENTITY_DDL или запрос через /entity с именем представления.

Этап 1 — объединение указаний запроса

Система объединяет указания из разных блоков запроса в общий список, сохраняя их порядок появления в запросе.

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

-- исходный запрос для ADP/ADB
-- в СУБД подключено расширение pg_hint_plan, и значение параметра конфигурации ADP_PG_HINT_PLAN_EXTENSION_ENABLED / ADB_PG_HINT_PLAN_EXTENSION_ENABLED = true
/*+ 
  IndexScan(a_actual)
  Set(random_page_cost 5.0)
*/
SELECT * FROM a
/*+ 
  BitmapScan(b_actual)
  Set(enable_indexscan off)
*/
JOIN b ON a.id = b.id;

-- запрос с подготовленным списком указаний
/*+ 
  IndexScan(a_actual)
  Set(random_page_cost 5.0)
  BitmapScan(b_actual)
  Set(enable_indexscan off)
*/
SELECT * FROM a
JOIN b ON a.id = b.id;

Этап 2 — обработка конфликтующих указаний

Обработка конфликтующих указаний запроса, которые устанавливают одну настройку в разные значения, зависит от вида указаний:

[для указаний Set***] система формирует непротиворечивый список указаний, оставляя только одно из конфликтующих указаний:
- [внутри одного блока указаний] последнее из них;
- [между разными блоками указаний] первое из них;
[для указаний других видов] система пропускает обработку конфликтов.

*** Конфликты среди указаний Set разрешаются независимо от того, устанавливаются ли они как параметры исполнения запроса в СУБД или передаются в расширение pg_hint_plan.

Ниже показан пример с двумя парами конфликтующих указаний: Set(random_page_cost ... ) и Set(enable_indexscan ... ) — каждое с двумя разными значениями.

-- исходный запрос для ADP/ADB
-- в СУБД подключено расширение pg_hint_plan, и значение параметра конфигурации ADP_PG_HINT_PLAN_EXTENSION_ENABLED / ADB_PG_HINT_PLAN_EXTENSION_ENABLED = true
/*+ 
  IndexScan(a_actual)
  Set(random_page_cost 2.0)
  Set(enable_indexscan off)
  Set(random_page_cost 5.0)
*/
SELECT * FROM a
/*+ 
  BitmapScan(b_actual)
  Set(enable_indexscan on)
*/
JOIN b ON a.id = b.id;

-- запрос с подготовленным списком указаний, из которого исключены:
-- - Set(random_page_cost 2.0) — в пользу более позднего указания Set(random_page_cost 5.0) того же блока
-- - Set(enable_indexscan on) — в пользу указания Set(enable_indexscan off) предшествующего блока
/*+ 
  IndexScan(a_actual)
  Set(enable_indexscan off)
  Set(random_page_cost 5.0)
  BitmapScan(b_actual)
*/
SELECT * FROM a
JOIN b ON a.id = b.id;

Этап 3 — обработка указаний по типу СУБД

ADP и ADB

Обработка указаний для ADP и ADB, оставшихся после разрешения конфликтов, зависит от значений параметров конфигурации ADP_PG_HINT_PLAN_EXTENSION_ENABLED и ADB_PG_HINT_PLAN_EXTENSION_ENABLED соответственно, как показано в таблице ниже.

`ADP_PG_HINT_PLAN_EXTENSION_ENABLED`/ `ADB_PG_HINT_PLAN_EXTENSION_ENABLED`	Описание значения	Поведение
`false` (по умолчанию)	Расширение pg_hint_plan отсутствует в СУБД	Указания `Set(<имя_параметра> <значение_параметра>)` преобразуются в SQL-команды `SET LOCAL <имя_параметра> = '<значение_параметра>'` (с сохранением регистра значений), которые выполняются в СУБД в начале транзакции запроса. Прочие указания исключаются из запроса перед его передачей в СУБД
`true`	Расширение pg_hint_plan подключено в СУБД	Все заданные указания передаются в СУБД в начале запроса, сохраняя свой исходный синтаксис

Пример преобразования:

-- исходный запрос для ADP/ADB
-- в СУБД отключено расширение pg_hint_plan, и значение параметра конфигурации ADP_PG_HINT_PLAN_EXTENSION_ENABLED / ADB_PG_HINT_PLAN_EXTENSION_ENABLED = false
/*+ 
  IndexScan(a_actual)
  Set(random_page_cost 5.0)
*/
SELECT * FROM a
/*+ 
  BitmapScan(b_actual)
  Set(enable_indexscan off)
*/
JOIN b ON a.id = b.id;

-- исполнение запроса в СУБД: указания IndexScan и BitmapScan исключены как неподдерживаемые, а указания Set пребразованы в SET LOCAL и исполняются в транзакции перед запросом
BEGIN;
SET LOCAL random_page_cost='5.0';
SET LOCAL enable_indexscan='off';
SELECT * FROM a_actual JOIN b_actual ON a.id=b.id .... ;
COMMIT;

ADQM

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

Прочие указания исключаются из запроса перед его передачей в СУБД.

Особенности обработки Set(statement_timeout/max_execution_time …)

Указание, ограничивающее время исполнения запроса в СУБД (Set(statement_timeout ...) для ADP и ADB, Set(max_execution_time ...) для ADQM), передается в СУБД с заданным значением и не учитывает время, уже потраченное системой к этому моменту на предобработку запроса.

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

Общий тайм-аут не поддерживается для запросов на выгрузку данных, и единственный способ ограничить время исполнения таких запросов в СУБД — с помощью указания Set(statement_timeout ...) / Set(max_execution_time ...).

Указания логических представлений

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

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

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

Пример преобразования:

-- выбор логической БД, используемой по умолчанию
USE marketing;

-- запрос на создание представления v с указаниями;
-- указание IndexScan требует наличия pg_hint_plan в СУБД и ADP_PG_HINT_PLAN_EXTENSION_ENABLED = true в конфигурации нод, иначе оно исключается из запроса
CREATE VIEW v AS
/*+ 
  IndexScan(a_actual)
  Set(random_page_cost 2.0)
  Set(enable_indexscan off)
  Set(random_page_cost 5.0)
*/
SELECT * FROM a;

-- запрос, обращающийся к представлению
/*+ 
  Set(random_page_cost 1.0)
  Set(enable_indexscan on)
*/
SELECT * FROM v
JOIN b ON v.id = b.id
DATASOURCE_TYPE = 'adp2';

-- обогащенный запрос к представлению, передаваемый в СУБД; 
-- указания представления Set(random_page_cost 5.0) и Set(enable_indexscan off) исключены в пользу указаний запроса
/*+ Set(random_page_cost 1.0) Set(enable_indexscan on) IndexScan(a_actual) */
SELECT * FROM (SELECT * FROM a_actual ....) AS v
JOIN b_actual ON v.id=b_actual.id .... ;

Ограничения

Ограничения СУБД

Указания применимы только для запросов к ADP, ADB или ADQM.
При ADP_PG_HINT_PLAN_EXTENSION_ENABLED / ADB_PG_HINT_PLAN_EXTENSION_ENABLED = false и отсутствии расширения pg_hint_plan указания в ADP и ADB:
- могут использоваться только для установки параметров исполнения запроса;
- поддерживают только однокомпонентные значения — например, доступно указание Set(shared_buffers 128MB), но не Set(search_path public,"$user",postgres).
Указания в ADQM могут использоваться только для установки параметров исполнения запроса.

Другие ограничения

Указания поддерживаются только в следующих запросах:
- чтения данных, включая потоковое чтение и чтение с табличным параметром;
- выгрузки данных;
- CREATE VIEW, ALTER VIEW.
Система не проверяет корректность указаний: их состав и значения проверяются в СУБД при исполнении запроса.
Конфликты разрешаются только между указаниями Set. Остальные указания передаются полным списком в СУБД, где они применяются в порядке их перечисления (последнее из конфликтующих указаний имеет приоритет).

Примеры

Указания в SELECT-запросе к ADP

-- указания требуют расширения pg_hint_plan и ADP_PG_HINT_PLAN_EXTENSION_ENABLED = true, иначе они исключаются из запроса
/*+ 
  IndexScan(sales_actual)
  HashJoin(sales_actual stores_actual)
  Leading(stores_actual sales_actual)
*/
SELECT st.category, SUM(s.product_units) AS product_units
FROM marketing.stores AS st
JOIN marketing.sales AS s ON st.id = s.store_id
WHERE s.transaction_date >= '2020-01-01 00:00:00'
GROUP BY st.category
DATASOURCE_TYPE = 'adp';

Указания в SELECT-запросе к ADB

-- указания применяются при любом значении ADB_PG_HINT_PLAN_EXTENSION_ENABLED и независимо от наличия/отсутствия pg_hint_plan
/*+ 
  Set(enable_seqscan off)
  Set(work_mem 256MB) 
  Set(statement_timeout 30000)
*/
SELECT store_id, SUM(product_units) AS product_units
FROM marketing.sales
WHERE transaction_date >= '2020-01-01 00:00:00'
GROUP BY store_id
DATASOURCE_TYPE = 'adb';

Указания в SELECT-запросе к ADQM

-- обработка указаний не зависит от конфигурации и наличия/отсутствия pg_hint_plan
/*+ 
  Set(distributed_product_mode global)
  Set(max_threads 4) 
  Set(max_execution_time 30)
*/
SELECT s.product_code, SUM(s.product_units) AS product_units
FROM marketing.sales AS s
JOIN marketing.stores AS st ON s.store_id = st.id
GROUP BY s.product_code
DATASOURCE_TYPE = 'adqm';

Указания в запросе CREATE VIEW

-- указание SeqScan требует расширения pg_hint_plan и ADP_PG_HINT_PLAN_EXTENSION_ENABLED = true, иначе оно исключается из итогового списка указаний при обращении к представлению в SELECT-запросе
CREATE VIEW marketing.stores_by_sold_products AS
/*+ 
  SeqScan(sales_actual)
  Set(work_mem 256MB)
*/
SELECT store_id, SUM(product_units) AS product_amount
FROM marketing.sales
GROUP BY store_id
ORDER BY product_amount DESC
LIMIT 20;

Указания в запросе на выгрузку данных из ADP

-- IndexScan требует расширения pg_hint_plan и ADP_PG_HINT_PLAN_EXTENSION_ENABLED = true, иначе исключается из запроса; Set применяется независимо от конфигурации и наличия/отсутствия pg_hint_plan
/*+ 
  IndexScan(sales_actual)
  Set(statement_timeout 600000)
*/
INSERT INTO marketing.sales_ext_download
SELECT * FROM marketing.sales
WHERE transaction_date >= '2020-01-01 00:00:00'
DATASOURCE_TYPE = 'adp';

Указания в запросе на выгрузку данных из ADQM

-- обработка указаний не зависит от конфигурации и наличия/отсутствия pg_hint_plan
/*+ 
  Set(max_execution_time 600)
  Set(max_threads 8)
*/
INSERT INTO marketing.sales_ext_download
SELECT * FROM marketing.sales
WHERE transaction_date >= '2020-01-01 00:00:00'
DATASOURCE_TYPE = 'adqm';