Postgres pg_hint_plan : forcer GIN vs GiST trigram en prod RAG

Un seul mot a fait scanner notre base Postgres pendant 38 minutes en prod, en pleine nuit, jusqu'à ce que Sentry s'effondre sous les exceptions et que le pod RAG sature. Le mot : « stiefkinderen » (néerlandais, « beaux-enfants »). Pas un terme exotique, pas une injection, juste un nom commun cherché par un avocat dans un assistant juridique multilingue.

La cause racine n'est pas dans la requête. Elle est dans le choix d'index par le planner Postgres, qui a basculé sur un GiST trigram là où on attendait un GIN, et qui n'a jamais voulu en démordre malgré nos tentatives de le forcer. La sortie de crise a tenu en une extension peu connue (pg_hint_plan) installée dans une image Postgres custom sur CloudNativePG, et un commentaire SQL inline qui a ramené la latence à 890 ms. Soit un facteur 2500 sur le même corpus.

Cet article raconte la séquence complète : le faux fix qu'on a cru bon pendant trois semaines, le crash qui a révélé son inutilité, la mécanique précise par laquelle GiST trigram peut s'effondrer sur certains patterns, et pourquoi pg_hint_plan reste à manier avec parcimonie même quand il sauve une nuit.

Prérequis avant de commencer

Avant de transposer ce qui suit dans ton contexte, vérifie que ton environnement coche ces cases. Si l'un d'eux manque, le détour par pg_hint_plan ne sera pas la bonne réponse.

• Postgres 14 ou plus, accès superuser pour modifier shared_preload_libraries (ou tu opères ton propre cluster, pas un Postgres managé sans option).
• Une recherche ILIKE substring sur du texte long, multilingue, et un volume qui dépasse le million de lignes : c'est là que le choix d'index trigram devient critique.
• Un EXPLAIN ANALYZE déjà capté en prod sur une requête lente. Sans ANALYZE, tu lis les estimations du planner, pas la réalité.
• Possibilité de redémarrer ton instance pour charger shared_preload_libraries, ou un opérateur Kubernetes (CloudNativePG, Crunchy, Zalando) qui pilote ce redémarrage proprement.
• Tolérance à un workflow d'image custom : si tu refuses par principe de builder ton propre conteneur Postgres, l'option pg_hint_plan est fermée pour toi.

Sans ces conditions, la méthode présentée ici est inadaptée. Tu trouveras à la fin un rappel des alternatives.

Pourquoi un trigram à côté de pgvector

Notre stack RAG est hybride : pgvector pour la similarité sémantique sur les embeddings Cohere, et pg_trgm pour les ILIKE substring sur le texte brut, avec dédupe et rerank Cohere derrière. Les deux requêtes tournent en parallèle. La logique : les embeddings ratent les noms propres, les références juridiques, les codes d'articles, et certains termes rares dans des langues sous-représentées dans les modèles. Le trigram rattrape ces angles morts.

On a documenté la partie pgvector dans un article dédié sur l'indexation HNSW d'un RAG d'1 To sans downtime. Cet article-ci s'attaque à l'autre moitié, celle qui paraît la plus innocente : ajouter un index trigram sur une colonne texte. Spoiler : c'est là que les surprises se cachent.

flowchart LR
    User[Avocat - requête utilisateur]
    API[API RAG]
    Embed[Cohere Embed v4]
    PGV[(pgvector HNSW<br/>9M embeddings)]
    TRGM[(pg_trgm GIN<br/>5,9 GB)]
    Dedupe[Dédupe + rerank Cohere]
    LLM[LLM réponse]

    User --> API
    API --> Embed
    Embed --> PGV
    API --> TRGM
    PGV --> Dedupe
    TRGM --> Dedupe
    Dedupe --> LLM
    LLM --> User

Le diagramme cache l'enjeu : ces deux requêtes tournent en parallèle dans la même transaction RAG. Si l'une des deux dérape, l'utilisateur attend la plus lente. Un ILIKE qui timeout à 38 minutes met l'agent dans un état dégradé pour tous les utilisateurs partageant le pod.

Le faux fix : SET LOCAL enable_indexscan = off

Courant avril, on déploie la migration Drizzle qui crée l'index GIN trigram sur la colonne texte des chunks documentaires :

CREATE INDEX CONCURRENTLY document_chunk_text_trgm_idx
ON "DocumentChunk" USING gin (text gin_trgm_ops);

