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

Павел Семёнов

Contents

Когда обновлять статистику
Способы сбора статистики
Сбор статистики партиционированных таблиц
Целевой объем статистики
Собираемая статистика
- Размеры таблиц: таблица pg_class
- Статистика по столбцам: таблица pg_statistic и представление pg_stats

В этой статье описываются основы сбора статистики в Greengage DB (на базе Greenplum).

Greengage DB использует метаданные и статистические данные для оптимизации выполнения запросов. При выполнении запроса исполнитель проходит по его шагам в порядке, заданном планировщиком. Это могут быть сканирование, фильтрация, соединение, перемещение и другие шаги. Каждый шаг имеет стоимость, которая оценивает предполагаемую нагрузку на CPU, память, диск и сеть. Стоимость — абстрактное числовое значение, оно отражает относительную ресурсоемкость выполнения шага. Для наилучшей производительности стоимостной оптимизатор запросов оценивает возможные способы выполнения запроса и выбирает тот, у которого суммарная стоимость минимальна.

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

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

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

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

Для вновь созданных таблиц статистика не хранится до тех пор, пока она не будет собрана одним из доступных способов.

Проверить наличие статистики можно через представление gp_toolkit.gp_stats_missing:

SELECT * FROM gp_toolkit.gp_stats_missing;

Результат показывает таблицы с отсутствующей статистикой:

 smischema | smitable  | smisize | smicols | smirecs
-----------+-----------+---------+---------+---------
 public    | orders    | f       |       4 |       0
 public    | customers | f       |       4 |       0
(2 rows)

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

Обновление статистики обычно нужно в следующих случаях:

После загрузки большого объема данных в таблицу.
После создания индексов.
После DML-операций, таких как INSERT, UPDATE или DELETE, затрагивающих значительные объемы данных.

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

ПРИМЕЧАНИЕ

Косвенно на устаревание статистики могут указывать:

Значительное расхождение reltuples в системной таблице pg_catalog.pg_class (оценки числа строк) и фактического числа строк — результата SELECT COUNT(*). Имейте в виду, что подсчет строк в больших таблицах может быть ресурсоемким.
Давняя дата последнего сбора статистики в столбцах last_analyze и last_autoanalyze системного представления pg_catalog.pg_stat_all_tables. Однако, если таблица изменяется редко, это может не влиять на производительность.

Greengage DB предоставляет несколько инструментов и механизмов для сбора и управления статистикой:

SQL-команда ANALYZE.
Утилита analyzedb.
Автоматический сбор статистики.
SQL-команда VACUUM ANALYZE, которая удаляет устаревшие строки таблицы и после этого обновляет ее статистику. Узнайте больше в статье Удаление устаревших строк с помощью VACUUM.

Предварительные требования

Для выполнения команд, описанных в этом разделе, подключитесь к мастер-хосту Greengage DB с помощью psql, как описано в разделе Подключение к Greengage DB с использованием psql.

SQL-команда ANALYZE собирает статистику по содержимому таблиц и столбцов. Ее можно использовать для сбора статистики на разных уровнях:

Анализ всех таблиц, доступных пользователю:
```
ANALYZE;
```
Такой вызов собирает статистику по всем обычным (не foreign) таблицам текущей базы данных. В больших базах это может занять много времени, поэтому такой способ обычно нужен при подготовке всей системы к оптимизации запросов, например, после загрузки больших объемов данных или миграции.
Анализ конкретной таблицы:
```
ANALYZE orders;
```
Обновляет статистику по всем столбцам указанной таблицы. Рекомендуется после массовых операций вставки, обновления или удаления данных в таблице.
Анализ отдельных столбцов таблицы:
```
ANALYZE orders (total, order_date)
```
Обновляет статистику только для указанных столбцов. Применяется, если известно, что изменились лишь отдельные столбцы, особенно если они используются в WHERE, JOIN или GROUP BY.

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

ANALYZE VERBOSE;

