PostgreSQL : Optimisation des Requêtes et Compréhension des Opérations d'Index

Optimisation des Index PostgreSQL : Pourquoi vos Index sont Ignorés et Comment y Remédier

Créer un index ne garantit pas toujours que PostgreSQL l'utilisera. Les développeurs rencontrent souvent des scénarios où, malgré la présence d'un index, les requêtes s'exécutent lentement, recourant à une analyse complète de la table (Seq Scan). Comprendre les mécanismes du planificateur de requêtes de PostgreSQL, interpréter les métriques de EXPLAIN ANALYZE et connaître les facteurs influençant les choix de stratégie d'exécution sont cruciaux pour l'optimisation des performances des bases de données. Dans cet article, nous allons approfondir ces aspects en détail, en utilisant des exemples concrets avec de grands ensembles de données.

Préparation : 4 Millions de Lignes pour les Expériences

Pour démontrer le fonctionnement des index, nous allons créer une table de test nommée t_test sans clé primaire ni aucun index, contenant 4 millions d'enregistrements. Cela illustrera clairement la différence de performance avant et après l'indexation.

DROP TABLE IF EXISTS t_test;
CREATE TABLE t_test (id serial, name text);
INSERT INTO t_test (name) SELECT 'hans' FROM generate_series(1, 2000000);
INSERT INTO t_test (name) SELECT 'paul' FROM generate_series(1, 2000000);
SELECT name, count(*) FROM t_test GROUP BY name;

Lors de l'exécution d'une requête sans index, par exemple, EXPLAIN ANALYZE SELECT * FROM t_test WHERE id = 432332;, nous observerons un Seq Scan qui parcourt les 4 millions de lignes, prenant environ 126 millisecondes. C'est un exemple classique du problème que les index sont conçus pour résoudre.

Comprendre EXPLAIN et la Métrique cost

EXPLAIN ANALYZE est l'outil principal pour analyser les plans d'exécution des requêtes dans PostgreSQL. Il fournit des informations détaillées sur la manière dont le planificateur a l'intention d'exécuter une requête, y compris les opérateurs choisis (par exemple, Seq Scan, Index Scan), le temps d'exécution et, surtout, la métrique cost.

Examinons la sortie cost=0.00..71622.00 de l'exemple précédent. Ce nombre n'est pas un temps réel en millisecondes, mais un coefficient relatif que PostgreSQL utilise pour comparer différents plans d'exécution. Considérez-le comme des « perroquets » – des unités de coût abstraites. Pour une expérience plus propre, il est utile de désactiver le parallélisme :

SET max_parallel_workers_per_gather TO 0;

Le coût est dérivé de plusieurs composants, tels que le nombre de blocs disque et le coût de traitement de chaque ligne :

