Сбор статистики с помощью ANALYZE

Евгения Кузина

Статистика базы данных играет важную роль в повышении производительности. Команда ANALYZE собирает статистику о содержимом таблиц базы данных. Планировщик запросов использует эту статистику для создания эффективных планов запросов.

Команда ANALYZE

Обзор

Команда ANALYZE собирает статистику о содержимом таблиц базы данных и сохраняет результаты в системном каталоге pg_statistic. В дальнейшем планировщик запросов использует эту статистику для определения наиболее эффективных планов выполнения запросов. ANALYZE имеет следующий синтаксис:

ANALYZE [ ( <параметр> [, ...] ) ] [ <таблицы_и_столбцы> [, ...] ]
ANALYZE [ VERBOSE ] [ <таблицы_и_столбцы> [, ...] ]

где <параметр> может быть одним из:

    VERBOSE [ <логическое_значение> ]
    SKIP_LOCKED [ <логическое_значение> ]

и <таблицы_и_столбцы> это:

    <имя_таблицы> [ ( <имя_столбца> [, ...] ) ]
Параметры команды ANALYZE

VERBOSE

Включает отображение сообщений о ходе выполнения. Если указано VERBOSE, ANALYZE выдает сообщения о ходе выполнения: выводит какая таблица в данный момент обрабатывается и печатает различную статистику

SKIP_LOCKED

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

<логическое_значение>

Определяет, следует ли включить или выключить выбранную опцию. Можно написать TRUE, ON или 1, чтобы включить эту опцию, и FALSE, OFF или 0, чтобы отключить ее. Если логическое значение опущено, предполагается TRUE

<имя_таблицы>

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

<имя_столбца>

Имя определенного столбца для анализа. По умолчанию анализируются все столбцы

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

Примеры

ANALYZE VERBOSE для базы данных

 

Чтобы запустить ANALYZE в базе данных, сначала подключитесь к этой базе данных. Например, вы можете сделать это, используя psql:

\c example

Запустите команду ANALYZE VERBOSE:

ANALYZE VERBOSE;

Результат:

INFO:  analyzing "public.pgbench_accounts"
INFO:  "pgbench_accounts": scanned 30000 of 81968 pages, containing 1830000 live rows and 0 dead rows; 30000 rows in sample, 5000048 estimated total rows
INFO:  analyzing "public.pgbench_branches"
INFO:  "pgbench_branches": scanned 1 of 1 pages, containing 50 live rows and 0 dead rows; 50 rows in sample, 50 estimated total rows
INFO:  analyzing "public.pgbench_history"
INFO:  "pgbench_history": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "public.pgbench_tellers"
INFO:  "pgbench_tellers": scanned 3 of 3 pages, containing 500 live rows and 0 dead rows; 500 rows in sample, 500 estimated total rows
INFO:  analyzing "pg_catalog.pg_type"
INFO:  "pg_type": scanned 15 of 15 pages, containing 609 live rows and 0 dead rows; 609 rows in sample, 609 estimated total rows
INFO:  analyzing "pg_catalog.pg_foreign_table"
INFO:  "pg_foreign_table": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_authid"
INFO:  "pg_authid": scanned 1 of 1 pages, containing 16 live rows and 3 dead rows; 16 rows in sample, 16 estimated total rows
INFO:  analyzing "pg_catalog.pg_statistic_ext_data"
INFO:  "pg_statistic_ext_data": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
...

Поскольку используется опция VERBOSE, выходные данные содержат подробную информацию об анализируемых отношениях.

ANALYZE для таблицы

 

Для анализа таблицы укажите её имя в команде:

ANALYZE pgbench_accounts;
ANALYZE для определенных столбцов

 

Для анализа столбца укажите имя таблицы и имя столбца в скобках:

ANALYZE pgbench_accounts (bid, abalance);

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

Автовакуум запускает ANALYZE для таблиц, когда они впервые заполняются данными, а также если они изменяются во время обычных операций. Если автовакуум отключен (что не рекомендуется), следует периодически запускать ANALYZE вручную, а также дополнительно после серьезных изменений в содержимом таблиц. Точная статистика помогает планировщику выбрать наиболее подходящий план запроса и повысить скорость обработки запросов. Общая стратегия для баз данных, предназначенных в основном для чтения, — запускать команды VACUUM и ANALYZE один раз в день во время малой загрузки. Этого недостаточно, если база данных активно обновляется.

Для ANALYZE требуется только блокировка на чтение целевой таблицы, поэтому он может выполняться параллельно с другими операциями над таблицей.

Если таблица пуста, ANALYZE не записывает новую статистику для этой таблицы. Существующая статистика сохраняется.

Каждый сервер, на котором выполняется ANALYZE, будет записывать информацию о прогрессе выполнения команды в представлении pg_stat_progress_analyze. Для получения дополнительной информации об этом обратитесь к статье ANALYZE progress reporting.

Конфигурационные параметры, влияющие на ANALYZE