Блокировка

При выполнении ANALYZE на таблицу устанавливается блокировка Share Update Exclusive. Она блокирует параллельные изменения схемы, а также операции VACUUM (без FULL и только на heap-таблицах) и ANALYZE. Параллельные DML-операции не блокируются, однако не рекомендуется запускать ANALYZE одновременно с масштабными изменениями данных или созданием индексов. Это может снизить точность собранной статистики и повлиять на производительность.

ПРИМЕЧАНИЕ

При анализе партиционированных таблиц поведение ANALYZE зависит от того, анализируется родительская таблица или отдельные партиции. Подробности приведены в разделе Сбор статистики партиционированных таблиц.

Утилита analyzedb запускает сбор статистики в Greengage DB из командной строки. Она обеспечивает большую гибкость и возможности автоматизации, чем SQL-команда ANALYZE, и полезна для работы с большими базами данных или при использовании скриптов.

Чтобы проанализировать все таблицы в базе данных, запустите на мастер-хосте утилиту analyzedb, указав имя базы в опции -d:

$ analyzedb -d books_store

Команда выведет список таблиц и партиций, для которых будет собрана статистика. Введите y и нажмите Enter, чтобы подтвердить сбор статистики. Чтобы пропустить подтверждение, используйте опцию -a:

$ analyzedb -d books_store -a

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

Чтобы указать количество таблиц, обрабатываемых параллельно, используйте опцию -p. Допустимые значения: от 1 до 10:

$ analyzedb -d books_store -p 10

ПРИМЕЧАНИЕ

Это число отражает количество параллельно обрабатываемых партиций при анализе партиционированных таблиц.

Чтобы автоматически пропускать таблицы с актуальной статистикой, analyzedb сохраняет информацию о состоянии оптимизированных для добавления (Append-optimized, AO) таблиц в подкаталоге db_analyze каталога данных мастера. При следующем запуске утилита использует эту информацию, чтобы определить, были ли изменения в таблице или партиции. Если изменений нет, то собирать статистику для этой таблицы или партиции не нужно. По умолчанию analyzedb пропускает сбор статистики, когда в нем нет необходимости. Чтобы принудительно обновить статистику независимо от ее актуальности, используйте опцию --full:

$ analyzedb -d books_store --full

ПРИМЕЧАНИЕ

Heap-таблицы всегда анализируются вне зависимости от изменений.

Следующие опции analyzedb позволяют выбирать объекты для сбора статистики:

-s — анализ всех таблиц в схеме:
```
$ analyzedb -d books_store -s sales
```
-t — анализ конкретной таблицы:
```
$ analyzedb -d books_store -t sales.orders
```
-i — анализ отдельных столбцов таблицы:
```
$ analyzedb -d books_store -t sales.orders -i order_date,total
```
-x — исключение столбцов из анализа:
```
$ analyzedb -d books_store -t sales.orders -x comment
```

Опция -f (--file) позволяет указать список таблиц и столбцов для анализа в файле. Файл конфигурации analyzedb содержит имена таблиц для анализа, каждое на отдельной строке. Дополнительно вы можете указать список столбцов для анализа или исключить столбцы из анализа, используя опции -i и -x.

<schema>.<table>
<schema>.<table> -i <column1>,<column2>,...
<schema>.<table> -x <column1>,<column2>,...

Пример:

public.customers
sales.orders -i order_date, total
public.books -x year

Чтобы собрать статистику как указано в файле:

$ analyzedb  -d books_store -f <analyze_file_name>

где <analyze_file_name> — имя конфигурационного файла для analyzedb.

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

Параметр gp_autostats_mode определяет, когда запускается автоматический сбор статистики. Допустимые значения:

