Utilisation de pg_stat_statements pour analyser l'utilisation des index et des filtres

Les index, c'est un peu comme des marque-pages dans un bouquin. Ça aide à retrouver vite fait les infos qu'on cherche. Mais imagine si t'as mis plein de marque-pages et que personne s'en sert ? Ou pire, si t'as des marque-pages mal placés qui t'obligent à feuilleter tout le livre du début à la fin ? C'est là qu'il faut analyser comment on utilise les index.

Des requêtes mal fichues peuvent zapper les index, ce qui donne des scans séquentiels (Seq Scan) bien lourds. Résultat : tes requêtes rament et ton serveur souffre. Notre but, c'est de piger quelles requêtes n'utilisent pas les index et pourquoi.

Comment savoir si les index sont utilisés ?

Regardons deux questions clés :

1. Est-ce que les index qu'on a créés sont vraiment utilisés ?
2. Et s'ils le sont, est-ce qu'ils sont efficaces ?

Pour ça, on peut mater les stats des requêtes dans pg_stat_statements et checker quelques colonnes :

• rows : combien de lignes la requête a traitées.
• shared_blks_hit : combien de pages ont été lues depuis la RAM (et pas depuis le disque).
• shared_blks_read : combien de pages ont vraiment été lues sur le disque.

Moins la requête traite de lignes, et plus la part de shared_blks_hit est grande par rapport au total de pages, mieux ton index fait le taf.

Exemple d'analyse d'indexation

Imaginons qu'on a une table d'étudiants :

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    grade_level INTEGER
);

-- On ajoute un index sur grade_level
CREATE INDEX idx_grade_level ON students(grade_level);

Maintenant, on insère des données pour tester :

INSERT INTO students (first_name, last_name, birth_date, grade_level)
SELECT 
    'Étudiant ' || generate_series(1, 100000),
    'NomDeFamille',
    '2000-01-01'::DATE + (random() * 3650)::INT,
    floor(random() * 12)::INT
FROM generate_series(1, 100000);

On lance une requête pour trouver les étudiants d'un certain niveau :

SELECT *
FROM students
WHERE grade_level = 10;

Vérification dans pg_stat_statements

Après avoir exécuté la requête plusieurs fois, on peut checker les stats :

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level = 10%';

Interprétation du résultat :

• rows : Si la requête renvoie trop de lignes, est-ce que l'index sert à quelque chose ? Peut-être qu'il ne sert à rien pour des conditions pas assez sélectives.
• shared_blks_hit et shared_blks_read : Si beaucoup de pages sont lues depuis le disque (shared_blks_read), soit l'index ne sert pas, soit les données sont pas en cache.

Optimisation de l'indexation

Créer un index, c'est que la moitié du boulot. Faut que PostgreSQL l'utilise vraiment. Parfois, malgré tous tes efforts, la base préfère faire un scan séquentiel. Pourquoi ? On va voir ça.

D'abord, voyons pourquoi un index peut être zappé même s'il semble utile. Ensuite, quelques astuces pour forcer la base à se rappeler qu'on a un index et à s'en servir.

Et si l'index n'est pas utilisé ?

Parfois, PostgreSQL ignore l'index et fait un scan séquentiel (Seq Scan). Ça peut venir de plusieurs raisons :

1. Faible sélectivité de la condition. Si la requête renvoie plus de la moitié des lignes de la table, le scan séquentiel peut être plus rapide.
2. Type de données ou fonctions. Si tu utilises une fonction sur la colonne indexée dans ta requête, l'index peut être ignoré. Par exemple :

   SELECT *
   FROM students
   WHERE grade_level + 1 = 11; -- L'index n'est pas utilisé

Dans ces cas-là, tu peux réécrire la requête :

   SELECT * 
   FROM students
   WHERE grade_level = 10; -- Utilise l'index

3. Type d'index pas adapté. Par exemple, pour la recherche full-text, il vaut mieux utiliser des index GIN ou GiST plutôt que B-TREE.

4. Stats pas à jour. Si les stats sont vieilles, l'optimiseur peut faire de mauvais choix. Utilise ANALYZE :

   ANALYZE students;
   

Améliorer la requête

Revenons à notre exemple. Si l'index ne sert à rien, tu peux essayer ça :

1. Vérifie que ta requête utilise des filtres qui peuvent profiter de l'index : évite les fonctions, les conversions de type, etc.
2. Si le filtre renvoie trop de valeurs, demande-toi si l'index est utile. Si c'est une requête fréquente, pense à changer la structure de la table ou à ajouter des vues matérialisées.
3. Si tu as un Seq Scan à cause d'un gros volume de données, essaie de partitionner la table (PARTITION BY).

Vérifier l'efficacité de l'indexation

Après optimisation, relance la requête et vérifie les stats :

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level%';

Compare les métriques avant/après. Tu devrais voir moins de lectures disque (shared_blks_read) et plus de hits (shared_blks_hit).

Cas réels

1. Mauvaise utilisation de l'index

On a une table de produits avec un champ texte description :

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT
);

-- Index pour la recherche full-text
CREATE INDEX idx_description ON products USING GIN (to_tsvector('english', description));

Si on fait la requête suivante :

SELECT *
FROM products
WHERE description ILIKE '%smartphone%';

L'index ne sera pas utilisé ! La raison, c'est que ILIKE n'est pas compatible avec GIN. Pour utiliser l'index, il faut réécrire la requête :

SELECT *
FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('smartphone');

2. Manque d'index là où il en faut un

Imaginons la requête :

SELECT *
FROM students
WHERE birth_date BETWEEN '2001-01-01' AND '2003-01-01';

fait un scan séquentiel (Seq Scan). C'est sûrement parce qu'il n'y a pas d'index sur birth_date. En créant l'index :

CREATE INDEX idx_birth_date ON students(birth_date);

et en recalculant les stats (ANALYZE students), tu pourras accélérer cette requête de ouf.