Techniques d’optimisation PostgreSQL non conventionnelles

• Présentation de trois approches créatives pour améliorer les performances des requêtes PostgreSQL en sortant des méthodes classiques, au lieu d’ajouter des index ou de réécrire les requêtes
• Élimination des scans complets de table via des contraintes CHECK, optimisation de la faible cardinalité avec des index basés sur des fonctions, et mise en œuvre d’une contrainte d’unicité avec des index Hash
• 1. Utiliser le paramètre constraint_exclusion pour éviter les scans inutiles dans les requêtes comportant des conditions erronées
• 2. Utiliser des index basés sur des fonctions et des colonnes générées virtuelles (virtual generated column) pour réduire la taille des index et garantir la cohérence des requêtes
• 3. Combiner des index Hash et des contraintes d’exclusion (exclusion constraint) pour implémenter efficacement une contrainte d’unicité sur de gros champs texte, avec d’importantes économies d’espace de stockage

---

Éliminer les scans complets de table avec des contraintes CHECK

• Même si une contrainte CHECK n’autorise que les valeurs 'free' et 'pro' dans la colonne plan, si une requête erronée est exécutée avec 'Pro', PostgreSQL parcourt toute la table
  • Le plan d’exécution lit les 100�00 lignes, alors que le résultat réel est de 0 ligne
• En définissant le paramètre constraint_exclusion sur 'on', PostgreSQL tient compte des contraintes et supprime entièrement le scan
  • Le temps d’exécution passe de 7,4 ms à 0,008 ms
• La valeur par défaut est 'partition', et sur des requêtes simples, la surcharge de planification peut au contraire augmenter
  • En revanche, dans un environnement de BI et de reporting, où les utilisateurs saisissent souvent des conditions incorrectes, le réglage sur 'on' peut être utile

Optimiser la faible cardinalité avec des index basés sur des fonctions

• Sur la table sale, qui contient 10 millions d’enregistrements de ventes, une requête d’agrégation du chiffre d’affaires quotidien prend 627 ms avec un scan complet
• L’ajout d’un index B-Tree sur la colonne sold_at réduit ce temps à 187 ms, mais la taille de l’index grimpe à 214 MB
• En créant un index basé sur une fonction sur l’expression date_trunc('day', sold_at), la taille tombe à 66 MB et le temps d’exécution s’améliore encore à 145 ms
  • La faible cardinalité permet une déduplication de l’index
• En revanche, l’expression de la requête doit correspondre exactement à la définition de l’index, ce qui impose de maintenir la cohérence des expressions
  • Pour cela, on peut créer une VIEW contenant la même expression, ou
  • ajouter une colonne générée virtuelle (virtual generated column), prise en charge à partir de PostgreSQL 18, afin d’automatiser cette cohérence
• Avec une colonne générée virtuelle, l’index est utilisé automatiquement, ce qui permet d’obtenir à la fois un petit index, des requêtes plus rapides et une cohérence des expressions
• Toutefois, dans PostgreSQL 18, la création d’index sur des colonnes virtuelles n’est pas encore prise en charge, un support étant prévu dans la version 19

Implémenter une contrainte d’unicité avec des index Hash

• Dans une table urls stockant de longues URL, la création d’un index unique basé sur B-Tree pour empêcher les doublons d’URL fait monter la taille de l’index à 154 MB
• Un index Hash ne stocke pas les valeurs réelles mais uniquement leur hachage, ce qui le rend beaucoup plus compact
  • PostgreSQL ne prend pas en charge par défaut les index Hash uniques, mais
  • il est possible de contourner cette limite pour implémenter une contrainte d’unicité à l’aide d’une contrainte d’exclusion (exclusion constraint) sous la forme EXCLUDE USING HASH (url WITH =)
• Avec cette méthode, une insertion en double provoque également une erreur, et les performances des requêtes sont supérieures à celles du B-Tree (0.022 ms vs 0.046 ms)
• La taille de l’index est de 32 MB, soit plus de 5 fois plus petit qu’un B-Tree
• Inconvénients :
  • impossible de référencer la colonne avec une clé étrangère (contrainte REFERENCES impossible)
  • compatibilité limitée avec la syntaxe INSERT ... ON CONFLICT
  • remplacement possible par ON CONFLICT ON CONSTRAINT ou la syntaxe MERGE
• Les index Hash sont adaptés à la garantie d’unicité sur de gros champs texte et constituent une alternative efficace en espace lorsqu’aucune clé étrangère n’est nécessaire