NONE — отключает автоматический сбор статистики.
ON_NO_STAT (по умолчанию) — собирает статистику для таблиц, у которых она отсутствует, после операций загрузки данных:
- CREATE TABLE AS SELECT
- INSERT
- COPY
ON_CHANGE — собирает статистику после операций, перечисленных ниже, если они изменяют число строк, превышающее порог (параметр gp_autostats_on_change_threshold):
- CREATE TABLE AS SELECT
- UPDATE
- DELETE
- INSERT
- COPY

Параметр gp_autostats_on_change_threshold задает количество измененных строк, при котором в режиме ON_CHANGE запускается сбор статистики. Значение по умолчанию — 2147483647 (максимальное значение для 32-битного целого числа со знаком). Такое большое значение фактически отключает автоматический сбор. Поэтому при переходе в режим ON_CHANGE нужно задать подходящий порог для вашей нагрузки. Например, чтобы запускать сбор статистики при изменении более 1000 строк:

$ gpconfig -c gp_autostats_mode -v ON_CHANGE
$ gpconfig -c gp_autostats_on_change_threshold -v 1000
$ gpstop -u

Чтобы отключить автоматический сбор статистики:

$ gpconfig -c gp_autostats_mode -v NONE
$ gpstop -u

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

$ gpconfig -c gp_autostats_allow_nonowner -v on
$ gpstop -u

Параметр gp_autostats_mode применяется только к операциям, выполняемым вне пользовательских функций и процедур. Сбор статистики после операций, выполненных внутри функций и процедур, управляется отдельным параметром gp_autostats_mode_in_functions. Он принимает те же значения, что и gp_autostats_mode: NONE, ON_NO_STAT, ON_CHANGE.

$ gpconfig -c gp_autostats_mode_in_functions -v ON_CHANGE
$ gpstop -u

РЕКОМЕНДАЦИЯ

Чтобы включить логирование событий автоматического сбора статистики, активируйте параметр log_autostats:

$ gpconfig -c log_autostats -v on
$ gpstop -u

Это поможет отслеживать, когда и при каких операциях статистика собирается автоматически.

Партиционированные таблицы имеют свои особенности сбора статистики. Оптимизатор Greengage DB по умолчанию — GPORCA — использует статистику родительской таблицы (root partition) для планирования запросов. В отличие от него, альтернативный планировщик — планировщик Postgres (устаревший) — использует только статистику партиций самого нижнего уровня (leaf-партиций).

По умолчанию команда ANALYZE собирает статистику со всех leaf-партиций и родительской таблицы. Такой полный набор статистики позволяет GPORCA строить эффективные планы выполнения запросов.

Если вы используете только планировщик Postgres (параметр optimizer выключен), вы можете ускорить сбор статистики, отключив его для родительских таблиц. Для этого установите optimizer_analyze_root_partition в значение off:

$ gpconfig -c optimizer -v off
$ gpconfig -c optimizer_analyze_root_partition -v off
$ gpstop -u

При вызове analyzedb добавьте опцию --skip_orca_root_stats, чтобы отключить сбор статистики для родительских таблиц:

$ analyzedb -d books_store --skip_orca_root_stats

Кгда параметр optimizer_analyze_root_partition отключен, вы можете собрать статистику корневой партиции с помощью вызова ANALYZE ROOTPARTITION на партиционированной табилце:

ANALYZE ROOTPARTITION orders;

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

NOTE

При стандартный настройках, когда и optimizer, и optimizer_analyze_root_partition включены, ключевое слово ROOTPARTITION не требуется.

Чтобы оптимизировать сбор статистики на партиционированных таблицах, можно выполнять ANALYZE только на измененных партициях. Утилита analyzedb автоматически проверяет изменения и пропускает неизмененные партиции. Подробнее об этом рассказывается в разделе Утилита analyzedb.

Автоматически сбор статистики партиционированных таблиц

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

Целевой объем статистики определяет уровень детализации собираемой статистики. Он определяет количество значений, хранимых в списке наиболее часто встречающихся значений (Most Common Values, MCVs) и гистограммах для каждого столбца. Greengage DB использует эти данные для оценки распределения значений и выбора эффективных планов выполнения запросов.

