F.44. pg_qualstats#

F.44. pg_qualstats

F.44. pg_qualstats

F.44.1. О pg_qualstats

Версия: 2.1.1

GitHub

Авторское право © Dalibo, Команда PoWA

F.44.2. Краткое описание

pg_qualstats - это расширение Tantor SE, ведущее статистику по предикатам, найденным в операторах WHERE и в предложениях JOIN.

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

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

Расширение работает, ища известные шаблоны в запросах. В настоящее время это включает:

  • Binary OpExpr где хотя бы одна сторона является столбцом из таблицы. При возможности предикат будет переставлен таким образом, чтобы выражения CONST OP VAR превращались в VAR COMMUTED_OP CONST. Члены выражений AND и OR считаются отдельными записями. Например: WHERE column1 = 2, WHERE column1 = column2, WHERE 3 = column3

  • ScalarArrayOpExpr, где левая сторона - VAR, а правая сторона - массивная константа. Они будут учитываться один раз для каждого элемента в массиве. Например, WHERE column1 IN (2, 3) будет учитываться как 2 вхождения для пары операторов (column1, =)

  • BooleanTest где выражение является простым логическим столбцом ссылка Например: WHERE column1 IS TRUE Пожалуйста, обратите внимание, что такие предложения, как WHERE columns1, WHERE NOT column1 не будут обрабатываться pg_qualstats (пока)

Это расширение также сохраняет текст первого запроса, как есть, для каждого отдельного queryid, выполненного, с ограничением в pg_qualstats.max записей.

Пожалуйста, обратите внимание, что собранные данные не сохраняются при перезапуске Tantor SE сервера.

F.44.3. Установка

Добавьте pg_qualstats в общие библиотеки предварительной загрузки:

   shared_preload_libraries = 'pg_qualstats'

F.44.4. Конфигурация

Следующие GUC-параметры могут быть настроены в файле postgresql.conf:

  • pg_qualstats.enabled (boolean, по умолчанию true): включено ли pg_qualstats или нет

  • pg_qualstats.track_constants (логическое, по умолчанию true): определяет, должен ли pg_qualstats отслеживать каждое значение константы индивидуально. Отключение этого GUC значительно сократит количество записей, необходимых для отслеживания предикатов.

  • pg_qualstats.max: максимальное количество отслеживаемых предикатов и текстов запросов (по умолчанию 1000)

  • pg_qualstats.resolve_oids (boolean, default false): определяет, должен ли pg_qualstats разрешать идентификаторы объектов (oids) во время выполнения запроса или просто сохранять их. Включение этого параметра значительно упрощает анализ данных, поскольку не требуется подключение к базе данных, где был выполнен запрос, но это потребует гораздо больше места (624 байта на запись вместо 176). Кроме того, это потребует некоторых поисков в каталоге, которые не являются бесплатными.

  • pg_qualstats.track_pg_catalog (boolean, default false): определяет, должен ли pg_qualstats вычислять предикаты для объектов в схеме pg_catalog.

  • pg_qualstats.sample_rate (double, по умолчанию -1): доля запросов, которые должны быть отобраны для анализа. Например, 0.1 означает, что только один из десяти запросов будет отобран для анализа. Значение по умолчанию (-1) означает автоматический выбор и приводит к значению 1 / max_connections, так что статистически проблемы с параллелизмом будут редкими.

F.44.5. Обновление расширения

Обратите внимание, что, поскольку все расширения настроены в shared_preload_libraries, большинство изменений применяются только после перезапуска Tantor SE с новой версией общей библиотеки. Сами объекты расширения предоставляют только SQL-оболочки для доступа к внутренним структурам данных.

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

F.44.6. Использование

  • Создайте расширение в любой базе данных:

   CREATE EXTENSION pg_qualstats;

F.44.6.1. Функции

Расширение определяет следующие функции:

  • pg_qualstats: возвращает количество для каждого квалификатора, идентифицированного хешем выражения. Этот хеш идентифицирует каждое выражение.

    • userid: идентификатор пользователя, который выполнил запрос.

    • dbid: идентификатор oid базы данных, в которой был выполнен запрос.

    • lrelid, lattnum: идентификатор отношения и номер атрибута VAR на левой стороне, если есть.

    • opno: идентификатор оператора, используемого в выражении

    • rrelid, rattnum: идентификатор отношения и номер атрибута VAR справа, если есть.

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

    • uniquequalid: уникальный идентификатор родительского выражения AND, если таковое имеется. Этот идентификатор вычисляется включая константы.

    • qualnodeid: нормализованный идентификатор этого простого предиката. Этот идентификатор вычисляется без учета констант.

    • uniquequalnodeid: уникальный идентификатор этого простого предиката. Этот идентификатор вычисляется включая константы.

    • occurences: количество раз, когда этот предикат был вызван, то есть количество связанных запросов выполнения.

    • execution_count: количество раз, когда этот предикат был выполнен, то есть количество обработанных им строк.

    • nbfiltered: количество кортежей, отброшенных этим предикатом.

    • constant_position: позиция константы в исходной строке запроса, как сообщает парсер.

    • queryid: если установлено расширение pg_stats_statements, то это идентификатор запроса (queryid), иначе NULL.

    • constvalue: строковое представление правой части константы, если таковая имеется, обрезанное до 80 байт. Требуется быть суперпользователем или членом pg_read_all_stats (начиная с Tantor SE 10), вместо этого будет показано <недостаточно привилегий>.

    • eval_type: тип оценки. f для предиката, оцениваемого после сканирования или i для предиката индекса.

    Пример:

