pg_outline. Изменение плана выполнения запросов#

Версия: 1.1.

В исходном дистрибутиве установлено по умолчанию: да.

Связанные компоненты: отсутствуют.

Схема размещения: outline.

Расширение pg_outline предназначено для изменение плана выполнения запросов.

Функциональные возможности расширения pg_outline#

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

  • управление (добавить, заменить, удалить, включить, выключить) правилами подмены (фиксациями подсказок pg_hint_plan) планов запросов;

  • возврат информации о созданных правилах подмены планов запросов;

  • возврат идентификатора запроса (queryId) для текста запроса (queryText).

Подробное описание в документе «Список PL/SQL функций продукта» раздел «Функциональные возможности расширения pg_outline» (доступен в личном кабинете).

Подмена подсказок#

Получение идентификатора запроса (query ID)#

Идентификатор запроса используется для сопоставления выполняемого запроса и правила фиксации и/или подмены этого запроса.

Получить идентификатор в ручном режиме можно через:

  • расширение pg_stat_statements;

  • функцию outline.identify. Подробнее в документе «Список PL/SQL функций продукта», раздел «Функциональные возможности расширения pg_outline» (документ доступен в личном кабинете).

Получение идентификатора при помощи расширения pg_stat_statements#

Для получения идентификатора при помощи расширения pg_stat_statements выполните:

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

    SELECT * FROM table1;

  2. Найдите идентификатор запроса (поле queryid) в таблице расширения pg_stat_statements:

    SELECT * FROM pg_stat_statements;

    Примечание:

    Идентификатор запроса (поле queryid) представляет собой целое положительное или отрицательное число.

Получение идентификатора при помощи функции outline.identify#

Для получения идентификатора передайте текст запроса в качестве аргумента:

SELECT outline.identify( 'SELECT * FROM mytable WHERE x=10;' );

Примечание:

В тексте запроса (queryText) обязательно укажите все константы. Это необходимо для определения типа данных этих констант (само значение констант неважно).

Особенности#

В данном разделе приведены особенности работы с расширением pg_outline.

Номера ссылок#

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

Формирование идентификатора#

В зависимости от метода передачи параметров некоторые запросы могут иметь разный идентификатор (queryid). Например, два идентичных (в генерализованном виде) запроса будут иметь разные query_id:

PREPARE foo1(int) AS SELECT f1, f2 FROM table1 where f1 = $1
PREPARE foo2      AS SELECT f1, f2 FROM table1 where f1 = 123

Доработка#

Доработка: Фиксация планов запросов.

Версия: 4.5.0.

Ограничения#

Ограничения отсутствуют.

Установка#

Установка расширения может быть произведена в процессе развертывании СУБД Pangolin при использовании настроек по умолчанию (документ «Руководство по установке», раздел «Автоматизированная установка при помощи Ansible-скриптов»).

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

Для включения расширения необходимо установить конфигурационный параметр pg_outline.enable в значение true.

Включить расширение pg_outline можно:

  • только для текущей сессии:

    SET pg_outline.enable = TRUE;

  • для всех сессий — в postgresql.conf пропишите:

    pg_outline.enable = on

Примечание:

Начиная с версии Pangolin 6.4.3 pg_outline поддерживает мажорные обновления. Во время таких обновлений идентификатор запроса (queryId) обычно изменяется, что делает старые значения внутри таблицы outlines недействительными. Для решения этой проблемы была добавлена отдельная таблица outline_ref, в которой хранится связь между queryId и query_text. После мажорных обновлений, используя этот query_text, пересчитывается queryId, делая outlines снова действительными.

Обратите внимание, что данные добавляются в таблицу outline_ref при выполнении операций set или set_hint, когда предоставляется query_text. Поэтому использование функции с queryId вместо query_text, после мажорного обновления приведет к появлению недействительных обрисовок.

Настройка#

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

Использование модуля#

Порядок вывода функции outline.get#

