Анализируй это — ANALYZE

Анализируй это - ANALYZE

Используйте ANALYZE что бы оптимизировать PostreSQL запросы

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

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

Так как же планировщик определяет наилучший способ выполнения SQL запроса? Каждый строительный блок SQL запроса (узел запроса или query node) имеет связанную с ним функцию, которая генерирует стоимость выполнения данного узла. Это именно то, что вы видите, когда вы запускаете EXPLAIN. Например:

EXPLAIN SELECT * FROM "Devices" WHERE "Id" = 214;

Seq Scan on "Devices"  (cost=0.00..3.38 rows=1 width=102)

В данном примере делается запрос к таблице Devices, и из нее извлекается одна запись с использованием отсечения данных по колонке Id. Планировщик показывает, что при сканировании таблицы будет возвращена 1 запись размером 102 байта. Предполагаемая стоимость операции — от 0 до 3.38 единиц работы. Единицы работы очень трудно перевести во что-нибудь реальное, типа времени или операций ввода/вывода. Просто думайте о них как о абсолютной величине затраченной работы. Чем меньше – тем лучше, чем больше – тем хуже.

Но как PostgreSQL высчитал, что стоимость операции 3,38? Планировщик вызвал функцию для оценки стоимости для Seq Scan. Затем эта функция посмотрела статистическую информацию о таблице Devices и использовала ее, чтобы произвести оценку работы для выполнения запроса. PostgreSQL хранит два набора статистики для каждой таблицы.

Первый набор хранит информацию о размере таблицы – насколько она большая. Эта информация хранится в системной таблице pg_class. Поле relpages – это количество страниц БД, которые используются для хранения всей информации таблицы. reltuples – это поле хранит количество записей (строк, кортежей) в таблице. Для планировщика это очень важная информация. Он использует среднее количество строк на страницу (reltuples/relpages) что бы решить, как много страниц нужно будет прочесть, что бы извлечь необходимое количество записей. Это важно, так как все операции записи/чтения диска работают со страницами, т.е. для того, что бы прочесть даже одну запись, БД будет читать целую страницу с диска. Т.е. зная количество записей, которые нужно извлечь, планировщик может подсчитать количество операций чтения (приблизительно).

Таблица pg_class для тестовой таблицы

Таблица pg_class для тестовой таблицы

Второй набор статистики призван ответить на вопрос, сколько записей нужно будет вернуть в результате запроса. Рассмотрим простой пример, как используется эта статистика. Возьмем простую таблицу, в которой будет только одно поле number, с числами от 1 до 10. Затем зададим запрос, для извлечения всех чисел больше или равных 5.

EXPLAIN для простого SQL запроса

EXPLAIN для простого SQL запроса

Как видите, планировщик определил, что нужно достать только 6 записей (5, 6, 7, 8, 9, 10). Эта информация получена из представления pg_stats (которое основано на таблице pg_statistic). Планировщик проверяет поле pg_stats.histogram_bounds, которое содержит список значений, разделяющих все значения столбца на одинаковые группы (“buckets”). (Это поле используется только для типов данных, которые поддерживают операция меньше <). Группы разделяются на примерно одинаковое количество элементов. Т.е. между границами, перечисленными в данном столбце, будет примерно одинаковое количество элементов в столбце. Если таблица содержит числа от 1 до 100, то такое поле может выглядеть примерно как {1, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100}. Т.е. столбец имеет одинаковое количество элементов со значением от 1 до 10, от 10 до 20, и т.д. Если в таблице будут дубликаты, тогда границы изменятся.

Таблица pg_stats

Таблица pg_stats

Поэтому когда вы задаете условие <10, планировщик используя эти данные может предположить, что нужно будет вернуть строки из группы 1..10. А количество строк можно высчитать как общее количество строк (pg_class.reltuples) деленное на количество групп в поле pg_stats.histogram_bounds. Далее смотрим, сколько записей помещается на одной странице (pg_class.reltuples / pg_class.relpages) и высчитываем сколько операция чтения страниц нужно сделать. Имея эту информацию, планировщик может предположить, сколько именно единиц работы нужно сделать, что бы извлечь все нужные строки.