Quelques heures plus tard, on observe un comportement louche en staging. Le planner choisit parfois un index GiST trigram (24 GB, présent pour le ranking par similarité <% sur d'autres requêtes) plutôt que le GIN (5,9 GB) sur certains ILIKE. Sur le papier, GIN devrait toujours gagner sur ILIKE substring. Mais le coût estimé varie selon la sélectivité estimée et la cardinalité, et le planner peut se tromper.

Le réflexe : forcer le planner à éviter les Index Scan dans la transaction concernée.

BEGIN;
SET LOCAL enable_indexscan = off;
SELECT "id", "text" FROM "DocumentChunk" WHERE "text" ILIKE '%terme%' LIMIT 50;
COMMIT;

EXPLAIN ANALYZE donne après ce changement un plan qui utilise le GIN. On respire, on déploie, on passe à autre chose. Ce fix était un no-op complet, mais on a mis trois semaines à le découvrir.

:::tip GIN vs GiST trigram en 30 secondes

• GIN est un inverted index. Pour chaque trigramme distinct, il maintient la liste des lignes qui le contiennent. Un ILIKE '%toto%' se résout en intersection des listes pour ' to', 'tot', 'oto', 'to '. Pas de faux positifs au niveau de l'index. Idéal pour ILIKE substring exact.
• GiST stocke des signatures hashées de taille fixe (12 bytes par défaut, configurable jusqu'à 2024 via siglen). Plusieurs trigrammes peuvent partager la même signature : c'est lossy. Un match de signature impose un recheck ligne par ligne. Idéal pour <% similarity ranking et nearest-neighbor, médiocre pour les ILIKE substring sur des patterns peu discriminants.
• Taille relative observée chez nous : GIN 5,9 GB vs GiST 24 GB sur le même corpus. GIN est plus compact mais plus lent à mettre à jour, GiST est plus volumineux mais plus rapide en écriture. La doc Postgres résume historiquement par « GIN faster for searches, GiST faster to update ».

:::

Quelques jours plus tard, en pleine nuit

Un avocat tape « stiefkinderen » dans son espace personnel. La requête tourne. Et tourne. Au bout de 38 minutes, le statement_timeout n'a pas suffi (mal câblé sur le pool, c'est une autre histoire), Sentry crame sous les exceptions agrégées, le pod RAG est saturé.

EXPLAIN (ANALYZE, BUFFERS) sur la requête isolée révèle la vérité.

Bitmap Heap Scan on "DocumentChunk"  (actual time=2280112.4..2281024.7 rows=12 loops=1)
  Recheck Cond: (text ~~* '%stiefkinderen%')
  Rows Removed by Index Recheck: 4127884
  Heap Blocks: exact=812441
  ->  Bitmap Index Scan on document_chunk_text_trgm_gist_idx
        (actual time=18.3..18.3 rows=4127896 loops=1)
        Index Cond: (text ~~* '%stiefkinderen%')
Planning Time: 0.412 ms
Execution Time: 2281039.802 ms

Trois choses sautent aux yeux. Premièrement, l'index utilisé est bien le GiST trigram, pas le GIN. Deuxièmement, l'index a renvoyé 4,1 millions de candidats à valider, dont 4 127 884 ont été éliminés au recheck. Soit un taux de faux positifs à l'index supérieur à 99,99 %. Troisièmement, le node n'est pas un Index Scan mais un Bitmap Index Scan, ce qui explique pourquoi notre fix d'avril était inutile.

:::gotcha enable_indexscan ne désactive pas l'index

SET enable_indexscan = off désactive uniquement le node Index Scan dans le planner. Le planner reste libre de choisir un Bitmap Index Scan sur le même index. Or Bitmap Index Scan est un autre node, contrôlé par enable_bitmapscan (qui est on par défaut).

Pour vraiment exclure un index spécifique, il n'existe pas de mécanisme natif propre dans Postgres standard. Les options réalistes sont : DROP INDEX (radical), passer le GUC enable_bitmapscan = off aussi (mais ça affecte tout le reste), ou installer pg_hint_plan pour cibler chirurgicalement la requête.

La leçon : lire un EXPLAIN ANALYZE jusqu'au bout, et vérifier le nom du node, pas seulement « ça utilise un index ».

:::

Pourquoi GiST trigram sature sur certains patterns

À ce stade, une question se pose : pourquoi « stiefkinderen » et pas « contrat » ? Pourquoi cette dérive non déterministe ?

La réponse tient dans la mécanique des signatures GiST. La classe d'opérateurs gist_trgm_ops représente l'ensemble des trigrammes d'une ligne par un bitmap de taille fixe (12 bytes par défaut, soit 96 bits). Chaque trigramme est haché en une position de bit, plusieurs trigrammes peuvent collisionner sur le même bit, c'est lossy par construction. La doc officielle de pg_trgm précise : « Longer signatures lead to a more precise search (scanning a smaller fraction of the index and fewer heap pages), at the cost of a larger index. »

Sur du texte légal multilingue, on a beaucoup de trigrammes très fréquents : ' de', ' et', 'ie ', 'en ', 'er ', 'ten', 'sch'. Ces trigrammes pollinent rapidement la quasi-totalité des bits du bitmap. La signature ne discrimine plus rien. Quand « stiefkinderen » est décomposé, il contient surtout des trigrammes communs en néerlandais ('ie ', 'en ', 'er ', etc.). Au moment de chercher la signature dans l'index, presque toutes les signatures de la base matchent. Le recheck doit alors réexaminer 4 millions de lignes pour en valider 12. C'est l'effondrement.

À l'inverse, un mot comme « stipendium » contient des trigrammes plus rares ('sti', 'pen', 'diu') qui discriminent mieux les signatures. Le recheck reste raisonnable. Le comportement dépend littéralement du contenu de la requête et du contenu de la base, ce qui le rend difficile à reproduire en test si le corpus de staging n'est pas représentatif. Augmenter siglen à 256 ou 1024 atténue le problème mais ne le supprime pas, et ça multiplie la taille de l'index (benchmark Klibisz, 2022).

Le GIN, lui, n'a pas de signature. Il maintient la liste exacte des lignes par trigramme. Pas de faux positifs au niveau index, donc pas d'effondrement par saturation. C'est pour ça que sur ILIKE substring, GIN n'a pas d'équivalent en pathologie possible. La seule mauvaise surprise possible, c'est sa taille en écriture lourde, qu'on n'a pas dans notre cas (corpus quasi append-only).

La vraie solution : pg_hint_plan + image custom CloudNativePG

On a deux choix : supprimer l'index GiST (mais on en a besoin pour le ranking par similarité <%) ou forcer le planner à toujours utiliser GIN sur ILIKE. La deuxième option est plus propre, à condition d'avoir un mécanisme déterministe.

pg_hint_plan, maintenu par NTT Open Source (dépôt GitHub, 895 stars, mises à jour régulières), permet d'embarquer des hints en commentaire SQL directement dans la requête : /*+ BitmapScan(table index) */. Le planner les parse au moment de la planification et force le node + l'index demandé.

Le piège opérationnel : pg_hint_plan doit être chargée via shared_preload_libraries. Ce n'est pas une simple CREATE EXTENSION. Conséquence directe pour les Postgres managés : sur RDS, Cloud SQL, Neon, Supabase, ce n'est typiquement pas disponible et la décision dépend de l'éditeur. Sur CloudNativePG, qui est notre opérateur Kubernetes, la voie passe par une image Postgres custom.

Le Dockerfile tient en quelques lignes :

FROM ghcr.io/cloudnative-pg/postgresql:18.3
USER root
RUN apt-get update \
    && apt-get install -y --no-install-recommends \
       postgresql-18-pg-hint-plan \
    && rm -rf /var/lib/apt/lists/*
USER 26

Le tag 26 est l'UID Postgres dans l'image officielle CNPG. Côté CI, un workflow GitHub Actions build et push l'image sur déclenchement par changement du Dockerfile, plus un workflow_dispatch manuel pour rebuilder à chaque CVE Postgres dans l'intervalle.

Côté Cluster CRD CloudNativePG, on déclare l'image et on ajoute la lib aux preloads :

spec:
  imageName: ghcr.io/your-org/postgres-pg-hint-plan:18.3
  postgresql:
    shared_preload_libraries:
      - pg_hint_plan
    parameters:
      pg_hint_plan.enable_hint: "on"

Après rolling switchover, on peut faire CREATE EXTENSION pg_hint_plan; dans la base concernée. Et la requête devient :

SELECT /*+ BitmapScan("DocumentChunk" document_chunk_text_trgm_idx) */
  "chunkIndex", "documentId", "id", "text"
FROM "DocumentChunk"
WHERE "text" ILIKE $1
LIMIT $2 OFFSET $3

Le hint cible le node Bitmap Index Scan ET l'index spécifique document_chunk_text_trgm_idx (notre GIN). Le planner ne peut plus dériver. C'est déterministe, indépendant des stats, et lisible côté code parce que le hint vit dans le commentaire SQL versionné.

:::warning pg_hint_plan sur Postgres managé

• AWS RDS, Cloud SQL, Neon, Supabase : indisponible par défaut. Demande à ton support, mais ne mise rien dessus. RDS Aurora Postgres supporte certaines extensions, vérifie la liste blanche pour ta région.
• Postgres autonome (Debian/Ubuntu) : apt install postgresql-XX-pg-hint-plan (XX = version majeure), puis ajouter pg_hint_plan à shared_preload_libraries dans postgresql.conf et redémarrer.
• CloudNativePG : possible via image custom + Cluster.spec.postgresql.shared_preload_libraries. Note que CNPG 1.29 a introduit le mécanisme d'Image Volume Extensions qui rend cette opération encore plus propre, sans rebuild de l'image principale.
• Crunchy Postgres for Kubernetes : extensions tierces gérées via customExtensionImage, mécanisme analogue à CNPG.

:::

Le résultat mesuré en prod : 38 minutes vers 890 ms

Une fois pg_hint_plan chargée et le hint déployé, on rejoue la requête pathologique. 890 ms cold cache, ~120 ms warm cache, sur le même corpus, le même node, la même requête. Soit un facteur ~2500 par rapport aux 38 minutes initiales.

Plus important : on a rejoué l'ensemble des patterns qui avaient dérivé dans les semaines précédentes (une douzaine de mots néerlandais ou français aux trigrammes communs). Tous sont sous le seuil des 1,5 secondes, dans une distribution étroite. La variance pathologique a disparu.

Le EXPLAIN ANALYZE confirme que le hint est bien pris en compte (pg_hint_plan log un message LOG: pg_hint_plan: hint syntax error ou LOG: pg_hint_plan: hint accepted dans les logs Postgres si tu actives pg_hint_plan.message_level = log, utile en debug).

GIN vs GiST trigram : le tableau de décision

Tableau synthétique des trade-offs, à confronter à ton workload réel.

La règle pragmatique qui se dégage : un index GIN trigram pour les ILIKE substring, un index GiST trigram en plus si tu as besoin de ranking par similarité ou de NN. Les deux peuvent cohabiter sur la même colonne, c'est ce qu'on fait. Le piège est que le planner, en l'absence de hint, peut choisir le mauvais des deux selon les estimations, et ça se voit en cas extrême comme « stiefkinderen ».

Pourquoi on n'utilise pas pg_hint_plan partout

pg_hint_plan règle un problème précis. Ça ne veut pas dire qu'il faut le déployer en prophylactique sur toutes les requêtes lentes.

:::our-take Forcer le planner reste un dernier recours

• Forcer le planner, c'est admettre que ses estimations sont fausses pour cette requête, et qu'on les corrige à la main. Le coût caché : le hint ne s'adapte pas si la donnée change. Si demain notre index GIN est démesurément grossi par un autoremplissage et qu'un Seq Scan deviendrait plus rapide, le hint forcera quand même le BitmapScan. On a échangé une dérive du planner contre une dérive de la décision figée.
• Notre règle : on ne pose un hint que quand (1) le pattern est connu et stable (chez nous, ILIKE substring + GIN trigram = vrai dans 100 % des cas, par construction), (2) le coût d'erreur est asymétrique (38 minutes vs 890 ms, c'est un facteur 2500), (3) on a essayé ANALYZE, des index partiels, ou une réécriture de requête avant.
• On préfère un hint inline en commentaire SQL (versionné dans le code applicatif, visible en code review) à un GUC global ou une pg_hint_plan.hint table. Le périmètre du hint reste local à la requête concernée. Si demain on supprime la requête, le hint disparaît avec elle.

