История вопроса
PostgreSQL внедрил подготовленные выражения для устранения затрат на парсинг и планирование для многократно выполняемых SQL запросов. Ранние версии всегда генерировали пользовательские планы выполнения, адаптированные к конкретным значениям параметров, но это влекло за собой значительные затраты ЦП для сложных запросов. Для оптимизации этого в PostgreSQL 9.2 была реализована кэширование планов с помощью механизма общего плана, который повторно использует одну и ту же структуру плана для нескольких выполнений. Хотя этот подход снижает задержку планирования, он рассматривает все параметры как непрозрачные заполнители на стадии начального планирования.
Проблема
Отсечение партиций в PostgreSQL происходит на двух различных стадиях: отсечении во время планирования, которое происходит во время планирования запроса, когда планировщик рассматривает ограничения партиций по сравнению с явными значениями, и отсечении во время выполнения, которое фильтрует партиции во время выполнения с использованием защиты партиций или фильтрации узлов добавления. Общие планы, генерируемые для подготовленных выражений, не имеют конкретных значений параметров на этапе планирования, что делает отсечение во время планирования невозможным. В результате планировщик генерирует сканирование по всем партициям независимо от фактически переданных значений параметров во время выполнения, что приводит к катастрофическому ухудшению производительности на больших таблицах с партициями.
Решение
Для решения проблемы необходимо заставить PostgreSQL генерировать пользовательские планы, которые включают фактические значения параметров на этапе планирования. Это достигается установкой параметра конфигурации plan_cache_mode в force_custom_plan для сеанса или конкретного запроса, полностью обходя общий кэш планов. Альтернативно, обходы на уровне протокола включают использование расширенного протокола запросов с сообщением Bind, содержащим явные значения вместо параметров, или использование клиентских генераторов запросов, которые вставляют явные значения для ключей партиции, сохраняя при этом привязанные другие параметры для предотвращения SQL-инъекций.
-- Принудить пользовательский план для этого сеанса SET plan_cache_mode = force_custom_plan; -- Или использовать динамический SQL с format() для безопасного инлайн-встраивания литералов EXECUTE format('SELECT * FROM logs WHERE log_date >= %L', '2024-01-01');
Платформа аналитики высокочастотной торговли испытывала резкие скачки задержки каждое утро при запросах на внутридневные движения цен. База данных хранила данные о сделках в таблице, разбитой по дате сеанса торговли, содержащей более 2000 партиций за пять лет. Приложение использовало подготовленные выражения JDBC с заполнителями ? для даты параметра, чтобы предотвратить SQL-инъекции и уменьшить затраты на парсинг.
Команда разработчиков изначально заметила, что запросы, фильтрующие "сегодняшние" данные, сканировали исторические партиции, затрачивая 45 секунд вместо ожидаемых 300 миллисекунд. Это ухудшение производительности произошло из-за того, что общий план не мог исключить неактуальные партиции во время этапа планирования.
Один из подходов заключался в создании отдельной незаписываемой таблицы для горячих данных и миграции записей каждую ночь. Эта стратегия полностью обошла бы разделенные таблицы для недавних запросов, но ввела бы сложную логику ETL и риски потери данных во время сбоев системы.
Другое предложение заключалось в том, чтобы отключить подготовленные выражения глобально в пуле соединений JDBC. Хотя это восстановило бы отсечение во время планирования, открыв явные значения планировщику, бенчмарки показали увеличение использования ЦП на 40% на сервере базы данных из-за повторного парсинга и планирования.
Команда также оценивала использование возможностей отсечения партиций на уровне выполнения, внедренных в PostgreSQL версии 11. Однако отсечение во время выполнения только исключает партиции после начала сканирования исполнителем, что означает, что планировщик по-прежнему выделял ресурсы для всех партиций и производил неоптимальные порядки соединения, игнорируя границы партиций.
В конечном итоге команда решила реализовать переключение конфигурации на уровне соединения. Они настроили пул соединений для обнаружения запросов, обращающихся к разделенным таблицам, и выполнения SET plan_cache_mode = force_custom_plan перед отправкой конкретных выражений. Это сохранило преимущества безопасности параметризованных запросов для фильтров пользовательского ввода, обеспечивая при этом видимость значений ключей партиций для планировщика.
В результате задержка запроса снизилась до 280 миллисекунд, а общее использование ЦП базы данных уменьшилось на 15%, так как планировщик теперь мог использовать исключение ограничений для исключения 1999 партиций перед выполнением. Эта оптимизация позволила торговой платформе удовлетворить строгие утренние требования по задержке без ущерба для целостности данных или безопасности.
Как PostgreSQL решает, между какими общими и пользовательскими планами выбирать, когда plan_cache_mode установлен в auto?
В режиме auto PostgreSQL планирует и выполняет запрос, используя пользовательский план для первых пяти выполнений, аккумулируя затраты на планирование. После пятого выполнения система сравнивает среднее время выполнения общего плана (оцененное во время первого выполнения) с средним временем выполнения пользовательских планов плюс их затраты на планирование. Если оцененные затраты общего плана меньше, чем средняя стоимость пользовательского плана, система навсегда переключается на общий план для этого подготовленного выражения. Кандидаты часто упускают из виду, что это сравнение включает затраты на планирование, сэкономленные за счет повторного использования общего плана, и что решение является постоянным на протяжении всей жизни подготовленного выражения, если его не переосмыслить.
В чем различие между отсечением партиций во время планирования и во время выполнения в контексте подготовленных выражений?
Отсечение во время планирования происходит на этапе планирования, когда планировщик может доказать, что определенные партиции не могут содержать актуальные строки на основе ограничений партиций и явных значений в запросе. Отсечение во время выполнения происходит во время выполнения, когда исполнитель проверяет ограничения партиций по фактическим значениям параметров с помощью механизма фильтрации исполнителя. Общие подготовленные планы поддерживают отсечение во время выполнения, начиная с PostgreSQL 11, но не могут поддерживать отсечение во время планирования, поскольку значения параметров неизвестны. Кандидаты часто путают эти механизмы, полагая, что отсечение во время выполнения решает все проблемы с партиционированием подготовленных выражений, не осознавая, что отсечение во время планирования имеет решающее значение для эффективного планирования соединений и выбора индексов.
Почему force_custom_plan может не решить проблемы с отсечением партиций в версиях PostgreSQL до 10?
До версии 10 PostgreSQL полностью не поддерживал отсечение партиций во время выполнения, и подготовленные выражения не могли воспользоваться исключением ограничений даже с пользовательскими планами, если параметры передавались через расширенный протокол запросов с использованием сообщения Bind. Планировщик рассматривал все связанные параметры как внешние по отношению к процессу планирования, требуя явных значений непосредственно в строке запроса, чтобы инициировать исключение ограничений. Это историческое ограничение означает, что в устаревших системах даже пользовательские планы сканировали все партиции, требуя динамической генерации SQL с EXECUTE ... USING с литералами или конкатенации строк на стороне клиента с правильным экранированием вместо современной привязки параметров на уровне протокола.