PostgreSQL. default_statistics_target. Эксперименты

Совсем недавно чуть-чуть рассказал про параметр 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), можете попробовать проверить самостоятельно.


Be the first to comment

Leave a Reply

Ваш Mail не будет опубликован.


*