Skip to content

03.4-index-gin-performance.md

Latest commit

 

History

History
1073 lines (830 loc) · 30.2 KB

03.4-index-gin-performance.md

File metadata and controls

1073 lines (830 loc) · 30.2 KB

🔝 Retour au Sommaire

18.3.4. Index GIN pour la Performance

Introduction

Imaginez que vous avez une table avec 10 millions d'articles de blog. Sans index, une recherche textuelle pourrait prendre plusieurs minutes car PostgreSQL doit scanner chaque ligne pour vérifier si elle correspond à votre requête. Avec un index GIN bien configuré, la même recherche s'exécute en quelques millisecondes.

Les index GIN (Generalized Inverted Index) sont indispensables pour obtenir de bonnes performances avec le Full-Text Search. Ils transforment une opération O(n) en O(log n), soit un gain de performance de 100× à 10 000× selon la taille des données.

Exemple concret :

-- SANS index GIN : 45 secondes sur 10M d'articles
SELECT titre FROM articles  
WHERE to_tsvector('french', contenu) @@ plainto_tsquery('french', 'postgresql');  
-- Plan : Seq Scan (balayage séquentiel)

-- AVEC index GIN : 15 millisecondes
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);  
SELECT titre FROM articles  
WHERE search_vector @@ plainto_tsquery('french', 'postgresql');  
-- Plan : Bitmap Index Scan (utilise l'index)

Gain : 3000× plus rapide !

1. Qu'est-ce qu'un Index GIN ?

GIN : Generalized Inverted Index

Un index inversé (inverted index) fonctionne à l'opposé d'un index normal :

Index normal (B-Tree) : Document → Mots

Document 1 → ["postgresql", "base", "données"]  
Document 2 → ["mysql", "base", "données"]  
Document 3 → ["postgresql", "performance"]

Index inversé (GIN) : Mot → Documents

"postgresql" → [Document 1, Document 3]
"base"       → [Document 1, Document 2]
"données"    → [Document 1, Document 2]
"mysql"      → [Document 2]
"performance"→ [Document 3]

Avec un index inversé, pour trouver tous les documents contenant "postgresql", il suffit de faire une seule recherche dans l'index !

Structure d'un Index GIN

Index GIN sur search_vector
│
├── Lexème: "base"
│   ├── Document ID: 1
│   ├── Document ID: 2
│   ├── Document ID: 5
│   └── Document ID: 8
│
├── Lexème: "donn"
│   ├── Document ID: 1
│   ├── Document ID: 2
│   └── Document ID: 9
│
├── Lexème: "postgresql"
│   ├── Document ID: 1
│   ├── Document ID: 3
│   ├── Document ID: 7
│   └── Document ID: 10
│
└── ...

Pourquoi GIN est Parfait pour Full-Text Search

  1. Recherche rapide : O(log n) au lieu de O(n)
  2. Opérateurs complexes : AND, OR, NOT nativement supportés
  3. Compression efficace : Les listes de documents sont compressées
  4. Scalabilité : Performances constantes même avec des milliards de mots

2. Créer un Index GIN

Syntaxe de Base

CREATE INDEX nom_index ON nom_table USING GIN(colonne_tsvector);

Prérequis : Colonne tsvector

Les index GIN ne peuvent être créés que sur des colonnes de type tsvector :

-- ❌ IMPOSSIBLE : Index GIN sur colonne TEXT
CREATE INDEX idx_bad ON articles USING GIN(contenu);
-- Erreur : data type text has no default operator class for access method "gin"

-- ✅ BON : Index GIN sur colonne tsvector
ALTER TABLE articles ADD COLUMN search_vector tsvector;  
UPDATE articles SET search_vector = to_tsvector('french', contenu);  
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

Exemple Complet : De Zéro à l'Index

-- 1. Créer la table
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    titre VARCHAR(200),
    contenu TEXT,
    auteur VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 2. Insérer des données (simuler 100k articles)
INSERT INTO articles (titre, contenu, auteur)  
SELECT  
    'Article ' || i,
    'Contenu de l''article numéro ' || i || '. ' ||
    CASE WHEN i % 100 = 0 THEN 'PostgreSQL est mentionné ici.' ELSE '' END,
    'Auteur ' || (i % 50)
FROM generate_series(1, 100000) AS i;

-- 3. Ajouter la colonne tsvector
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- 4. Remplir la colonne tsvector
UPDATE articles  
SET search_vector =  
    setweight(to_tsvector('french', COALESCE(titre, '')), 'A') ||
    setweight(to_tsvector('french', COALESCE(contenu, '')), 'B');

-- 5. Créer l'index GIN
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- 6. Analyser la table (mettre à jour les statistiques)
ANALYZE articles;

Créer un Index avec Expression

Si vous n'avez pas de colonne tsvector dédiée, vous pouvez créer un index sur une expression :

-- Index GIN sur expression (sans colonne dédiée)
CREATE INDEX idx_articles_content_search  
ON articles  
USING GIN(to_tsvector('french', contenu));  

-- Requête qui utilise l'index
SELECT titre FROM articles  
WHERE to_tsvector('french', contenu) @@ plainto_tsquery('french', 'postgresql');

⚠️ Attention : Les index sur expressions sont plus lents que sur des colonnes dédiées car le tsvector doit être recalculé pour les insertions/mises à jour.

Recommandation : Toujours utiliser une colonne tsvector dédiée pour de meilleures performances.

3. Performance : Avant vs Après Index GIN

Benchmark Réel

Testons sur une table de 100 000 articles :

Sans Index GIN

-- Désactiver l'utilisation des index (pour tester sans)
SET enable_indexscan = off;  
SET enable_bitmapscan = off;  

-- Recherche sans index
EXPLAIN (ANALYZE, BUFFERS)  
SELECT titre, auteur  
FROM articles  
WHERE search_vector @@ plainto_tsquery('french', 'postgresql');

Résultat :

Seq Scan on articles  (cost=0.00..5234.00 rows=50 width=68)
                      (actual time=0.045..245.678 rows=1000 loops=1)
  Filter: (search_vector @@ plainto_tsquery('french'::regconfig, 'postgresql'::text))
  Rows Removed by Filter: 99000
  Buffers: shared hit=2234
Planning Time: 0.234 ms  
Execution Time: 245.912 ms  ← 246 ms

Interprétation :

  • Seq Scan : Balayage séquentiel de toute la table
  • 99 000 lignes filtrées : Scan de 100% de la table
  • 246 ms : Temps d'exécution

Avec Index GIN

-- Réactiver les index
RESET enable_indexscan;  
RESET enable_bitmapscan;  

-- Même recherche avec index
EXPLAIN (ANALYZE, BUFFERS)  
SELECT titre, auteur  
FROM articles  
WHERE search_vector @@ plainto_tsquery('french', 'postgresql');

Résultat :

Bitmap Heap Scan on articles  (cost=50.45..234.67 rows=50 width=68)
                              (actual time=1.234..3.456 rows=1000 loops=1)
  Recheck Cond: (search_vector @@ plainto_tsquery('french'::regconfig, 'postgresql'::text))
  Heap Blocks: exact=987
  Buffers: shared hit=1012
  ->  Bitmap Index Scan on idx_articles_search  (cost=0.00..50.44 rows=50 width=0)
                                                  (actual time=1.123..1.123 rows=1000 loops=1)
        Index Cond: (search_vector @@ plainto_tsquery('french'::regconfig, 'postgresql'::text))
        Buffers: shared hit=25
Planning Time: 0.189 ms  
Execution Time: 3.678 ms  ← 3.7 ms

Interprétation :

  • Bitmap Index Scan : Utilise l'index GIN
  • 25 blocs d'index : Seulement l'index consulté
  • 3.7 ms : Temps d'exécution

Gain : 67× plus rapide (246ms → 3.7ms) !

Comparaison Visuelle

Sans Index GIN :
[██████████████████████████████████████████████████] 246 ms
Scan de 100 000 lignes

Avec Index GIN :
[███] 3.7 ms
Scan de 1 000 lignes (celles qui matchent)

4. Opérations Supportées par les Index GIN

Les index GIN accélèrent l'opérateur @@ et ses variantes :

Opérateur @@

-- ✅ Utilise l'index GIN
SELECT * FROM articles  
WHERE search_vector @@ to_tsquery('french', 'postgresql');  

-- ✅ Utilise l'index GIN
SELECT * FROM articles  
WHERE search_vector @@ plainto_tsquery('french', 'postgresql base données');  

-- ✅ Utilise l'index GIN
SELECT * FROM articles  
WHERE search_vector @@ websearch_to_tsquery('french', 'postgresql "base données"');

Opérations Complexes

-- ✅ AND : Utilise l'index
SELECT * FROM articles  
WHERE search_vector @@ to_tsquery('french', 'postgresql & base');  

-- ✅ OR : Utilise l'index
SELECT * FROM articles  
WHERE search_vector @@ to_tsquery('french', 'postgresql | mysql');  

-- ✅ NOT : Utilise l'index
SELECT * FROM articles  
WHERE search_vector @@ to_tsquery('french', 'base & !oracle');  

-- ✅ Phrases : Utilise l'index
SELECT * FROM articles  
WHERE search_vector @@ phraseto_tsquery('french', 'base de données');

Opérations NON Supportées

-- ❌ N'utilise PAS l'index (fonction sur tsvector)
SELECT * FROM articles  
WHERE length(search_vector) > 10;  

-- ❌ N'utilise PAS l'index (opérateur non supporté)
SELECT * FROM articles  
WHERE search_vector > to_tsquery('french', 'postgresql');

5. Options et Paramètres d'Index GIN

fastupdate : Accélérer les Écritures

Par défaut, les insertions/mises à jour sont immédiatement appliquées à l'index GIN, ce qui peut être lent.

L'option fastupdate permet de mettre en attente les mises à jour dans une liste temporaire :

-- Créer un index GIN avec fastupdate activé
CREATE INDEX idx_articles_search  
ON articles  
USING GIN(search_vector)  
WITH (fastupdate = on);

Comment ça marche :

  1. Les nouvelles entrées sont stockées dans une pending list (liste d'attente)
  2. Périodiquement, la pending list est fusionnée avec l'index principal
  3. Les recherches consultent index + pending list

Avantages :

  • ✅ Insertions/mises à jour plus rapides (10-50%)
  • ✅ Moins de fragmentation de l'index

Inconvénients :

  • ⚠️ Recherches légèrement plus lentes si la pending list est grande
  • ⚠️ Consommation mémoire supplémentaire

Recommandation :

  • ON : Pour tables avec insertions/mises à jour fréquentes
  • OFF : Pour tables principalement en lecture

gin_pending_list_limit : Taille de la Liste d'Attente

Définit la taille maximale de la pending list (par index) :

-- Par défaut : 4 MB
SHOW gin_pending_list_limit;
-- Résultat : 4MB

-- Augmenter pour tables avec beaucoup d'écritures
SET gin_pending_list_limit = '16MB';

-- Ou au niveau de l'index
CREATE INDEX idx_articles_search  
ON articles  
USING GIN(search_vector)  
WITH (fastupdate = on, gin_pending_list_limit = 16384);  -- 16 MB

Impact :

  • Petite valeur (1-4 MB) : Fusions fréquentes, écritures plus lentes mais recherches rapides
  • Grande valeur (16-64 MB) : Fusions moins fréquentes, écritures rapides mais recherches un peu plus lentes

Créer l'Index CONCURRENTLY

Pour éviter de bloquer la table pendant la création de l'index :

-- Index créé sans bloquer les écritures/lectures
CREATE INDEX CONCURRENTLY idx_articles_search  
ON articles  
USING GIN(search_vector);

Avantages :

  • ✅ Aucun verrou exclusif sur la table
  • ✅ Applications continuent de fonctionner

Inconvénients :

  • ⚠️ Création 2-3× plus lente
  • ⚠️ Utilise plus de ressources (CPU, I/O)

Recommandation : Toujours utiliser CONCURRENTLY en production.

6. Maintenance des Index GIN

Pourquoi la Maintenance est Importante

Avec le temps, les index GIN peuvent :

  • Gonfler (bloat) après de nombreuses suppressions/mises à jour
  • Accumuler des entrées dans la pending list
  • Devenir fragmentés

Symptômes d'un index dégradé :

  • Recherches de plus en plus lentes
  • Taille de l'index anormalement grande
  • EXPLAIN montre des coûts élevés

VACUUM : Nettoyer l'Index

-- Vacuum standard (nettoie la pending list)
VACUUM articles;

-- Vacuum verbose (avec détails)
VACUUM VERBOSE articles;

Sortie :

INFO:  vacuuming "public.articles"  
INFO:  index "idx_articles_search" now contains 100000 row versions in 2345 pages  
DETAIL:  0 index row versions were removed.  
500 index pages have been deleted, 0 are currently reusable.

REINDEX : Reconstruire l'Index

Pour reconstruire complètement un index :

-- Reindex un index spécifique (bloque les écritures)
REINDEX INDEX idx_articles_search;

-- Reindex tous les index d'une table
REINDEX TABLE articles;

-- Reindex sans bloquer (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_articles_search;

Quand reindexer ?

  • Après des suppressions massives (>50% de la table)
  • Si l'index a 2× la taille attendue (bloat important)
  • Si les performances se dégradent malgré VACUUM
  • Migration de version majeure PostgreSQL

Surveiller la Taille des Index

-- Taille des index GIN
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS taille_index,
    idx_scan AS nombre_scans,
    idx_tup_read AS tuples_lus
FROM pg_stat_user_indexes  
WHERE indexrelname LIKE 'idx_%search%'  
ORDER BY pg_relation_size(indexrelid) DESC;

Résultat exemple :

schemaname | tablename | indexname           | taille_index | nombre_scans | tuples_lus
-----------+-----------+---------------------+--------------+--------------+------------
public     | articles  | idx_articles_search | 45 MB        | 12543        | 2345678

Estimer le Bloat

-- Extension pgstattuple pour analyser le bloat
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Analyser un index GIN
SELECT
    indexname,
    leaf_pages,
    deleted_pages,
    ROUND(100.0 * deleted_pages / NULLIF(leaf_pages, 0), 2) AS bloat_percent
FROM pgstatginindex('idx_articles_search');

Interprétation :

  • bloat_percent < 10% : Index sain
  • bloat_percent 10-30% : Bloat modéré, VACUUM suffit
  • bloat_percent > 30% : Bloat important, REINDEX recommandé

7. Optimisations Avancées

7.1. Index Partiel (Partial Index)

Créer un index uniquement sur un sous-ensemble des lignes :

-- Index uniquement sur les articles publiés
CREATE INDEX idx_articles_published_search  
ON articles  
USING GIN(search_vector)  
WHERE statut = 'publié';  

-- Requête qui utilise l'index
SELECT titre FROM articles  
WHERE search_vector @@ plainto_tsquery('french', 'postgresql')  
  AND statut = 'publié';  -- ← Condition de l'index

Avantages :

  • ✅ Index plus petit (moins d'espace)
  • ✅ Maintenance plus rapide
  • ✅ Recherches plus rapides sur le sous-ensemble

Cas d'usage :

  • Articles publiés vs brouillons
  • Produits en stock vs rupture
  • Documents actifs vs archivés

7.2. Combiner GIN avec Index B-Tree

Pour des requêtes avec plusieurs filtres :

-- Index GIN pour la recherche textuelle
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- Index B-Tree pour les filtres classiques
CREATE INDEX idx_articles_date ON articles(created_at);  
CREATE INDEX idx_articles_auteur ON articles(auteur);  

-- Requête combinée (PostgreSQL choisit les index optimaux)
SELECT titre, created_at  
FROM articles  
WHERE search_vector @@ plainto_tsquery('french', 'postgresql')  
  AND created_at > NOW() - INTERVAL '1 month'
  AND auteur = 'Jean Dupont';

PostgreSQL peut :

  • Utiliser l'index GIN pour filtrer par texte
  • Puis l'index B-Tree pour filtrer par date
  • Ou vice versa, selon les statistiques

7.3. Paramètres PostgreSQL pour GIN

Ajuster les paramètres globaux pour optimiser les index GIN :

-- Mémoire pour la construction d'index
SET maintenance_work_mem = '256MB';  -- Défaut : 64MB
-- Impact : Index créés plus rapidement

-- Mémoire pour la pending list (par session)
SET gin_pending_list_limit = '16MB';  -- Défaut : 4MB
-- Impact : Moins de fusions, écritures plus rapides

-- Afficher les paramètres GIN actuels
SHOW maintenance_work_mem;  
SHOW gin_pending_list_limit;

Recommandations :

  • maintenance_work_mem : 5-10% de la RAM pour CREATE INDEX
  • gin_pending_list_limit : 16-32 MB pour tables avec beaucoup d'écritures

8. Cas Pratiques Complets

Cas 1 : Blog avec Millions d'Articles

-- Structure
CREATE TABLE blog_articles (
    id SERIAL PRIMARY KEY,
    titre VARCHAR(200),
    contenu TEXT,
    auteur VARCHAR(100),
    categorie VARCHAR(50),
    statut VARCHAR(20) DEFAULT 'brouillon',  -- 'brouillon', 'publié', 'archivé'
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    search_vector tsvector
);

-- Trigger pour maintenir search_vector
CREATE OR REPLACE FUNCTION blog_search_trigger() RETURNS trigger AS $$  
BEGIN  
    NEW.search_vector :=
        setweight(to_tsvector('french', COALESCE(NEW.titre, '')), 'A') ||
        setweight(to_tsvector('french', COALESCE(NEW.contenu, '')), 'B') ||
        setweight(to_tsvector('french', COALESCE(NEW.auteur, '')), 'D');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsvector_update  
BEFORE INSERT OR UPDATE ON blog_articles  
FOR EACH ROW EXECUTE FUNCTION blog_search_trigger();  

-- Index GIN partiel (uniquement articles publiés)
CREATE INDEX idx_blog_published_search  
ON blog_articles  
USING GIN(search_vector)  
WHERE statut = 'publié'  
WITH (fastupdate = on, gin_pending_list_limit = 16384);  

-- Index B-Tree complémentaires
CREATE INDEX idx_blog_date ON blog_articles(created_at DESC);  
CREATE INDEX idx_blog_categorie ON blog_articles(categorie);  

-- Requête optimisée
SELECT
    id,
    titre,
    LEFT(contenu, 200) AS extrait,
    created_at,
    ts_rank(search_vector, query) AS score
FROM
    blog_articles,
    websearch_to_tsquery('french', 'postgresql base données') query
WHERE
    statut = 'publié'  -- Utilise l'index partiel
    AND search_vector @@ query  -- Utilise l'index GIN
    AND created_at > NOW() - INTERVAL '1 year'  -- Peut utiliser idx_blog_date
ORDER BY score DESC  
LIMIT 20;

Cas 2 : E-commerce avec Recherche Produits

-- Structure
CREATE TABLE produits (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(200),
    description TEXT,
    marque VARCHAR(100),
    categorie VARCHAR(50),
    prix NUMERIC(10, 2),
    stock INTEGER,
    actif BOOLEAN DEFAULT true,
    search_vector tsvector
);

-- Trigger avec poids adaptés e-commerce
CREATE OR REPLACE FUNCTION produits_search_trigger() RETURNS trigger AS $$  
BEGIN  
    NEW.search_vector :=
        -- Nom produit : très important (doublé)
        setweight(to_tsvector('french', COALESCE(NEW.nom, '')), 'A') ||
        setweight(to_tsvector('french', COALESCE(NEW.nom, '')), 'A') ||
        -- Marque : important
        setweight(to_tsvector('french', COALESCE(NEW.marque, '')), 'A') ||
        -- Description : normal
        setweight(to_tsvector('french', COALESCE(NEW.description, '')), 'B') ||
        -- Catégorie : faible
        setweight(to_tsvector('french', COALESCE(NEW.categorie, '')), 'C');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsvector_update  
BEFORE INSERT OR UPDATE ON produits  
FOR EACH ROW EXECUTE FUNCTION produits_search_trigger();  

-- Index GIN partiel (uniquement produits actifs et en stock)
CREATE INDEX idx_produits_available_search  
ON produits  
USING GIN(search_vector)  
WHERE actif = true AND stock > 0  
WITH (fastupdate = on);  

-- Index complémentaires
CREATE INDEX idx_produits_prix ON produits(prix);  
CREATE INDEX idx_produits_categorie ON produits(categorie);  

-- Recherche optimisée
SELECT
    nom,
    marque,
    prix,
    stock,
    ts_rank(
        ARRAY[0.05, 0.2, 0.4, 2.0],  -- Boost nom/marque
        search_vector,
        query,
        1  -- Normalisation
    ) AS score
FROM
    produits,
    websearch_to_tsquery('french', 'chaussure running nike') query
WHERE
    actif = true
    AND stock > 0  -- Conditions de l'index partiel
    AND search_vector @@ query
    AND prix BETWEEN 50 AND 200  -- Peut utiliser idx_produits_prix
ORDER BY score DESC  
LIMIT 50;

Cas 3 : Documentation Technique Multilingue

-- Structure
CREATE TABLE documentation (
    id SERIAL PRIMARY KEY,
    titre VARCHAR(200),
    contenu TEXT,
    langue VARCHAR(10),  -- 'fr', 'en', 'es'
    version VARCHAR(20),
    categorie VARCHAR(50),
    search_vector tsvector
);

-- Trigger intelligent multilingue
CREATE OR REPLACE FUNCTION doc_search_trigger() RETURNS trigger AS $$  
DECLARE  
    config regconfig;
BEGIN
    -- Choisir la configuration selon la langue
    config := CASE NEW.langue
        WHEN 'fr' THEN 'french'::regconfig
        WHEN 'en' THEN 'english'::regconfig
        WHEN 'es' THEN 'spanish'::regconfig
        ELSE 'simple'::regconfig
    END;

    NEW.search_vector :=
        setweight(to_tsvector(config, COALESCE(NEW.titre, '')), 'A') ||
        setweight(to_tsvector(config, COALESCE(NEW.contenu, '')), 'B');

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsvector_update  
BEFORE INSERT OR UPDATE ON documentation  
FOR EACH ROW EXECUTE FUNCTION doc_search_trigger();  

-- Index GIN par langue (plus performant que index global)
CREATE INDEX idx_doc_fr_search  
ON documentation  
USING GIN(search_vector)  
WHERE langue = 'fr';  

CREATE INDEX idx_doc_en_search  
ON documentation  
USING GIN(search_vector)  
WHERE langue = 'en';  

CREATE INDEX idx_doc_es_search  
ON documentation  
USING GIN(search_vector)  
WHERE langue = 'es';  

-- Recherche par langue
SELECT
    titre,
    version,
    ts_rank(search_vector, query) AS score
FROM
    documentation,
    plainto_tsquery('french', 'connexion base données') query
WHERE
    langue = 'fr'  -- Utilise idx_doc_fr_search
    AND search_vector @@ query
ORDER BY score DESC;

9. Diagnostic et Troubleshooting

Vérifier si l'Index est Utilisé

-- EXPLAIN montre si l'index est utilisé
EXPLAIN (ANALYZE, BUFFERS)  
SELECT titre FROM articles  
WHERE search_vector @@ plainto_tsquery('french', 'postgresql');

Bon signe : "Bitmap Index Scan" ou "Index Scan"

Bitmap Index Scan on idx_articles_search  (cost=...)
  Index Cond: (search_vector @@ ...)

Mauvais signe : "Seq Scan"

Seq Scan on articles  (cost=...)
  Filter: (search_vector @@ ...)

Pourquoi l'Index n'est Pas Utilisé ?

Raison 1 : Statistiques Obsolètes

-- Mettre à jour les statistiques
ANALYZE articles;

-- Vérifier la date de dernière analyse
SELECT
    schemaname,
    tablename,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables  
WHERE tablename = 'articles';

Raison 2 : Pas Assez de Données

Pour de petites tables (< 1000 lignes), un scan séquentiel peut être plus rapide qu'un index.

-- Forcer l'utilisation de l'index (debug uniquement)
SET enable_seqscan = off;

-- Tester la requête
EXPLAIN SELECT * FROM articles WHERE search_vector @@ query;

-- Remettre à normal
RESET enable_seqscan;

Raison 3 : Requête Retourne Trop de Lignes

Si la requête retourne > 10-20% de la table, PostgreSQL préfère un scan séquentiel.

-- Vérifier la sélectivité
SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE search_vector @@ plainto_tsquery('french', 'le')) AS matches,
    ROUND(100.0 * COUNT(*) FILTER (WHERE search_vector @@ plainto_tsquery('french', 'le')) / COUNT(*), 2) AS pourcentage
FROM articles;

-- Si pourcentage > 20%, un scan séquentiel est normal

Raison 4 : Requête ne Correspond Pas à l'Index

-- ❌ N'utilise PAS l'index partiel (condition manquante)
CREATE INDEX idx_partial ON articles USING GIN(search_vector) WHERE statut = 'publié';

SELECT * FROM articles WHERE search_vector @@ query;
-- Manque : AND statut = 'publié'

-- ✅ Utilise l'index partiel
SELECT * FROM articles  
WHERE search_vector @@ query AND statut = 'publié';

Statistiques d'Utilisation des Index

-- Voir combien de fois chaque index est utilisé
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan AS scans,
    idx_tup_read AS tuples_lus,
    idx_tup_fetch AS tuples_recuperes,
    pg_size_pretty(pg_relation_size(indexrelid)) AS taille
FROM pg_stat_user_indexes  
WHERE schemaname = 'public'  
ORDER BY idx_scan DESC;

Interprétation :

  • idx_scan = 0 : Index jamais utilisé (candidat à la suppression)
  • idx_scan > 0 : Index utilisé (pertinent)
  • Ratio tuples_lus/idx_scan : Nombre moyen de résultats par recherche

10. Bonnes Pratiques

✅ Toujours Créer l'Index sur Colonne tsvector Dédiée

-- ✅ BON : Colonne dédiée
ALTER TABLE articles ADD COLUMN search_vector tsvector;  
UPDATE articles SET search_vector = to_tsvector('french', contenu);  
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);  

-- ❌ ÉVITER : Index sur expression
CREATE INDEX idx_bad ON articles USING GIN(to_tsvector('french', contenu));
-- Plus lent à l'insertion/mise à jour

✅ Utiliser CONCURRENTLY en Production

-- ✅ BON : Pas de blocage
CREATE INDEX CONCURRENTLY idx_articles_search ON articles USING GIN(search_vector);

-- ❌ RISQUÉ : Bloque la table (peut durer plusieurs minutes)
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

✅ Activer fastupdate pour Tables avec Écritures Fréquentes

-- Tables avec insertions/mises à jour fréquentes
CREATE INDEX idx_articles_search  
ON articles  
USING GIN(search_vector)  
WITH (fastupdate = on, gin_pending_list_limit = 16384);

✅ Utiliser des Index Partiels Quand Possible

-- Si 90% des recherches concernent les articles publiés
CREATE INDEX idx_published_search  
ON articles  
USING GIN(search_vector)  
WHERE statut = 'publié';  
-- Index 10× plus petit !

✅ ANALYZE Après Création d'Index

CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);  
ANALYZE articles;  -- ← IMPORTANT : Met à jour les statistiques

✅ Surveiller et Maintenir Régulièrement

-- Script de maintenance hebdomadaire
VACUUM ANALYZE articles;

-- Reindex annuel (ou après opérations massives)
REINDEX INDEX CONCURRENTLY idx_articles_search;

✅ Tester les Performances Avant et Après

-- Avant création de l'index
\timing on
SELECT COUNT(*) FROM articles WHERE search_vector @@ query;
-- Temps : 2450 ms

-- Créer l'index
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);  
ANALYZE articles;  