:::

Le piège qu'on a vraiment vécu, en synthèse

Symptôme. Une requête ILIKE qui tournait sub-seconde sur 99 % des termes, et atteignait plusieurs minutes (jusqu'à 38 mesurées) sur certains termes apparemment anodins. Pas de pattern évident côté code, pas d'erreur Sentry au niveau application avant la saturation.

Diagnostic. EXPLAIN ANALYZE en prod sur la requête isolée a révélé que le planner utilisait un index GiST trigram avec un taux de faux positifs supérieur à 99,99 %. Notre fix précédent (SET enable_indexscan = off) ne désactivait que le node Index Scan, pas le Bitmap Index Scan, donc l'index pourri était toujours utilisé. Le tout aggravé par des signatures GiST saturées sur des trigrammes très fréquents en néerlandais.

Fix. Build d'une image Postgres custom embarquant pg_hint_plan (apt install postgresql-18-pg-hint-plan sur l'image officielle CloudNativePG), ajout de la lib aux shared_preload_libraries, rolling switchover via CNPG, puis hint inline /*+ BitmapScan(table index) */ dans la requête. Délai entre crash et fix en prod : moins de 24 heures, dont 4 heures de build/test image et 20 minutes de switchover.

FAQ pg_hint_plan et trigrams en prod