ro=# select * from pg_qualstats;
 userid │ dbid  │ lrelid │ lattnum │ opno │ rrelid │ rattnum │ qualid │ uniquequalid │ qualnodeid │ uniquequalnodeid │ occurences │ execution_count │ nbfiltered │ constant_position │ queryid │   constvalue   │ eval_type
--------+-------+--------+---------+------+--------+---------+--------+--------------+------------+------------------+------------+-----------------+------------+-------------------+---------+----------------+-----------
     10 │ 16384 │  16385 │       2 │   98 │ <NULL> │  <NULL> │ <NULL> │       <NULL> │  115075651 │       1858640877 │          1 │          100000 │      99999 │                29 │  <NULL> │ 'line 1'::text │ f
     10 │ 16384 │  16391 │       2 │   98 │  16385 │       2 │ <NULL> │       <NULL> │  497379130 │        497379130 │          1 │               0 │          0 │            <NULL> │  <NULL> │                │ f
  • pg_qualstats_index_advisor(min_filter, min_selectivity, forbidden_am): Выполнить глобальное предложение индекса. По умолчанию будут рассматриваться только предикаты, фильтрующие как минимум 1000 строк и в среднем 30% строк, но это можно передать в качестве параметра. Вы также можете предоставить массив методов доступа к индексам, если хотите избежать некоторых. Например, в Tantor SE 9.6 и ранее, hash индексы будут игнорироваться, так как они еще не были безопасны при сбоях.

    Пример:

SELECT v
  FROM json_array_elements(
    pg_qualstats_index_advisor(min_filter => 50)->'indexes') v
  ORDER BY v::text COLLATE "C";
                               v
---------------------------------------------------------------
 "CREATE INDEX ON public.adv USING btree (id1)"
 "CREATE INDEX ON public.adv USING btree (val, id1, id2, id3)"
 "CREATE INDEX ON public.pgqs USING btree (id)"
(3 rows)

SELECT v
  FROM json_array_elements(
    pg_qualstats_index_advisor(min_filter => 50)->'unoptimised') v
  ORDER BY v::text COLLATE "C";
        v
-----------------
 "adv.val ~~* ?"
(1 row)
  • pg_qualstats_deparse_qual: форматирует сохраненное предикат в виде tablename.columname operatorname ?. Это в основном для глобального советника по индексам.

  • pg_qualstats_get_idx_col: для данного предиката получить имя базового столбца и все возможные классы операторов. Это в основном для глобального советника по индексам.

  • pg_qualstats_get_qualnode_rel: для данного предиката возвращает базовую таблицу с полным указанием. Это в основном для глобального советника по индексам

  • pg_qualstats_example_queries: возвращает все сохраненные тексты запросов.

  • pg_qualstats_example_query: возвращает сохраненный текст запроса для данного queryid, если он есть, в противном случае NULL.

  • pg_qualstats_names: возвращает все сохраненные тексты запросов.

  • pg_qualstats_reset: сбросить внутренние счетчики и забыть о каждом встреченном условии.

F.44.6.2. Представления

В дополнение к этому, расширение определяет несколько представлений на основе функции pg_qualstats:

  • pg_qualstats: фильтрует вызовы pg_qualstats() по текущей базе данных.

  • pg_qualstats_pretty: выполняет соответствующие соединения для отображения читаемой агрегированной формы для каждого атрибута из представления pg_qualstats

    Пример:

ro=# select * from pg_qualstats_pretty;
 left_schema |    left_table    | left_column |   operator   | right_schema | right_table | right_column | occurences | execution_count | nbfiltered
-------------+------------------+-------------+--------------+--------------+-------------+--------------+------------+-----------------+------------
 public      | pgbench_accounts | aid         | pg_catalog.= |              |             |              |          5 |         5000000 |    4999995
 public      | pgbench_tellers  | tid         | pg_catalog.= |              |             |              |         10 |        10000000 |    9999990
 public      | pgbench_branches | bid         | pg_catalog.= |              |             |              |         10 |         2000000 |    1999990
 public      | t1               | id          | pg_catalog.= | public       | t2          | id_t1        |          1 |           10000 |       9999
  • pg_qualstats_all: суммирует количество для каждой пары атрибут / оператор, независимо от его позиции в качестве операнда (LEFT или RIGHT), группируя вместе атрибуты, используемые в AND выражениях.

    Пример:

    ro=# select * from pg_qualstats_all;
     dbid  | relid | userid | queryid | attnums | opno | qualid | occurences | execution_count | nbfiltered | qualnodeid
    -------+-------+--------+---------+---------+------+--------+------------+-----------------+------------+------------
     16384 | 16385 |     10 |         | {2}     |   98 |        |          1 |          100000 |      99999 |  115075651
     16384 | 16391 |     10 |         | {2}     |   98 |        |          2 |               0 |          0 |  497379130
    
  • pg_qualstats_by_query: возвращает только предикаты в форме VAR OPERATOR CONSTANT, агрегированные по queryid.