Решён
Как правильно проверять работу оптимизатора запросов в PostgreSQL?

Игорь Тихонов • SQL и базы данных •
1.4k
6

Работаю с PostgreSQL 16, столкнулся с проблемой: на тестовой базе запрос отрабатывает за 50мс, на проде тот же запрос с теми же индексами тормозит по 3-4 секунды. Таблица около 8 млн записей.

Смотрю EXPLAIN ANALYZE, планы разные. На тесте seq scan, на проде bitmap heap scan с каким то странным фильтром. Статистика вроде свежая, делал ANALYZE вчера.

Как правильно проверять работу оптимизатора? Что смотреть в плане запроса чтобы понять где он ошибается? И вообще можно ли заставить оптимизатор выбрать другой план без хинтов (их же нет в постгресе)?

UPDATE: Проблема оказалась в default_statistics_target. На тестовой базе стоял 100 (дефолт), на проде кто то поставил 10 на эту таблицу. После ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 500 и нового ANALYZE планировщик наконец увидел реальное распределение данных и переключился на нормальный index scan. Запрос стал 40мс. Спасибо всем!
EXPLAIN ANALYZEPostgreSQLQuery plannerОптимизаторПроизводительность

6 ответов

Лучшие Новые Старые
Решение
63
Участник • 1 ответ

Скинь сам запрос и оба плана (тестовый и продовый). Без этого гадание на кофейной гуще.

Но если в общих чертах, чек лист при расхождении планов:

1. Статистика. Да, ты делал ANALYZE. Но проверь default_statistics_target для конкретных колонок. Если данные с неравномерным распределением (например, 90% строк имеют status='active'), дефолтного значения 100 может не хватить. Попробуй:

ALTER TABLE your_table ALTER COLUMN problem_column SET STATISTICS 1000;
ANALYZE your_table;

После этого посмотри pg_stats для этой колонки:

SELECT attname, n_distinct, most_common_vals, most_common_freqs, correlation
FROM pg_stats
WHERE tablename = 'your_table' AND attname = 'problem_column';

2. Корреляция. Если correlation близка к 0, данные физически разбросаны по диску рандомно. Планировщик видит это и выбирает bitmap scan вместо index scan, потому что при низкой корреляции index scan генерирует random I/O, а bitmap собирает страницы пачкой.

Решение: CLUSTER your_table USING your_index; (но это блокирует таблицу, на проде аккуратно).

3. work_mem. На проде и тесте одинаковый? Если на тесте work_mem=256MB, а на проде дефолтный 4MB, планировщик будет избегать hash join и сортировок в памяти.

4. effective_cache_size и random_page_cost. Если на проде random_page_cost=4 (дефолт для HDD), а данные на SSD, планировщик переоценивает стоимость random I/O и избегает index scan. Для SSD ставь random_page_cost = 1.1.

5. Принудительная смена плана без хинтов. В постгресе нет хинтов (есть расширение pg_hint_plan, но это другая история). Зато можно отключать методы доступа для дебага:

SET enable_bitmapscan = off;
EXPLAIN ANALYZE SELECT ...;

Это покажет альтернативный план и его реальную стоимость. Не для прода, только для диагностики.

6. auto_explain. Включи расширение auto_explain на проде с auto_explain.log_min_duration = '1s'. Оно будет писать в лог полные планы всех запросов медленнее 1 секунды. Поймаешь деградацию в реальном времени.

Аватар Игорь Тихонов
Игорь Тихонов Автор

Пошел проверять statistics_target и random_page_cost. Про корреляцию вообще не думал, отличная наводка. Отпишусь по результатам.

29
Эксперт • 1 ответ

Знакомая боль. 8 млн строк это та граница где постгресовский оптимизатор начинает чудить если статистика хоть немного врет.

Первое что проверь: n_distinct в pg_stats. Если планировщик думает что в колонке 100 уникальных значений, а на самом деле 100 000, он выберет совершенно другой план. Можно явно задать:

ALTER TABLE t ALTER COLUMN c SET (n_distinct = 100000);
ANALYZE t;

Второе: EXPLAIN (ANALYZE, BUFFERS, TIMING). Обрати внимание на Buffers: shared hit vs shared read. Если на тесте все в кэше (hit), а на проде читает с диска (read), это объясняет разницу в скорости, но не в планах.

Третье: сравни pg_class.reltuples и реальный count(*). После массовых INSERT/DELETE без VACUUM FULL эта цифра может уплывать, и планировщик считает стоимость на основе устаревшего размера таблицы.

13
Участник • 2 ответа

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

/*+ IndexScan(your_table your_index) */
SELECT * FROM your_table WHERE ...;

На проде юзаем его для пары особо капризных запросов, где оптимизатор стабильно промахивается. Не идеально, зато предсказуемо.

9
Эксперт • 2 ответа

Еще вариант: https://explain.dalibo.com/ - вставляешь туда вывод EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) и получаешь визуальное дерево плана с подсветкой узких мест. Сильно помогает когда план сложный и глазами его разбирать долго.

pgMustard тоже хороший инструмент, но платный.

4
Участник • 1 ответ

Может банально разные версии постгреса на тесте и на проде? Или разные настройки конфига? Сравни pg_settings на обоих серверах, часто оказывается что какой нибудь джун два года назад покрутил параметры и забыл.

0
Эксперт • 1 ответ

3-4 секунды на 8 млн записей это вообще ни о чем, мы на оракле работаем с таблицами по 2 миллиарда строк и терпим))

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

Написать ответ

Визуальный редактор (WYSIWYG)
Премодерация гостей

Вы отвечаете как гость. Ваш ответ будет скрыт до проверки модератором. Чтобы ответ появился сразу и вы получали репутацию — войдите в аккаунт.

Или войдите в систему
Будьте вежливы и соблюдайте правила платформы.