SELECT pg_relation_size('t_test') / 8192.0; -- ~21622 blocs de 8 Ko
SHOW cpu_tuple_cost;    -- 0.01 (coût de traitement d'une ligne)
SHOW cpu_operator_cost; -- 0.0025 (coût d'un opérateur/fonction)

La formule de coût approximative pour notre Seq Scan ressemblerait à ceci :

SELECT (pg_relation_size('t_test') / 8192.0) * 1
       + count(id) * 0.01
       + count(id) * 0.0025
FROM t_test;

Le résultat sera proche de 71622. Il est crucial de comprendre que cost ne tient pas compte des spécificités matérielles du système, donc comparer le cost de deux requêtes différentes pour estimer le temps d'exécution réel est inexact. Cependant, au sein d'une seule requête, il est utile pour identifier les parties les plus « coûteuses » du plan.

Utilisation Basique des Index : BTree et ses Avantages

Le type d'index le plus courant dans PostgreSQL est le BTree. Il offre une recherche, un tri et une concurrence élevés et efficaces. Créons un index BTree sur la colonne id :

CREATE INDEX idx_id ON t_test (id);
EXPLAIN SELECT * FROM t_test WHERE id = 43242;

Après la création de l'index, le cost de la requête diminue considérablement (de 71 622 à 8,45), et le temps de récupération tombe à des fractions de milliseconde. Les index BTree sont également efficaces pour les opérations de tri et la recherche de valeurs minimales/maximales :

• Tri : PostgreSQL peut utiliser un index pour effectuer un ORDER BY en parcourant simplement l'index dans la direction souhaitée (Index Scan Backward pour DESC) et en s'arrêtant une fois que le LIMIT est atteint.

```sql

EXPLAIN SELECT * FROM t_test ORDER BY id DESC LIMIT 10;

```

• Min/Max : Pour déterminer min(id) ou max(id), le planificateur utilise un Index Only Scan, lisant la première ou la dernière entrée de l'index.

```sql

EXPLAIN SELECT min(id), max(id) FROM t_test;

```

Gérer les Conditions Multiples : Scan Bitmap

PostgreSQL peut gérer efficacement les requêtes avec plusieurs conditions OR sur un seul index en utilisant un Scan Bitmap.

EXPLAIN SELECT * FROM t_test WHERE id = 30 OR id = 50;

Dans ce scénario, PostgreSQL effectue un Bitmap Index Scan pour chaque condition, puis combine les résultats dans un bitmap en utilisant BitmapOr, et seulement ensuite accède à la table principale (Bitmap Heap Scan) pour récupérer les lignes complètes. Cela évite plusieurs analyses de table et optimise l'accès aux données.

Pourquoi le Planificateur Ignore un Index : Sélectivité et Statistiques

L'une des raisons les plus courantes pour lesquelles un index n'est pas utilisé est la faible sélectivité de la condition de la requête. La sélectivité est la proportion de lignes dans une table qui correspondent à une condition donnée. Si une condition affecte une grande partie de la table, le planificateur pourrait décider qu'un Seq Scan sera plus efficace qu'un Index Scan.

Considérons un exemple. Nous allons créer un index sur le champ name :

CREATE INDEX idx_name ON t_test (name);

Si nous recherchons un nom inexistant (EXPLAIN SELECT * FROM t_test WHERE name = 'hans2';), l'index sera utilisé, et rows sera 1, car PostgreSQL s'attend toujours à au moins une ligne. Cependant, si la requête couvre une grande partie de la table, par exemple, 'hans' OR 'paul' (qui constitue 100 % de notre table de test) :

EXPLAIN SELECT * FROM t_test WHERE name = 'hans' OR name = 'paul';

Dans ce cas, PostgreSQL effectuera un Seq Scan. La raison est simple : parcourir l'index entier puis accéder à la table pour chacune des 4 millions de lignes est plus coûteux que de simplement lire la table entière séquentiellement. Le planificateur prend sa décision en se basant sur les statistiques de distribution des données. Si ces statistiques sont obsolètes (par exemple, après un grand volume de modifications), la décision pourrait être sous-optimale.

Impact de la Disposition Physique des Données : Corrélation et CLUSTER

L'efficacité d'un index dépend fortement de l'agencement physique des données sur le disque. Si les données accédées par l'index sont très dispersées, cela peut ralentir considérablement la récupération. Créons une copie de notre table, mais avec un ordre de lignes aléatoire :

CREATE TABLE t_random AS SELECT * FROM t_test ORDER BY random();
CREATE INDEX idx_random ON t_random (id);
VACUUM ANALYZE t_random;

Comparons les performances d'une requête récupérant les 10 000 premiers enregistrements pour les tables originale (ordonnée) et aléatoire :

• Table originale (données ordonnées) :

```sql

EXPLAIN (analyze true, buffers true) SELECT * FROM t_test WHERE id < 10000;

```

Ici, nous observerons un faible nombre d'accès aux tampons (par exemple, Buffers: shared hit=3 read=82), indiquant une lecture séquentielle des données.

• Table aléatoire (données dispersées) :

```sql

EXPLAIN (analyze true, buffers true) SELECT * FROM t_random WHERE id < 10000;

```

Dans ce cas, le nombre d'accès aux tampons sera significativement plus élevé (par exemple, Buffers: shared hit=801 read=7210). Cela se produit parce que les données sont dispersées, forçant PostgreSQL à effectuer de nombreuses lectures disque aléatoires, ce qui augmente considérablement le temps d'exécution. Le planificateur pourrait même passer à un Bitmap Heap Scan.

Corrélation

PostgreSQL suit le degré d'ordonnancement des données à l'aide de la métrique de corrélation, disponible dans pg_stats :

SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename IN ('t_test', 't_random') AND attname = 'id'
ORDER BY 1, 2;

• correlation ~ 1 : Les données sont physiquement ordonnées, permettant une lecture séquentielle des blocs disque.
• correlation ~ 0 : Les données sont dispersées de manière aléatoire, entraînant de nombreux accès disque individuels pour chaque ligne.

CLUSTER

La commande CLUSTER vous permet de trier physiquement les données d'une table selon un index spécifié :

CLUSTER t_random USING idx_random;
VACUUM ANALYZE t_random;

Après CLUSTER, la récupération redeviendra rapide. Cependant, CLUSTER présente des inconvénients importants :

• Verrouillage de la Table : L'opération verrouille toute la table, y compris les instructions SELECT, pendant sa durée.
• Limitation : Elle ne fonctionne qu'avec un seul index.
• Pas de Maintenance Automatique : L'ordre des données n'est pas automatiquement maintenu ; après de nouvelles insertions ou mises à jour, les données peuvent redevenir désordonnées.

Optimisation via Index Only Scan et INCLUDE

Lorsqu'une requête n'accède qu'aux colonnes entièrement contenues dans un index, PostgreSQL peut effectuer un Index Only Scan. Cela évite d'accéder à la table principale (heap), accélérant considérablement l'exécution de la requête.

EXPLAIN SELECT id FROM t_test WHERE id = 34234;

Ici, id est déjà dans l'index idx_id, donc un Index Only Scan est possible. Cependant, si vous interrogez toutes les colonnes, y compris name, qui n'est pas dans idx_id :

EXPLAIN SELECT * FROM t_test WHERE id = 34234;

PostgreSQL effectuera un Index Scan régulier, car il devra accéder à la table pour la colonne name. Pour activer un Index Only Scan même pour SELECT *, vous pouvez utiliser un index de couverture avec la clause INCLUDE :

CREATE INDEX idx_random_cover ON t_random (id) INCLUDE (name);
EXPLAIN SELECT * FROM t_random WHERE id = 34234;

Maintenant, name est inclus dans l'index, et un Index Only Scan est à nouveau effectué, minimisant les accès disque.

Techniques d'Indexation Avancées : Index Fonctionnels et Partiels

Au-delà des index BTree standard, PostgreSQL offre des solutions plus spécialisées :

• Index Fonctionnels : Ceux-ci vous permettent d'indexer les résultats de fonctions. La seule exigence est que la fonction doit être déterministe (retournant toujours le même résultat pour la même entrée).

```sql

CREATE INDEX idx_cos ON t_random (cos(id));

EXPLAIN SELECT * FROM t_random WHERE cos(id) = 10;

```

Un cas d'utilisation typique est l'indexation de lower(email) pour les recherches insensibles à la casse.

• Index Partiels : Ces index ne couvrent qu'un sous-ensemble de lignes de table qui satisfont une condition WHERE spécifique.

```sql

CREATE INDEX idx_name ON t_test (name) WHERE name NOT IN ('hans', 'paul');

```

Un tel index sera significativement plus petit et mis à jour moins fréquemment, ce qui est bénéfique lorsque la majorité des données est rarement impliquée dans les requêtes de recherche.

Autres Types d'Index : GiST et pg_trgm

PostgreSQL prend en charge divers types d'index, chacun optimisé pour des tâches spécifiques. Outre BTree, il existe GIN, GiST, SP-GiST, BRIN et Bloom. Par exemple, les index GiST sont souvent utilisés pour les données géospatiales, la recherche plein texte et d'autres types de données complexes.

L'extension pg_trgm permet la correspondance de chaînes floue en décomposant les chaînes en trigrammes et en calculant la distance entre elles. Ceci est utile pour les recherches tolérantes aux fautes de frappe ou les correspondances de chaînes partielles.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT 'abcde' <-> 'abdeacb'; -- un nombre entre 0 et 1
SELECT show_trgm('abcdef');

Points Clés à Retenir

• EXPLAIN ANALYZE est votre meilleur ami : Utilisez-le pour comprendre les plans de requête et identifier les goulots d'étranglement. Le cost est une métrique relative, utile pour comparer des parties d'un même plan.
• La sélectivité détermine le choix : Les index sont efficaces pour les requêtes très sélectives (peu de lignes). Avec une faible sélectivité (beaucoup de lignes), PostgreSQL pourrait préférer un Seq Scan.
• La disposition physique des données compte : Une corrélation élevée entre l'ordre logique et physique des données améliore les performances de l'Index Scan. CLUSTER peut aider mais présente des inconvénients importants.
• Index Only Scan et INCLUDE : Utilisez ces mécanismes pour minimiser l'accès à la table principale lorsque toutes les colonnes nécessaires sont déjà présentes dans l'index.
• Index Avancés : Les index fonctionnels et partiels vous permettent de créer des structures plus spécialisées et efficaces pour des scénarios spécifiques.

— Editorial Team