Совсем недавно чуть-чуть рассказал про параметр PostgreSQL default_statistics_target. Этот параметр нужен будет для того, чтобы понять как сервер PostgreSQL собирает статистику и подбирает оптимальные планы выполнения запросов.
Сейчас предлагаю провести пару небольших экспериментов. Все опыты осуществляются в демобазе.
Различие скорости сбора статистики в зависимости от значения default_statistics_target
Включим печать времени выполнения команд в pslq
postgres@demo=# \timing on
Timing is on.
Теперь посмотрим параметр значение default_statistics_target:
postgres@demo=# show default_statistics_target;
default_statistics_target
---------------------------
100
Как и должно быть по умолчанию — установлено в 100. Запустим команду analyze и посмотрим, как долго она будет работать на демобазе:
postgres@demo=# analyze ;
analyze;
Time: 10797,072 ms (00:10,797)
На мой ВМке такая команда при текущих настройках отработала за 10 секунд. А теперь поменяем значение default_statistics_target на 10 и снова выполним команду analyze:
postgres@demo=# set default_statistics_target = 10;
SET
Time: 1,077 ms
postgres@demo=# analyze ;
ANALYZE
Time: 1039,410 ms (00:01,039)
Одна секунда — разница в десять раз (но эта разница может отличаться). Так что видно, что сокращение параметра default_statistics_target влияет на скорость сбора статистики. Но, естественно, качество собранной статистики тоже ухудшается.
Но на сколько? Попробовал провести эксперимент — выбрать самые объемные таблицы и проверить, на сколько правильно собирается статистика по количеству строк в них с default_statistics_target 10 и 100. Отличие (на моих данных) не получилось вовсе. Примерно одинаково ошибается при 10 и при 100. Но, наверное, это потому, что тест у меня всё-таки не очень обширный. В реальности отличия имеются.
Различие величин массива и гистограммы значений
А теперь посмотрим, почему такая разница в длительности analyze происходит.
Много информации я беру из документации. Вообще, в этой документации много чего хорошо расписано, а я эти эксперименты делаю для себя — чтобы самому лучше разобраться в том, как работает PostgreSQL. Про использование default_statistics_target в статистике можно почитать тут: «Как планировщик использует статистику», «Оптимизация производительности».
Если кратко (как уже было сказано), параметр default_statistics_target:
-
устанавливает максимальную величину количества элементов в массиве самых популярных значений (most_common_values);
-
устанавливает максимальное число корзин в гистограмме (histogram_bounds), в которую попадают остальные значения, не попавшие в массив самых популярных значений;
-
и используется как коэффициент в формуле при формировании выборки строк для анализа данных из таблиц (default_statistics_target * 300).
Относительно последнего пункта — видимо, эта константа 300 забита где-то в исходном коде. Можно будет поискать, но не сейчас. Поэтому этот пункт трогать не буду.
А вот первые два можно и нужно проверить, чтобы закрепить понимание.
Сам эксперимент — посмотрим, что будет отличаться в собранной статистике при различных значениях default_statistics_target.
Сначала установим данный параметр в 10 и соберем статистику:
postgres@demo=# set default_statistics_target = 10;
SET
postgres@demo=# analyze;
ANALYZE
А теперь посмотрим, где лежит массив и гистаграмма. В документации по приведенным ссылкам эта информация есть, поэтому я сразу ее приведу — массив и гистограмма находятся в каталоге pg_statistic (системная таблица, в которой много различных полей). Над ней есть вьюха pg_stats, более простая для анализа. Я буду использовать данные именно из представления pg_stats (её структура тоже описана в документации). Кратко — в этой вьюхе хранится поколоночная статистика из таблиц.
Выполним запрос:
select tablename, attname, most_common_vals, histogram_bounds from pg_stats
where tablename = 'flights' and attname = 'departure_airport' \gx
В нем я получаю информацию по колонке (столбцу) «departure_airport» (аэропорт отправления) из таблицы демобазы «flights» (рейсы):
-[ RECORD 1 ]-----+----------------------------------------------
tablename | flights
attname | departure_airport
most_common_vals | {DME,SVO,LED,VKO,OVB,PEE,KJA,AER,ROV,BZK}
histogram_bounds | {AAQ,CSY,HMA,KHV,MCX,NOJ,OVS,SGC,TJM,URJ,YKS}
Это означает, что в most_common_vals хранится список самых частых значений в этой колонке этой таблицы. То есть, чаще всего аэропортами отправления являлись вот эти десять аэропортов (три буквы — это коды аэропортов, так принято, в интернете описано почему так).
Все остальные значения считаются равномерно распределенными и попадают в корзины гистограммы histogram_bounds. Гистограмма устроена так, что в ней располагается не больше default_statistics_target штук корзин. PostgreSQL распределяет оставшиеся значения из таблицы таким образом, чтобы в каждую корзину гистограммы попало примерно одинаковое количество значений.
Поэтому, в гистограмме хранятся только крайние значений корзин — в результате вывода запроса это можно увидеть. В histogram_bounds перечислено 11 значений, но там же корзины — от меньшего значений к большему, поэтому, этих корзин там (в данном случае) десять: первая корзина от AAQ до CSY, вторая корзина от CYS до HMA и так далее.
А теперь продолжим эксперимент. Уменьшим значение default_statistics_target до 5, пересоберем статистику и посмотрим, что поменяется:
postgres@demo=# set default_statistics_target = 5;
SET
postgres@demo=# analyze;
ANALYZE
Снова нужно выполнить запрос к вьюхе pg_stats:
postgres@demo=# select tablename, attname, most_common_vals, histogram_bounds
from pg_stats where tablename = 'flights' and attname = 'departure_airport' \gx
-[ RECORD 1 ]----+--------------------------
tablename | flights
attname | departure_airport
most_common_vals | {DME,SVO,VKO,LED,NUX}
histogram_bounds | {AAQ,GDX,KXK,OVS,TBW,YKS}
Смотрим результат — размерность массива с самыми популярными значениями изменилась до пяти, и количество корзин в гистограмме тоже сократилось.
Увидели и потрогали статистику собственными руками 🙂 А вот качество собираемой статистики (в зависимости от величины default_statistics_target), можете попробовать проверить самостоятельно.
Leave a Reply