-- Après création de l'index
SELECT COUNT(*) FROM articles WHERE search_vector @@ query;
-- Temps : 15 ms
-- Gain : 163× plus rapide

11. Comparaison : GIN vs GiST vs Autres

GIN vs GiST

Critère GIN GiST
Performance recherche ⭐⭐⭐⭐⭐ Excellent ⭐⭐⭐⭐ Très bon
Performance écriture ⭐⭐⭐ Moyen ⭐⭐⭐⭐ Bon
Taille index Plus grand Plus petit
Full-Text Search ✅ Recommandé ⚠️ Possible
Données spatiales ❌ Non ✅ Oui
Arrays ✅ Oui ⚠️ Limité
JSONB ✅ Oui ⚠️ Limité

Verdict Full-Text Search : GIN est le choix optimal

Quand NE PAS Utiliser GIN

  • ❌ Très petites tables (< 1000 lignes) : Overhead pas justifié
  • ❌ Tables avec écritures ultra-fréquentes et peu de lectures : BRIN peut être mieux
  • ❌ Colonnes avec très faible cardinalité : B-Tree suffit

12. Résumé et Checklist

Checklist de Déploiement

  • Créer une colonne tsvector dédiée
  • Ajouter un trigger pour maintenir la colonne automatiquement
  • Créer l'index GIN (avec CONCURRENTLY en production)
  • Exécuter ANALYZE après création
  • Vérifier avec EXPLAIN que l'index est utilisé
  • Mesurer les performances avant/après
  • Planifier la maintenance (VACUUM hebdomadaire)
  • Surveiller la taille et l'utilisation de l'index

