Historique de la question
PostgreSQL a introduit des instructions préparées pour éliminer les frais de parsing et de planification pour les requêtes SQL exécutées à plusieurs reprises. Les premières versions généraient toujours des plans d'exécution personnalisés adaptés à des valeurs de paramètres spécifiques, mais cela entraînait des coûts CPU significatifs pour des requêtes complexes. Pour optimiser cela, PostgreSQL 9.2 a mis en œuvre un cache de plans avec un mécanisme de plan générique qui réutilise une seule structure de plan sur plusieurs exécutions. Bien que cette approche réduise la latence de planification, elle traite tous les paramètres comme des espaces réservés opaques lors de la phase de planification initiale.
Le problème
L'élagage de partitions dans PostgreSQL opère à deux étapes distinctes : l'élagage au moment de la planification, qui se produit lors de la planification de la requête lorsque le planificateur examine les contraintes de partition par rapport à des valeurs littérales, et l'élagage à l'exécution, qui filtre les partitions lors de l'exécution à l'aide de jointures partitionnées ou de filtrage de nœuds d'addition. Les plans génériques générés pour les instructions préparées manquent de valeurs paramétriques concrètes au moment de la planification, rendant impossible l'élagage au moment de la planification. En conséquence, le planificateur génère une analyse de toutes les partitions, indépendamment des valeurs de paramètres réelles fournies lors de l'exécution, entraînant une dégradation catastrophique des performances sur de grandes tables partitionnées.
La solution
La résolution nécessite de forcer PostgreSQL à générer des plans personnalisés qui intègrent de réelles valeurs de paramètres lors de la phase de planification. Cela s'effectue en définissant le paramètre de configuration plan_cache_mode à force_custom_plan pour la session ou la requête spécifique, contournant entièrement le cache de plan générique. Alternativement, les contournements au niveau du protocole incluent l'utilisation du protocole de requête étendue avec le message Bind contenant des valeurs littérales plutôt que des paramètres, ou l'utilisation de constructeurs de requêtes côté client qui insèrent des valeurs littérales pour les clés de partition tout en conservant d'autres paramètres liés pour prévenir l'injection SQL.
-- Forcer un plan personnalisé pour cette session SET plan_cache_mode = force_custom_plan; -- Ou utiliser SQL dynamique avec format() pour insérer des littéraux en toute sécurité EXECUTE format('SELECT * FROM logs WHERE log_date >= %L', '2024-01-01');
Une plateforme d'analyse de trading haute fréquence a éprouvé des pics de latence sévères chaque matin lors de la requête des mouvements de prix intrajournaliers. La base de données stockait des données de ticks dans une table partitionnée par date de session de trading, contenant plus de 2 000 partitions s'étendant sur cinq ans. L'application utilisait des instructions préparées JDBC avec des espaces réservés ? pour le paramètre de date afin de prévenir l'injection SQL et de réduire les frais de parsing.
L'équipe de développement a d'abord observé que les requêtes filtrant pour les données "du jour" analysaient des partitions historiques, consommant 45 secondes au lieu des 300 millisecondes attendues. Cette dégradation des performances est survenue parce que le plan générique ne pouvait pas éliminer les partitions non pertinentes lors de la phase de planification.
Une approche consistait à créer une table non journalisée séparée pour les données chaudes et à migrer les enregistrements chaque nuit. Cette stratégie aurait complètement contourné la table partitionnée pour les requêtes récentes, mais elle a introduit une logique ETL complexe et risquait une perte de données lors de pannes système.
Une autre proposition a suggéré de désactiver les instructions préparées globalement dans le pool de connexions JDBC. Bien que cela aurait rétabli l'élagage au moment de la planification en exposant des valeurs littérales au planificateur, des benchmarks ont révélé une augmentation de 40 % de l'utilisation CPU sur le serveur de base de données en raison des frais de parsing et de planification répétés.
L'équipe a également évalué l'utilisation des capacités d'élagage de partitions à l'exécution de PostgreSQL introduites dans la version 11. Cependant, l'élagage à l'exécution n'élimine les partitions qu'après que l'exécuteur a commencé à analyser, ce qui signifie que le planificateur allouait toujours des ressources pour toutes les partitions et produisait des ordres de jointure sous-optimaux qui ignoraient les frontières des partitions.
En fin de compte, l'équipe a choisi de mettre en œuvre un changement de configuration au niveau de la connexion. Ils ont configuré le pool de connexions pour détecter les requêtes ciblant des tables partitionnées et exécuter SET plan_cache_mode = force_custom_plan avant de transmettre ces instructions spécifiques. Cela a préservé les avantages de sécurité des requêtes paramétrées pour les filtres d'entrée utilisateur tout en s'assurant que les valeurs des clés de partition était visibles au planificateur.
Le résultat a réduit la latence des requêtes à 280 millisecondes et diminué l'utilisation globale du CPU de la base de données de 15 %, car le planificateur pouvait désormais utiliser l'exclusion de contraintes pour éliminer 1 999 partitions avant l'exécution. Cette optimisation a permis à la plateforme de trading de respecter ses exigences strictes de latence matinale sans compromettre l'intégrité ou la sécurité des données.
Comment PostgreSQL décide-t-il entre des plans génériques et personnalisés lorsque plan_cache_mode est défini sur auto ?
En mode auto, PostgreSQL planifie et exécute la requête en utilisant un plan personnalisé pour les cinq premières exécutions, accumulant le coût de planification. Après la cinquième exécution, il compare le temps d'exécution moyen du plan générique (estimé lors de la première exécution) avec le temps d'exécution moyen des plans personnalisés plus leurs frais de planification. Si le coût estimé du plan générique est inférieur au coût moyen du plan personnalisé, le système bascule définitivement vers le plan générique pour cette instruction préparée. Les candidats oublient souvent que cette comparaison inclut les frais de planification économisés en réutilisant le plan générique, et que la décision est permanente pour la durée de vie de l'instruction préparée, à moins d'être explicitement replanifiée.
Quelle est la distinction entre l'élagage au moment de la planification et l'élagage à l'exécution dans le contexte des instructions préparées ?
L'élagage au moment de la planification se produit pendant la phase de planification lorsque le planificateur peut prouver que certaines partitions ne peuvent pas contenir de lignes pertinentes sur la base des contraintes de partition et des valeurs littérales dans la requête. L'élagage à l'exécution se produit pendant l'exécution lorsque l'exécuteur vérifie les contraintes de partition par rapport aux valeurs paramétriques réelles à l'aide du mécanisme de filtrage de l'exécuteur. Les plans préparés génériques prennent en charge l'élagage à l'exécution à partir de PostgreSQL 11, mais ils ne peuvent pas prendre en charge l'élagage au moment de la planification parce que les valeurs de paramètres sont inconnues. Les candidats confondent souvent ces mécanismes, croyant que l'élagage à l'exécution résout tous les problèmes de partitionnement des instructions préparées, sans être conscients que l'élagage au moment de la planification est crucial pour une planification de jointure efficace et la sélection d'index.
Pourquoi force_custom_plan pourrait-il ne pas résoudre les problèmes d'élagage de partition dans les versions de PostgreSQL antérieures à 10 ?
Avant la version 10, PostgreSQL manquait de prise en charge significative pour l'élagage de partitions à l'exécution dans son intégralité, et les instructions préparées ne pouvaient pas bénéficier de l'exclusion de contraintes même avec des plans personnalisés si les paramètres étaient transmis via le protocole de requête étendue en utilisant le message Bind. Le planificateur traitait tous les paramètres liés comme externes au processus de planification, nécessitant des valeurs littérales explicites dans la chaîne de requête elle-même pour déclencher l'exclusion de contraintes. Cette limitation historique signifie que dans les systèmes hérités, même les plans personnalisés analysaient toutes les partitions, nécessitant une génération dynamique de SQL avec EXECUTE ... USING avec des littéraux ou une concaténation de chaînes côté client avec un échappement approprié, plutôt que le binding de paramètres au niveau du protocole moderne.