Q : Faut-il toujours créer un GIN trigram plutôt qu'un GiST ?

R : Pour des ILIKE substring sur du texte long, oui. Pour du ranking par similarité (%, <%), de la suggestion de typo, ou du nearest-neighbor sur trigrammes, le GiST reste pertinent. Les deux peuvent cohabiter, c'est même souvent le bon choix.

Q : Augmenter siglen GiST suffit-il à éviter le problème ?

R : Ça atténue, ça ne supprime pas. Sur le benchmark Klibisz, passer siglen de 64 à 256 réduit la latence d'un facteur 2 à 3. Mais sur un corpus multilingue avec des trigrammes très fréquents, même siglen=2024 (le max) ne ramènera pas le taux de faux positifs au niveau d'un GIN. Et l'index quadruple en taille.

Q : Comment savoir si pg_hint_plan est bien chargé ?

R : Trois vérifications. (1) SHOW shared_preload_libraries; doit lister pg_hint_plan. (2) SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_hint_plan'; doit retourner une ligne après CREATE EXTENSION. (3) Active SET pg_hint_plan.message_level = log; puis exécute une requête avec hint, l'event log Postgres doit afficher LOG: pg_hint_plan: hint accepted.

Q : Le hint est-il portable d'une version Postgres à l'autre ?