Статистика, собранная с помощью ANALYZE, обычно включает список наиболее распространенных значений и гистограмму, показывающую приблизительное распределение данных в каждом столбце. Один или оба из этих статистических показателей могут быть опущены, если они бесполезны или если тип данных столбца не поддерживает соответствующие операторы. Например, в столбце с уникальными ключами нет общих наиболее распространенных значений.

Для больших таблиц ANALYZE берет случайную выборку содержимого таблицы, а не проверяет каждую строку. Это позволяет анализировать даже очень большие таблицы за короткий промежуток времени. Обратите внимание, что статистика является приблизительной и будет немного меняться каждый раз при запуске ANALYZE, и в том случае, когда фактическое содержимое таблицы не изменилось. Это приводит к небольшим изменениям в предполагаемых затратах планировщика, отображаемых EXPLAIN. В редких случаях такой недетерминизм может привести к изменению выбора плана запроса планировщиком после запуска ANALYZE. Чтобы избежать этого, увеличьте объем статистики, собираемой ANALYZE, как описано ниже.

Объемом статистики можно управлять, установив конфигурационный параметр default_statistics_target. Используйте раздел ADPG configurations на вкладке Clusters → Кластер ADPG → Services → ADPG → Primary configuration в пользовательском интерфейсе ADCM, чтобы указать этот параметр (см. Конфигурационные параметры). Также можно установить целевое значение статистики для каждого столбца с помощью ALTER TABLE…​ ALTER COLUMN…​ SET STATISTICS. См. ALTER TABLE.

ALTER TABLE table1 ALTER COLUMN column1 SET STATISTICS 0;

Целевое значение задает максимальное количество записей в списке наиболее распространенных значений и максимальное количество интервалов в гистограмме. Целевое значение по умолчанию — 100, но его можно увеличить или уменьшить, чтобы найти баланс между точностью оценок планировщика и временем, необходимым для запуска ANALYZE, а также объемом статистики в pg_statistic. Установка целевого значения равным 0 отключает сбор статистики для этого столбца. Это может быть полезно для столбцов, которые никогда не используются как часть выражений WHERE, GROUP BY или ORDER BY, поскольку планировщик не использует статистику по таким столбцам.

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

Второе значение, оцениваемое с помощью ANALYZE, — это количество различных значений, которые присутствуют в каждом столбце. Поскольку исследуется только подмножество строк, эта оценка может быть весьма неточной даже при максимально возможном целевом значении статистики. Если это приводит к неправильным планам запросов, более точное значение можно определить вручную и установить с помощью команды: ALTER TABLE…​ ALTER COLUMN…​ SET (n_distinct = <value>).

ALTER TABLE table2 ALTER COLUMN column2 SET (n_distinct = 500);

ANALYZE для секционированных и дочерних таблиц

Если у таблицы есть наследуемые дочерние элементы, ANALYZE собирает два набора статистики: один только по строкам родительской таблицы, а второй включает строки родительской таблицы и всех её дочерних элементов. Планировщик использует второй набор статистики при обработке запросов, оперирующих всем деревом наследования. В этом случае дочерние таблицы по отдельности не анализируются. Процесс автовакуума учитывает только вставки или обновления самой родительской таблицы при принятии решения об автоматическом запуске ANALYZE для этой таблицы. Когда таблица редко обновляется, статистика будет устаревшей, если не запускать ANALYZE вручную.

Для секционированных таблиц ANALYZE собирает статистику, выбирая строки из всех секций. Кроме того, он будет рекурсивно посещать каждую секцию и обновлять её статистику. Каждая секция анализируется только один раз, даже при многоуровневом секционировании. ANALYZE не собирает статистику по секционированной таблице (без данных из её секций), так как при секционировании она пуста.

Автовакуум не обрабатывает секционированные таблицы и родительские таблицы, если были изменены только дочерние таблицы или секции. Обычно необходимо периодически запускать ANALYZE вручную, чтобы поддерживать актуальность статистики иерархии таблиц.

Если какие-либо дочерние таблицы или секции являются внешними таблицами и их обертки внешних данных не поддерживают ANALYZE, эти таблицы игнорируются при сборе статистики.

Статистика, используемая планировщиком

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

Статистика по одному столбцу

Одним из компонентов статистики является общее количество записей и количество дисковых блоков, занимаемых каждой таблицей и индексом. Таблица pg_class содержит эту информацию в столбцах reltuples и relpages.

SELECT relname, relkind, reltuples, relpages
FROM pg_class
WHERE relname LIKE 'pgbench_accounts%';

Результат:

        relname        | relkind |  reltuples   | relpages
-----------------------+---------+--------------+----------
 pgbench_accounts      | r       | 5.000048e+06 |    81968
 pgbench_accounts_pkey | i       | 5.000048e+06 |    13713

Таблица pgbench_accounts и её индекс содержат 5.000048e+06 строку, но индекс меньше таблицы.

По соображениям эффективности reltuples и relpages обновляются с помощью VACUUM, ANALYZE и нескольких команд DDL, таких как CREATE INDEX. Операции VACUUM или ANALYZE могут не сканировать всю таблицу, а вычислять приращение reltuples на основе той части таблицы, которую они уже сканировали, что приводит к приблизительному значению. Планировщик масштабирует значения из pg_class, чтобы они соответствовали текущему физическому размеру таблицы, и получает более точное приближение.