По умолчанию целевой объем статистики одинаков для всех таблиц и столбцов. Он задается параметром конфигурации default_statistics_target. Значение по умолчанию — 100, максимальное — 10000.

Чтобы увеличить целевой объем по умолчанию для всей системы:

$ gpconfig -c default_statistics_target -v 200
$ gpstop -u

Увеличение значения позволяет оптимизатору лучше обрабатывать столбцы с неравномерным распределением данных и повысить точность планов. Однако это увеличивает затраты на сбор статистики и может потребовать больше места в системном каталоге.

Вы можете переопределить целевое значение для конкретных столбцов, если требуется более подробная статистика:

ALTER TABLE orders ALTER COLUMN total SET STATISTICS 300;

ПРИМЕЧАНИЕ

Есть два специальных значения параметра:

-1 — использовать значение по умолчанию из default_statistics_target.
0 — отключить сбор статистики для столбца (планировщик игнорирует его).

РЕКОМЕНДАЦИЯ

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

В этом разделе перечислены метрики статистики, собираемой в Greengage DB, и места их хранения.

Greengage DB использует системную таблицу pg_catalog.pg_class для оценки размеров таблиц при планировании запросов. Столбец reltuples хранит оценку количества строк в каждой таблице. Оценка обновляется при выполнении операций ANALYZE и VACUUM.

Чтобы посмотреть оценку числа строк в таблице, найдите ее по имени в столбце relname:

SELECT reltuples FROM pg_class WHERE relname = 'orders';

ПРИМЕЧАНИЕ

Значение может отличаться от фактического числа строк, возвращаемого вызовом SELECT COUNT(*). Если расхождение велико, это признак устаревшей статистики — рекомендуется ее обновить. Подробности приведены в разделе Когда обновлять статистику.

Подробная статистика по столбцам хранится в следующих объектах:

pg_catalog.pg_statistic — внутренняя системная таблица, используемая планировщиком.
pg_catalog.pg_stats — системное представление, удобное для просмотра статистики.

Таблица pg_statistic предоставляет больше технических деталей, а представление pg_stats — более удобную форму для просмотра информации.

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

Ключевые метрики
Метрика	Столбец в pg_stats	Столбец в pg_statistic	Описание
Число различных значений	n_distinct	stadistinct	Используется для оценки селективности соединений и фильтров
Доля NULL	null_frac	stanullfrac	Доля значений NULL в столбце
Наиболее частые значения (MCV)	most_common_vals	stavalues1	Массив наиболее часто встречающихся значений и их частот. Максимальный размер массива определяется целевым объемом статистики
Гистограммы	histogram_bounds	stavalues2	Оценивают распределение значений по диапазонам
Корреляция	correlation	stacorr1	Оценивает упорядоченность значений, влияет на эффективность сканирований по индексам

Представление pg_stats позволяет получить статистику столбца по его имени и имени таблицы:

SELECT null_frac, most_common_vals FROM pg_stats
WHERE tablename = 'orders' AND attname = 'comment';

Результат:

 null_frac | most_common_vals
-----------+-------------------
     0.7   | {NULL, 'no comment', 'ok'}
(1 row)

Таблица pg_statistic использует идентификаторы объектов и номера столбцов, что больше подходит для внутреннего использования системой:

SELECT stanullfrac FROM pg_statistic WHERE starelid = 16384 AND staattnum = 4;

Результат:

stanullfrac
-------------
         0.7
(1 row)

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

Использование ресурсных очередей

Удаление устаревших строк с помощью VACUUM

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

Когда обновлять статистику

Способы сбора статистики

Команда ANALYZE

Утилита analyzedb

Автоматический сбор статистики

Сбор статистики партиционированных таблиц

Целевой объем статистики

Собираемая статистика

Размеры таблиц: таблица pg_class

Статистика по столбцам: таблица pg_statistic и представление pg_stats