R : Oui pour la syntaxe (/*+ ... */), mais le nom de l'extension est versionné côté package Debian (postgresql-18-pg-hint-plan pour Postgres 18). Lors d'une montée de version majeure, prévoir un rebuild de l'image custom avec le bon paquet et tester les hints existants en non-régression. L'extension est maintenue à jour pour Postgres 14 à 19.

Q : Quel impact pg_hint_plan a-t-il sur les requêtes sans hint ?

R : Quasiment nul, l'extension scanne les commentaires en début de requête. Le coût est mesurable mais largement sous la milliseconde, négligeable face au temps de planning standard. Aucune raison de désactiver l'extension globalement « par précaution ».

Pour conclure

L'épisode « stiefkinderen » rejoint la collection des incidents qui font progresser sur l'opérationnel Postgres : pas exotique, pas spectaculaire, juste une combinaison non triviale entre un planner trop confiant, un index lossy, et un fix qui n'en était pas un. Le coût d'apprentissage est un cratère de 38 minutes en prod et trois semaines de fausse sécurité. Le bénéfice est durable : on sait maintenant lire un EXPLAIN ANALYZE jusqu'au nom exact du node, on a pg_hint_plan chargée par défaut sur tous nos clusters CloudNativePG, et on a une convention claire pour les ILIKE substring (GIN trigram + hint).

L'agence Platane (https://platane.io) opère plusieurs plateformes IA et SaaS sur ce type d'architecture, dont Jef, l'assistant IA du Barreau de Bruxelles, un RAG juridique multi-tenant avec isolation totale par avocat, sur cluster Kubernetes Scaleway en France et stream WAL offshore vers OVH. La maîtrise du combo pgvector + pg_trgm + pg_hint_plan dans ce contexte fait partie de notre expertise solutions IA. Pour le pendant côté embeddings, on a documenté en détail comment indexer un RAG d'1 To sans downtime sur le même produit.

pg_hint_plan n'est pas une solution miracle, c'est un scalpel. Il coupe précis quand on connaît exactement ce qu'on veut couper. La prochaine fois qu'un EXPLAIN ANALYZE révèle un Bitmap Index Scan sur le mauvais index, on saura qu'il existe une option entre « subir » et « DROP INDEX puis prier ».