Commandes Essentielles

-- Création complète
ALTER TABLE ma_table ADD COLUMN search_vector tsvector;  
CREATE INDEX CONCURRENTLY idx_search ON ma_table USING GIN(search_vector);  
ANALYZE ma_table;  

-- Maintenance
VACUUM ANALYZE ma_table;  
REINDEX INDEX CONCURRENTLY idx_search;  

-- Surveillance
SELECT pg_size_pretty(pg_relation_size('idx_search'));  
SELECT idx_scan FROM pg_stat_user_indexes WHERE indexrelname = 'idx_search';  

-- Debug
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM ma_table WHERE search_vector @@ query;

Paramètres Recommandés

-- Pour la création d'index
SET maintenance_work_mem = '256MB';  -- Ou plus selon RAM

-- Pour les écritures fréquentes
CREATE INDEX ... WITH (fastupdate = on, gin_pending_list_limit = 16384);

-- Index partiel si applicable
CREATE INDEX ... WHERE condition_frequente;

Conclusion

Les index GIN sont absolument essentiels pour obtenir de bonnes performances avec le Full-Text Search dans PostgreSQL. Ils transforment des requêtes de plusieurs secondes en quelques millisecondes.

Points clés à retenir :

  1. Index GIN = Obligatoire pour Full-Text Search à grande échelle

    • Gain de performance : 100× à 10 000×
    • Structure d'index inversé optimale pour la recherche textuelle

  2. Colonne tsvector dédiée recommandée

    • Plus performant que les index sur expressions
    • Maintenance automatique avec triggers

  3. Options d'optimisation :

    • fastupdate = on pour écritures fréquentes
    • gin_pending_list_limit adapté à votre charge
    • Index partiels pour réduire la taille

  4. Maintenance régulière :

    • VACUUM pour nettoyer la pending list
    • REINDEX en cas de bloat important
    • ANALYZE pour mettre à jour les statistiques

  5. Surveillance :

    • Vérifier l'utilisation avec pg_stat_user_indexes
    • Mesurer la taille avec pg_relation_size
    • Valider les plans avec EXPLAIN ANALYZE

Sans index GIN, le Full-Text Search PostgreSQL est inutilisable en production. Avec un index GIN bien configuré et maintenu, vous obtenez un moteur de recherche performant et scalable, directement intégré à votre base de données.

Prochaine étape : Explorez les techniques avancées de recherche, les highlights, et l'intégration avec des interfaces utilisateur pour créer des expériences de recherche complètes !

Ressources complémentaires :

⏭️ Foreign Data Wrappers (FDW) : PostgreSQL comme hub de données