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

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

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

       relname        | relkind | reltuples | relpages
----------------------+---------+-----------+----------
 tenk1                | r       |     10000 |      345
 tenk1_hundred        | i       |     10000 |       11
 tenk1_thous_tenthous | i       |     10000 |       30
 tenk1_unique1        | i       |     10000 |       30
 tenk1_unique2        | i       |     10000 |       30
(5 строк)

Здесь видно, что tenk1 содержит 10000 строк, как и её индексы, однако индексы (что неудивительно) имеют гораздо меньший размер, чем таблица.

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

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

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

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

 attname | inherited | n_distinct |          most_common_vals
---------+-----------+------------+------------------------------------
 name    | f         | -0.5681108 | I- 580                        Ramp+
         |           |            | I- 880                        Ramp+
         |           |            | Sp Railroad                       +
         |           |            | I- 580                            +
         |           |            | I- 680                        Ramp+
         |           |            | I- 80                         Ramp+
         |           |            | 14th                          St  +
         |           |            | I- 880                            +
         |           |            | Mac Arthur                    Blvd+
         |           |            | Mission                       Blvd+
...
 name    | t         |    -0.5125 | I- 580                        Ramp+
         |           |            | I- 880                        Ramp+
         |           |            | I- 580                            +
         |           |            | I- 680                        Ramp+
         |           |            | I- 80                         Ramp+
         |           |            | Sp Railroad                       +
         |           |            | I- 880                            +
         |           |            | State Hwy 13                  Ramp+
         |           |            | I- 80                             +
         |           |            | State Hwy 24                  Ramp+
...
 thepath | f         |          0 |
 thepath | t         |          0 |
(4 строки)

Обратите внимание, что для одного и того же столбца выводятся две строки, одна из которых соответствует полной иерархии наследования, начинающейся с таблицы road (наследуемые=t), и еще одно, включающее только road саму таблицу (наследуемые=f). (Для краткости приведены только первые десять наиболее распространенных значений для столбца name column.)

Объем информации, сохраняемой в pg_statistic в ANALYZE, в частности, максимальное количество записей в most_common_vals и histogram_bounds ALTER TABLE SET STATISTICS или глобально путем установки значения переменной конфигурации default_statistics_target . В настоящее время лимит по умолчанию составляет 100 записей. Увеличение данного лимита может позволить планировщику формировать более точные оценки, особенно для столбцов с неравномерным распределением данных, ценой потребления большего объема дискового пространства в pg_statistic и некоторого увеличения времени на вычисление оценок. И наоборот, более низкий лимит может быть достаточным для столбцов с простым распределением данных.

Дополнительные сведения об использовании статистики планировщиком можно найти в Глава 7.18.

2.11.2.2. Расширенная статистика #

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

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

Объекты статистики создаются с помощью команды CREATE STATISTICS команда. Создание такого объекта лишь формирует запись в каталоге, указывающую на необходимость сбора статистических данных. Фактический сбор данных выполняется функцией ANALYZE (либо вручную с помощью команды, либо в фоновом режиме процессом автоматического анализа). Собранные значения можно изучить в pg_statistic_ext_data каталоге.

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

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

2.11.2.2.1. Функциональные зависимости #

Простейший вид расширенной статистики отслеживает функциональные зависимости, — концепция, используемая в определениях нормальных форм базы данных. Принято говорить, что столбец b находится в функциональной зависимости от столбца a если знания значения a достаточно для определения значения b, то есть отсутствуют две строки с одинаковым значением a но с различными значениями b. В полностью нормализованной базе данных функциональные зависимости должны существовать только для первичных ключей и суперключей. Однако на практике многие наборы данных не являются полностью нормализованными по различным причинам; распространенным примером является преднамеренная денормализация в целях повышения производительности. Даже в полностью нормализованной базе данных может наблюдаться частичная корреляция между некоторыми столбцами, которая может быть выражена как частичная функциональная зависимость.

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

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

Ниже приведен пример сбора статистики функциональных зависимостей:

CREATE STATISTICS stts (dependencies) ON city, zip FROM zipcodes;

ANALYZE zipcodes;

SELECT stxname, stxkeys, stxddependencies
  FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
  WHERE stxname = 'stts';
 stxname | stxkeys |             stxddependencies
---------+---------+------------------------------------------
 stts    | 1 5     | {"1 => 5": 1.000000, "5 => 1": 0.423130}
(1 row)

Здесь видно, что столбец 1 (почтовый индекс) полностью определяет столбец 5 (город), поэтому коэффициент равен 1,0, тогда как город определяет почтовый индекс только примерно в 42% случаев; это означает, что существует много городов (58%), представленных более чем одним почтовым индексом.

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

