Планы выполнения формируются на основе команды, статистики, собранной по объектам и параметрам конфигурации. Основной способ сбора статистики — команда ANALYZE, для которой нужны данные. В PostgreSQL 18 версии появились функции pg_restore_relation_statsи pg_restore_attribute_stats, которые могут записать статистики в системный каталог. Вместе с возможностью выгрузки статистики параметром утилиты pg_dump —statistics-only, статистику можно переносить между базами данных.
Функционал переноса статистики был создан для обновления кластера баз данных на новые версии. До 18 версии статистика не выгружалась и собиралась после обновления. Сбор статистики мог выполняться часами, что увеличивало время обновления. Начиная с 18 версии, утилита pg_upgrade, по умолчанию, сохраняет статистику, используя функционал экспорта и импорта статистики.
Этот же функционал можно использовать для переноса статистики с промышленных на тестовые базы данных. В статье рассматривается как это применять.
Постановка задачи
Структура объектов тестовой базы данных такая же, как промышленной, только в тестовой базе намного меньше данных. При работе на тесовой базе данных хотелось бы видеть планы выполнения, такие же, какие генерируются на промышленной. Это позволит на этапе тестирования находить узкие места или заниматься настойкой планов выполнения.
Однако, в тестовой базе данных другая статистика и планы выполнения запросов на тестовой базе будут отличаться от планов на промышленной. Запрос работает медленно на промышленной базе, хочется воспроизвести план на тестовой, а планировщик выбирает другой план. Для того, чтобы планы воспроизводились, нужно перенести статистики из промышленной базы.
Функция pg_restore_relation_stats()
В 18 версии PostgreSQL функция позволяет импортировать статистику по таблицам и другим типам отношений. Функция записывает статистики в таблицу системного каталога pg_class в виде пар: параметр-значение.
SELECT pg_restore_relation_stats( 'schemaname', 'public', 'relname', 'orders', 'relpages', 123513::integer, 'reltuples', 50000000::real, 'relallvisible', 123513::integer, 'relallfrozen', 120000::integer);
Создадим небольшую таблицу, вставим туда тестовые данные и посмотрим, какие планы будут использоваться:
CREATE TABLE test_orders ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id integer NOT NULL, amount numeric(10,2) NOT NULL, status text NOT NULL DEFAULT 'pending', created_at date NOT NULL DEFAULT CURRENT_DATE);INSERT INTO test_orders (customer_id, amount, status, created_at)SELECT (random() * 9999 + 1)::int, (random() * 5000 + 5)::numeric(10,2), (ARRAY['pending', 'shipped', 'delivered', 'cancelled'])[floor(random()*4+1)::int], '2024-01-01'::date + (random() * 365)::int FROM generate_series(1, 10000);CREATE INDEX ON test_orders (created_at);CREATE INDEX ON test_orders (status);ANALYZE test_orders;Статистика показывает, что в таблице 10000 строк:SELECT relname, relpages, reltuples FROM pg_class WHERE relname = 'test_orders'; relname | relpages | reltuples-------------+----------+----------- test_orders | 74 | 10000(1 row)
Планировщик выбирает полное сканирование таблицы:
EXPLAIN SELECT * FROM test_orders WHERE created_at > '2024-06-01'; QUERY PLAN---------------------------------------------------- Seq Scan on test_orders (cost=0.00..199.00 rows=5891 width=26) Filter: (created_at > '2024-06-01'::date)(2 rows)
Предположим, что в промышленной базе данных 50 миллионов строк. Поменяем статистику:
SELECT pg_restore_relation_stats( 'schemaname', 'public', 'relname', 'test_orders', 'relpages', 123513::integer, 'reltuples', 50000000::real, 'relallvisible', 123513::integer);
План не поменяется:
EXPLAIN SELECT * FROM test_orders WHERE created_at > '2024-06-01'; QUERY PLAN---------------------------------------------------- Seq Scan on test_orders (cost=0.00..448.45 rows=17649 width=26) Filter: (created_at > '2024-06-01'::date)
Планировщик по-прежнему использует полное сканирование таблицы. Изменилось только планируемое число строк. Почему? Это из-за статистики по столбцам. Границы гистограммы распределения значений по столбцу created_at не поменялись и соответствуют 10000 строкам, которые были вставлены в таблицу.
Функция pg_restore_attribute_stats()
Эта функция записывает статистики на уровне столбцов в pg_statistic — ту же таблицу системного каталога, в которую команда ANALYZE записывает наиболее часто встречающиеся значения (MCV), гистограммы распределения значений по столбцам и данными по упорядоченности строк (correlation) и индексных ссылок.
correlation показывает планировщику, насколько точно физический порядок строк соответствует порядку сортировки столбцов. Значение, близкое к 1,0, означает последовательный доступ, что делает сканирование индекса дешевле, поскольку следующая строка, скорее всего, будет находиться в том же или соседнем блоке таблицы. Для временных рядов, например, когда строки вставляются в хронологическом порядке, корреляция для столбца created_at будет очень высокой.
Добавим границы гистограммы для столбца created_at:
SELECT pg_restore_attribute_stats( 'schemaname', 'public', 'relname', 'test_orders', 'attname', 'created_at', 'inherited', false::boolean, 'null_frac', 0.0::real, 'avg_width', 4::integer, 'n_distinct', -0.05::real, 'histogram_bounds', '{2019-01-01, 2019-07-01, 2020-01-01, 2020-07-01, 2021-01-01, 2021-07-01, 2022-01-01, 2022-07-01, 2023-01-01, 2023-07-01, 2024-01-01}'::text, 'correlation', 0.98::real);
Теперь планировщик знает, что данные охватывают 5 лет. Запрос, фильтрующий последние 6 месяцев 2024 года, охватывает узкий срез.
Теперь запрос будет использовать индексное сканирование:
EXPLAIN SELECT * FROM test_orders WHERE created_at > '2024-06-01'; QUERY PLAN----------------------------------------------------Index Scan using test_orders_created_at_idx on test_orders (cost=0.29..157.00 rows=6493 width=27) Index Cond: (created_at > '2024-06-01'::date) Estimated Fetched Rows: 6493 (3 rows)
Гистограмма показывает планировщику, что данные охватывают период с 2019 по 2024 год и условие > ‘2024-06-01’ соответствует лишь небольшой части от 50 миллионов строк. Теперь выбирается индексное сканирование. Статистика на уровне таблиц задает масштаб, статистика на уровне столбцов формирует селективность, вместе они меняют план.
Неравномерности в распределении значений
Та же функция загружает списки наиболее часто встречающихся значений (Most Common Values) . Распределение значений в столбце status не однородно, 95% заказов имеют статус delivered и только 1,5% в статусе pending.
Загрузим most common values:
SELECT pg_restore_attribute_stats( 'schemaname', 'public', 'relname', 'test_orders', 'attname', 'status', 'inherited', false::boolean, 'null_frac', 0.0::real, 'avg_width', 9::integer, 'n_distinct', 5::real, 'most_common_vals', '{delivered,shipped,cancelled,pending,returned}'::text, 'most_common_freqs', '{0.95,0.015,0.015,0.015,0.005}'::real[]);
Для 1.5%:
EXPLAIN SELECT * FROM test_orders WHERE status = 'pending'; QUERY PLAN----------------------------------------------------Bitmap Heap Scan on test_orders (cost=7.81..89.64 rows=455 width=27) Recheck Cond: (status = 'pending'::text) Estimated Fetched Rows: 455 -> Bitmap Index Scan on test_orders_status_idx (cost=0.00..7.70 rows=455 width=0) Index Cond: (status = 'pending'::text) (5 rows)
Для 95% оптимально полное сканирование:
EXPLAIN SELECT * FROM test_orders WHERE status = 'delivered'; QUERY PLAN---------------------------------------------------- Seq Scan on test_orders (cost=0.00..454.51 rows=28843 width=27) Filter: (status = 'delivered'::text)(2 rows)
Один и тот же столбец, один и тот же оператор сравнения, но разные планы. Планировщик использует построение и сканирование битовой карты для pending (1,5% случаев, достаточно редких для оправдания создания битовой карты) и полное сканирование для delivered (95%, то есть большая часть строк таблицы). Значения селективности (most_common_freqs) для списка значений MCV (most_common_vals) определяют выбор плана.
Библиотека pg_regresql
Оценка числа строк (455 и 28 843) ниже, чем можно было бы ожидать для таблицы с 50 миллионами строк. Это произошло из-за того, что планировщик использует размер файлов данных таблицы. Таблица занимает всего 74 блока, а не 123 513 блоков, если бы в таблице было 50 млн. строк. Планировщик пропорционально уменьшает reltuples и relpages. Абсолютные числа уменьшаются, но соотношения между ними остаются правильными, и именно эти соотношения определяют план. Если восстановить статистику, выгруженную командой pg_dump --statistics-only на том же объеме данных, то размеры файлов будут такими же, как исходные и значения числа строк и стоимости будут такими же, как в исходной базе данных.
Если нужно в тестовой базе данных получать rows и cost, как на промышленной базе, можно воспользловаться расширением RegreSQL. Расширение меняет логику планировщика, чтобы он использовал значения relpages из статистики, а не считывал физический размер файлов.
Установить библиотеку довольно просто:
wget https://github.com/boringSQL/regresql/archive/refs/heads/master.zip -O regresql.zipunzip regresql.zipcd regresql-master/pg_extsu -c "make clean && make install"
При использовании библиотеки, планировщик может использовать relpages значению, вместо физического размера файлов, поэтому cost и rows будут соответствовать тому, что выдаётся на промышленной базе данных, даже если в тестовой базе в таблицах нет строк или их мало.
Расширение использует библиотеку, которая реализует функцию, которую вызывает функция из ядра PostgreSQL get_relation_info_hook(). Функция запускается после того, как планировщик считывает размер файлов таблиц и индексов. Функция библиотеки заменяет статистики на значения из pg_class:pages на pg_class.relpagestuples на pg_class.reltuplesallvisfrac на pg_class.relallvisible делённое на pg_class.relpages
Загрузим библиотеку и посмотрим, что изменится в планах:
load 'pg_regresql';LOADEXPLAIN SELECT * FROM test_orders WHERE status = 'pending'; QUERY PLAN---------------------------------------------------- Gather (cost=1306.78..430657.13 rows=750000 width=27) Workers Planned: 2 -> Parallel Bitmap Heap Scan on test_orders (cost=306.78..354657.13 rows=312500 width=27) Recheck Cond: (status = 'pending'::text) Estimated Fetched Rows: 36933975 -> Bitmap Index Scan on test_orders_status_idx (cost=0.00..119.28 rows=750000 width=0) Index Cond: (status = 'pending'::text)(7 rows)EXPLAIN SELECT * FROM test_orders WHERE status = 'delivered'; QUERY PLAN---------------------------------------------------- Seq Scan on test_orders (cost=0.00..748513.00 rows=47499999 width=27) Filter: (status = 'delivered'::text)(2 rows)
Поменялись значения cost и rows:
было cost=7.81..89.64 rows=455 стало cost=1306.78..430657.13 rows=750000
было cost=0.00..454.51 rows=28843 стало cost=0.00..748513.00 rows=47499999
Расширение работает с версиями до 19. В 19 версии функция get_relation_info_hook заменена на build_simple_rel_hook и, после выхода 19 версии, нужно будет обновить библиотеку.
pg_dump
Рассмотренные функции — это лишь инструменты. Для практического использования, pg_dump выполняет всё что нужно сам. В PostgreSQL 18 добавлены три параметра:--statistics выгрузить статистику--statistics-only выгрузить только статистику, а не схему или сами данные.--no-statistics не выгружать статистику
Пример команды для выгрузки статистики из промышленной базы данных:
pg_dump --statistics-only -d production_db > stats.sql
В выводе утилиты получится набор вызовов SELECT pg_restore_relation_stats(...) и SELECT pg_restore_attribute_stats(...).
Статистика выгружается и с предыдущих версий PostgreSQL.
Последовательность выгрузки и загрузки статистики в тестовую базу такой:
# 1. дамп определения объектов без данныхpg_dump --schema-only -d production_db > schema.sql
# 2. дамп статистикиpg_dump --statistics-only -d production_db > stats.sql
# 3. создание тестовой базы данных на тестовом хостеcreatedb test_dbpsql -d test_db -f schema.sql
# 4. загрузить тестовые данные, если они есть
# 5. импортировать статистику с 2 шагаpsql -d test_db -f stats.sql
# 6. пример команды для просмотра плана на тестовой базеpsql -d test_db -c "EXPLAIN SELECT * FROM test_orders WHERE status = 'pending'"
Дампы со статистиками очень маленькие. Для сотен таблиц и тысячами столбцов дамп со статистикой будет меньше 1Мб.
Защита от пересбора статистики на тестовой базе данных
Если автовакуум решит пересобрать статистику, то импортированная статистика будет заменена статистикой по тестовым таблицам с небольшим числом строк. Чтобы этого избежать нужно отключить автовакуум для таблиц, по которым статистика была импортирована
ALTER TABLE test_orders SET (autovacuum_enabled = false);
или установить пороговые значения, которые предотвратят сбор статистики:
ALTER TABLE test_orders SET (autovacuum_analyze_threshold = 2147483647);
На промышленной базе данных не стоит отключать сбор статистики.
Особенности
Расширенная статистика и статистика для индексов по выражениям в 18 версии не выгружается. В 19 версии появится функция pg_restore_extended_stats() и расширенную статистику можно будет экспортировать и импортировать.
Заключение
Начиная с 18 версии PostgreSQL, можно перегружать статистики между базами данных, что позволяет планировщику формировать одинаковые планы выполнения. Это позволяет выполнять запросы с реальными планами выполнения на тестовых данных меньшего объема. Также, это позволяет настраивать работу планировщика, используя тестовую базу данных.
ссылка на оригинал статьи https://habr.com/ru/articles/1044730/