Рассматривается пример заведение нового hint и проверка вывода информации об этом hint функциями outline.get(text) и outline.get(bigint).

Шаги

  1. Создайте hint для запроса, выполнив команду:

    SELECT outline.set_hint( 'SELECT f1, f2 FROM table2 WHERE f1 < 250 ORDER BY f1 LIMIT 1;', 'SeqScan(table2)' );

  2. Проверьте вывод функции outline.get(text), выполнив команду:

    SELECT * FROM outline.get( 'SELECT f1, f2 FROM table2 WHERE f1 < 250 ORDER BY f1 LIMIT 1;' );

    Пример вывода:

    |       queryid       |              plan               |        outline         | applicationname | active |
|---------------------|---------------------------------|------------------------|-----------------|--------|
| <queryid>           | Limit                          +| /*+ SeqScan(table2) */ |                 | t      |
|                     |   ->  Sort                     +|                        |                 |        |
|                     |         Sort Key: f1           +|                        |                 |        |
|                     |         ->  Seq Scan on table2 +|                        |                 |        |
|                     |               Filter: (f1 < $1)+|                        |                 |        |
|                     |                                 |                        |                 |        |

  3. Проверьте вывод функции outline.get(bigint), выполнив команду:

    SELECT * FROM outline.get( <queryid> );

    Идентификатор подставьте из предыдущего пункта. Пример вывода:

    |       queryid       |              plan               |        outline         | applicationname | active |
|---------------------|---------------------------------|------------------------|-----------------|--------|
| <queryid>           | Limit                          +| /*+ SeqScan(table2) */ |                 | t      |
|                     |   ->  Sort                     +|                        |                 |        |
|                     |         Sort Key: f1           +|                        |                 |        |
|                     |         ->  Seq Scan on table2 +|                        |                 |        |
|                     |               Filter: (f1 < $1)+|                        |                 |        |
|                     |                                 |                        |                 |        |

Работа подготавливаемых запросов с подзапросами при активном расширении pg_outline#

Шаги:

  1. Включите pg_outline (если не включен):

    CREATE EXTENSION IF NOT EXISTS pg_outline;
CREATE EXTENSION IF NOT EXISTS pg_hint_plan;
ALTER SYSTEM SET pg_hint_plan.enable_hint TO on;
ALTER SYSTEM SET pg_outline.enable TO on;
SELECT pg_reload_conf();

  2. Проверьте работу подмены плана запроса, выполнив запрос:

    CREATE TABLE t( id INTEGER, PRIMARY KEY (id) );
INSERT INTO t(id) SELECT generate_series(1,1000);
PREPARE plane4(int) AS DELETE FROM t WHERE id IN (select 1);
SELECT outline.set_hint('DELETE FROM t WHERE id IN (select 1);', 'SeqScan(t)');
EXPLAIN EXECUTE plane4(4);
SELECT outline.delete('DELETE FROM t WHERE id IN (select 1);');
DROP TABLE t;

    В выведенном плане запроса должна использоваться схема последовательного сканирования таблицы (Seq Scan):

                            QUERY PLAN
--------------------------------------------------------
Delete on t  (cost=0.00..41.88 rows=1 width=6)
->  Seq Scan on t  (cost=0.00..41.88 rows=1 width=6)
        Filter: (id = $1)
(3 rows)

Пересчет идентификатора запроса#

Расширение включает функцию outline.recompute_query_ids, которая позволяет использовать текст запроса в таблице outline_ref для пересчета идентификатора запроса в таблице outlines.

При пересоздании таблиц меняется oid таблицы, что приводит к изменению идентификатора запроса, и неработоспособности старых правил подмены и подсказок. Для решения этой проблемы была добавлена функция outline.recompute_query_ids, которая пересчитывает идентификатор запроса, используя текст запроса. Это позволяет присоединить существующие правила к запросам на новых таблицах.

Примечание: Вызов функции outline.recompute_query_ids приводит к инвалидации всех правил подмены и подсказок, которые были созданы до вызова этой функции.