2.11.2.2.1.1. Ограничения функциональных зависимостей #

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

При расчете оценки с использованием функциональных зависимостей планировщик предполагает, что условия для задействованных столбцов совместимы и, следовательно, избыточны. Если они несовместимы, правильная оценка должна составлять ноль строк, но такая возможность не рассматривается. Например, для такого запроса, как

SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '94105';

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

SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '90210';

хотя в действительности этот запрос не вернет ни одной строки. Тем не менее, статистика функциональных зависимостей не предоставляет достаточно информации для формирования такого вывода.

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

2.11.2.2.2. Подсчет количества различных значений для нескольких столбцов #

Статистика по отдельным столбцам содержит сведения о количестве уникальных значений в каждом из них. Оценки числа уникальных значений при объединении нескольких столбцов (например, в GROUP BY a, b) часто оказываются некорректными, если планировщик располагает статистическими данными только по отдельным столбцам, что приводит к выбору неоптимальных планов выполнения.

Для повышения точности таких оценок ANALYZE может собирать n-distinct статистику для групп столбцов. Как и ранее, сбор таких данных для всех возможных сочетаний столбцов нецелесообразен, поэтому они собираются только для тех групп столбцов, которые указаны в объекте статистики, определенном с параметром ndistinct option. Сбор данных будет осуществляться для каждой возможной комбинации двух или более столбцов из набора указанных столбцов.

В продолжение предыдущего примера, количество уникальных значений (n-distinct) в таблице почтовых индексов может выглядеть следующим образом:

CREATE STATISTICS stts2 (ndistinct) ON city, state, zip FROM zipcodes;

ANALYZE zipcodes;

SELECT stxkeys AS k, stxdndistinct AS nd
  FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
  WHERE stxname = 'stts2';
-[ RECORD 1 ]--------------------------------------------------------
k  | 1 2 5
nd | {"1, 2": 33178, "1, 5": 33178, "2, 5": 27435, "1, 2, 5": 33178}
(1 row)

Это указывает на наличие трех комбинаций столбцов, имеющих 33178 уникальных значений: Почтовый индекс и штат; Почтовый индекс и город; а также Почтовый индекс, город и штат (тот факт, что все эти значения равны, является ожидаемым, поскольку Почтовый индекс сам по себе уникален в данной таблице). С другой стороны, комбинация города и штата имеет только 27435 уникальных значений.

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

2.11.2.2.3. Многомерные списки наиболее частых значений (MCV) #

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

Для повышения точности таких оценок ANALYZE позволяет собирать списки MCV для сочетаний нескольких столбцов. Аналогично функциональным зависимостям и коэффициентам n-distinct, создание таких списков для каждой возможной комбинации столбцов нецелесообразно. Это тем более актуально в данном случае, так как список MCV (в отличие от функциональных зависимостей и коэффициентов n-distinct) действительно хранит наиболее распространенные значения столбцов. Таким образом, сбор данных осуществляется только для тех групп столбцов, которые совместно фигурируют в объекте статистики, определенном с помощью mcv option.

В развитие предыдущего примера, список MCV для таблицы почтовых индексов может иметь следующий вид (в отличие от более простых типов статистики, для просмотра содержимого MCV требуется вызов функции):

CREATE STATISTICS stts3 (mcv) ON city, state FROM zipcodes;

ANALYZE zipcodes;

SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid),
                pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3';

 index |         values         | nulls | frequency | base_frequency
-------+------------------------+-------+-----------+----------------
     0 | {Washington, DC}       | {f,f} |  0.003467 |        2.7e-05
     1 | {Apo, AE}              | {f,f} |  0.003067 |        1.9e-05
     2 | {Houston, TX}          | {f,f} |  0.002167 |       0.000133
     3 | {El Paso, TX}          | {f,f} |     0.002 |       0.000113
     4 | {New York, NY}         | {f,f} |  0.001967 |       0.000114
     5 | {Atlanta, GA}          | {f,f} |  0.001633 |        3.3e-05
     6 | {Sacramento, CA}       | {f,f} |  0.001433 |        7.8e-05
     7 | {Miami, FL}            | {f,f} |    0.0014 |          6e-05
     8 | {Dallas, TX}           | {f,f} |  0.001367 |        8.8e-05
     9 | {Chicago, IL}          | {f,f} |  0.001333 |        5.1e-05
   ...
(99 строк)

Это указывает на то, что наиболее распространенным сочетанием города и штата является Вашингтон в округе Колумбия (DC), с фактической частотой (в выборке) около 0,35%. Базовая частота сочетания (вычисленная на основе простых частот отдельных столбцов) составляет всего 0,0027%, что приводит к занижению оценки на два порядка.

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

Наверх