Skip to main content

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

Версия: 1.2.

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

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

Схема размещения: 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;
    note

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

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

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

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

В тексте запроса (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
note

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

note

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