Но это кажется не достаточно полной информацией для составления корректного плана. К примеру, у нас есть список групп {1, 100, 101}. Это значит, что у нас в таблице записей со значение от1 до 100 столько же, сколько и записей со значением от 100 до 101. Но какие именно числа есть в таблице в регионе от 1 до 100? Что бы решить эту проблему, PostgreSQL использует два других поля: most_common_vals и most_common_freqs. most_common_vals – это поле хранит сведения о наиболее часто встречающихся значениях в таблице. most_common_freqs – это поле хранит информацию о том, как часто значения из most_common_vals встречаются в таблице (как десятичная дробь от общего количества строк в таблице) Т.е. к примеру, если most_common_vals = {1, 2} и most_common_freqs = {0.1, 0.2} это значит, что значение 1 встречается в 10% строк таблицы, а значение 2 в 20% строк таблицы.

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

Сделать это можно 2 путями.

Первый – изменить параметр default_statistics_target в файле конфигурации postgresql.conf. Это глобально изменит количество групп и значений в статистике для всех столбцов всех таблиц в базе данных. Второй – использовать SQL запрос на изменения этого параметра только для конкретного столбца в конкретной таблице:

ALTER TABLE table_name ALTER column_name SET STATISTICS 1000

Но тут есть и обратная сторона. Если у вас очень много таблиц (> 100 к примеру) то изменение глобального параметра в файле конфигурации приведет к росту размера статистических таблиц. А большой размер статистики может замедлить выполнение запроса. Так что не стоит ставить этот параметр больше чем 100 ( в большинстве случаев этого достаточно). А для таблиц с большим количеством строк стоит увеличить это значение только для строк, которые используются в выражении WHERE.

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

Следующие поле – correlation. Это поле определяет линейность изменения значений в столбцах. Если значение увеличивается с каждой следующей строкой в таблице, то correlation будет иметь значение 1, если значение будет уменьшаться – то -1. Это ключевое поле, которое влияет на то, будет ли выбрана функция INDEX SCAN при поиске значений. Если correlation близок к 1 или -1 – то INDEX SCAN будет работать практически линейно и не будет прыгать по таблице, что значительно сократит время поиска.

И парочку полей на последок:

  • avg_width – средняя ширина данных в столбце
  • null_frac – частота значения поля NULL в общем количеству строк
Таблица pg_stats для тестовой таблицы

Таблица pg_stats для тестовой таблицы

Теперь посмотри эти поля на примере простой таблицы, в которой хранятся числа от 1 до 10.Как видите, это отфильтрованное значение для таблицы analyze_test, для поля number.

Поле Значение Описание
 null_frac 0  это значит в таблице нет значений NULL Для поля number
 avg_width  4  это значит что средняя ширина данных – 4 байта (integer)
 n_distinct  -1  это значит что в таблице только уникальные значения
 most_common_vals  пусто  для этой таблицы это не актуально
 most_common_freq  пусто  для этой таблицы это не актуально
 histogram_bounds  тут все просто, все 10 значений распределились по 1 в каждой из 10 групп
 correlation  1  каждое следующие поле больше предыдущего

Все это хорошо, и эта статистика помогает планировщику делать быстрые запросы. Но есть проблема. Эта информация должна обновляться, что бы планировщик правильно планировал запрос. Что бы в этом убедиться, мы просто добавим в таблицу значения от 11 до 20 и снова посмотрим, что покажет планировщик.

Повторный EXPLAIN для тестового SQL запроса

Повторный EXPLAIN для тестового SQL запроса

Этот запрос по идее должен вернуть нам 15 записей, но планировщик показывает только 6. А это потому, что после добавления новых строк статистика осталась такой же, как была для 10 строк. Т.е. статистика устарела. И если это незначительно влияет на такую маленькую таблиц, то для больших таблиц с миллионами записей устаревшая статистика может приводить к долгим и неэффективным запросам.
Именно поэтому важно своевременно обновлять эту статистику. Для этого нужно использовать команду AUTOVACUUM или что еще предпочтительнее – демон AUTOVACUUM, который включен в PostgreSQL. Но об этом в следующей статье.

Добавить комментарий

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