Большинство запросов извлекают только часть строк таблицы, ограниченную выражением WHERE. Планировщику необходимо оценить избирательность выражений WHERE, то есть долю строк, соответствующих каждому условию в WHERE. Для этого планировщик использует информацию, хранящуюся в системном каталоге pg_statistic. Команды ANALYZE и VACUUM ANALYZE обновляют записи в pg_statistic, но они всегда приблизительны, даже сразу после обновления.

Вместо того чтобы использовать непосредственно pg_statistic, при изучении статистики лучше обратиться к представлению pg_stats. pg_stats доступно для чтения всем, тогда как pg_statistic — только суперпользователю. Это не позволяет непривилегированным пользователям узнать что-либо из статистики о содержимом таблиц других пользователей. Представление pg_stats ограничено отображением только строк с информацией о тех таблицах, которые текущий пользователь может прочитать.

Можно выполнить следующий запрос к pg_stats, где terminals — это имя таблицы:

SELECT attname, n_distinct,
array_to_string(most_common_vals, E'\n') as most_common_vals
FROM pg_stats
WHERE tablename = 'terminals';

Результат:

   attname     | n_distinct  |  most_common_vals
---------------+-------------+--------------------
 terminal_code |          -1 |
 timezone      | -0.16346154 | Europe/Moscow     +
               |             | Asia/Yekaterinburg+
               |             | Asia/Krasnoyarsk  +
               |             | Asia/Irkutsk      +
               |             | Asia/Yakutsk      +
               |             | Europe/Samara     +
               |             | Asia/Vladivostok  +
               |             | Asia/Novokuznetsk +
               |             | Europe/Volgograd
 terminal_name |          -1 |
 city          | -0.97115386 | Moscow            +
               |             | Ulyanovsk

где:

  • attname — имя столбца, описываемого этой строкой.

  • n_distinct — если значение больше нуля, оно представляет собой предполагаемое количество различных значений в столбце. Если это число меньше нуля, его модуль представляет собой количество различных значений, разделенное на количество строк. Отрицательная форма используется, когда ANALYZE предполагает, что количество различных значений будет увеличиваться по мере роста таблицы; положительная форма — когда предполагается, что столбец имеет фиксированное количество возможных значений. -1 указывает на уникальный столбец, в котором количество различных значений равно количеству строк.

  • most_common_vals — список наиболее распространенных значений в столбце. Null (пусто в приведенном выше результате), если никакие значения не являются более распространенными, чем другие.

Расширенная статистика

Часто можно увидеть медленные запросы с плохими планами выполнения, поскольку несколько столбцов, используемых в запросе, взаимосвязаны. Планировщик обычно предполагает, что несколько условий независимы друг от друга. Обычная статистика, поскольку она рассчитана на отдельные столбцы, не может учитывать корреляцию между столбцами. Однако ADPG/PostgreSQL способен вычислить многовариантную статистику, собирающую такую информацию.

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

Объекты статистики создаются с помощью команды CREATE STATISTICS. При создании такого объекта просто добавляется запись, выражающая необходимость этой статистики. Команда ANALYZE собирает требуемые статистические данные, которые можно просмотреть в каталоге pg_statistic_ext_data.

Самый простой вид расширенной статистики — отслеживание функциональных зависимостей. Эта концепция используется в определениях нормальных форм базы данных. Столбец b функционально зависит от столбца a, если знание значения a достаточно для определения значения b, то есть не существует двух строк с одинаковым значением a, но разными значениями b.

Наличие функциональных зависимостей напрямую влияет на точность оценок в тех или иных запросах. Чтобы сообщить планировщику о функциональных зависимостях, ANALYZE может собирать измерения зависимости между столбцами. Данные собираются только для тех групп столбцов, которые указаны в объекте расширенной статистики, определенном с помощью опции dependencies. Рекомендуется создавать статистику зависимостей только для групп столбцов, которые сильно коррелируют, чтобы избежать ненужных накладных расходов при выполнении ANALYZE и последующем планировании запросов.

Код ниже создает объект расширенной статистики stat1 для сбора статистики функциональных зависимостей в столбцах terminal_name и city таблицы terminals:

CREATE STATISTICS stat1 (dependencies) ON terminal_name, city FROM terminals;

ANALYZE terminals;

Проверьте результат:

SELECT stxname, stxkeys, stxddependencies
FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
WHERE stxname = 'stat1';

Результат:

 stxname | stxkeys |             stxddependencies
---------+---------+------------------------------------------
 stat1   | 2 3     | {"2 => 3": 1.000000, "3 => 2": 0.86751}

Столбец 2 (terminal_name) полностью определяет столбец 3 (city) — коэффициент равен 1.0, тогда как city определяет terminal_name только примерно в 87% всех случаев, то есть существуют города, в которых находятся несколько терминалов.

Дополнительную информацию и примеры многовариантной статистики можно найти в статье How the planner uses statistics.

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней