Indexer de façon réfléchie

Ce que vous saurez faire

Accélérez chaque requête lente avec un index adapté, et mesurez son coût. N’ajoutez pas un index parce que quelqu’un pourrait chercher dans une colonne un jour.

Avant de commencerCe qu’il faut savoir et avoir sous la mainEnviron 55 minutes

Ce que vous devez savoir

• Lire un plan : Seq Scan (parcours séquentiel), Index Scan (parcours d’index), lignes estimées et réelles, dans Lire un plan de requête.
• CREATE INDEX CONCURRENTLY et la ligne -- migrate: no-transaction, dans Migrations.
• Les clés étrangères et l’effet d’une suppression sur les lignes enfants, dans Contraintes d’intégrité.

Ce qu’il vous faut sur votre ordinateur

• Le projet après la dernière leçon, avec scripts/explain.py et bench-before.json. python -m pytest affiche 79 passed (79 tests réussis).
• PostgreSQL en cours d’exécution, avec le grand jeu de données chargé.

Cette leçon ajoute une migration, migrations/008_ticket_list_indexes.sql.

Un annuaire téléphonique

Un annuaire imprimé est trié par nom de famille, puis par prénom. Pour trouver « Garcia, Hugo », vous l’ouvrez près du G, puis cherchez Hugo parmi les Garcia. Cela prend quelques secondes. Maintenant, trouvez toutes les personnes qui s’appellent Hugo. L’annuaire ne peut pas vous aider : les Hugo sont répartis sous toutes les lettres. Vous devez donc lire tout l’annuaire.

L’ordre du tri détermine quelles questions trouvent une réponse rapide. Un deuxième annuaire, trié par prénom, permettrait de trouver les Hugo rapidement. Mais il coûte du papier. Quand une personne déménage, il faut modifier les deux annuaires.

Le problème : trois parcours complets et une supposition

La dernière leçon a trouvé les parties lentes de la liste de billets de soutien. Trois requêtes lisaient des tables entières pour trouver quelques lignes. La liste elle-même prenait 11,0 ms. La dernière exécution d’IA d’un billet prenait 8,3 ms, 20 fois par page. Ses messages prenaient 9,4 ms. Tomás veut toujours un index sur chaque colonne. « Les index accélèrent les lectures. Plus d’index, plus de vitesse. »

Mei a deux questions. Quel index accélère chaque requête? Et combien coûte chaque index? Elle répond aux deux avec des mesures.

L’idée : un index est une copie triée

Un index est une copie distincte et triée d’une ou plusieurs colonnes d’une table. Chaque entrée pointe vers sa ligne. PostgreSQL utilise habituellement un arbre B, un arbre de pages qui trouve n’importe quelle valeur en quelques étapes. Comme les entrées sont triées, la base de données peut accéder directement à la première correspondance, puis lire dans l’ordre. C’est le principe de l’annuaire.

Un index sur une colonne est un index à une colonne. Un index sur plusieurs colonnes est un index composite. Il est trié par la première colonne, puis par la deuxième pour chaque valeur de la première, et ainsi de suite. Comme l’annuaire, il aide donc une requête qui fixe les valeurs de ses premières colonnes. Un index sur (status, team) trouve « open, billing » (ouvert, facturation) immédiatement. Il ne peut pas trouver rapidement « tous les billets de facturation », car ces billets se trouvent sous chaque statut.

Voici un petit exemple détaillé : les 8 billets ouverts de facturation du petit jeu de données, et les 3 plus récents (LIMIT 3). Comparez deux index :

Les mêmes 8 billets ouverts de facturation dans deux index. Les deux trouvent le groupe immédiatement. Seul le deuxième le présente aussi dans l'ordre nécessaire à la liste.

Description textuelle du diagramme

L’index de gauche porte sur (status, team). Les 8 entrées open, billing sont regroupées. Dans le groupe, elles gardent l’ordre de la table : T-30002 en premier et T-30197 en dernier. Pour renvoyer les 3 plus récentes, la base de données lit les 8, les trie par heure et en garde 3. L’index de droite porte sur (status, team, created_at DESC, ticket_id DESC). Les mêmes 8 entrées sont regroupées, les plus récentes en premier. T-30197 (8 octobre, 14 h 47), T-30179 (6 octobre, 12 h 43) et T-30177 (6 octobre, 12 h 24) précèdent les 5 autres. La base de données lit les 3 premières entrées et s’arrête. Il n’y a rien à trier.

Avec 8 billets, la différence est de 8 lectures contre 3. Avec 76 160 billets fermés d’expédition dans le grand jeu de données, c’est 76 160 lignes récupérées et triées contre 20 entrées lues. Voici donc la règle pour un index composite. D’abord viennent les colonnes que la requête compare avec =. Ensuite viennent les colonnes de son ORDER BY, dans le même ordre et le même sens. Pour la liste de billets, c’est (status, team, created_at DESC, ticket_id DESC).

Les index candidats de Larkfield, mesurés

Mei ne se fie pas seulement à la règle. Elle mesure six index candidats pour la liste de billets du grand jeu de données, un à la fois, avec deux pages. « Ouverts, facturation » compte 1 659 billets correspondants; « fermés, expédition » en compte 76 160. L’explorateur montre quatre des candidats, à côté du cas sans index. Faites une prédiction, puis essayez-les :

Explorer

Cinq façons d'indexer la liste de billets

Temps

11,0 ms

Pages lues (8 ko chacune)

17 275

Lignes examinées

300 000

Taille de l’index

aucun index

Aucun index : trois processus lisent chaque ligne de tickets, environ 100 000 chacun. Ils gardent les 1 659 billets de facturation ouverts et les trient, puis le Limit renvoie les 20 plus récents.

Billets de facturation ouverts (status=open&team=billing), aucun index : les étapes dans l’ordre où elles s’exécutent

#	Étape	Lignes prévues	Lignes trouvées
1	Seq Scan Lit les 300 000 lignes de tickets en 3 processus et garde les 1 659 qui correspondent au filtre.	1 603	1 659
2	Sort Chaque processus trie ses propres lignes par created_at DESC, ticket_id DESC. Il doit voir toutes les lignes avant que la première puisse sortir.	–	–
3	Gather Merge Fusionne les lignes triées des 3 processus en une seule liste ordonnée.	–	–
4	Limit Renvoie les 20 premières lignes et s’arrête.	–	–

Les étapes et les lignes viennent de EXPLAIN (ANALYZE, BUFFERS) sur les grandes données (300 000 billets). Le temps est la médiane de 5 exécutions sur l’ordinateur du cours, sans autre charge; vos temps seront différents. « Lignes prévues » est l’estimation du planificateur avant l’exécution de la requête; elle varie un peu d’une exécution à l’autre.

La requête de liste de billets (SELECT * … LIMIT 20) sur 300 000 billets, avec un index à la fois. Étapes et lignes tirées de EXPLAIN (ANALYZE, BUFFERS); les temps sont les médianes de 5 exécutions sur l'ordinateur du cours.

Description textuelle de l’explorateur

L’explorateur a deux commandes. La première choisit l’index sur les billets : aucun index, (status), (status, team), (created_at) ou (status, team, created_at, ticket_id). La deuxième choisit la page : billets ouverts de facturation ou billets fermés d’expédition. Un tableau montre les étapes dans leur ordre d’exécution, avec les lignes attendues et trouvées. Un affichage montre le temps, les pages lues, les lignes examinées et la taille de l’index.

Ouverts, facturation. Aucun index : 11,0 ms, un parcours complet de 300 000 lignes. (status) : 1,39 ms; il trouve 7 216 billets ouverts, en élimine 5 557 et en trie 1 659. (status, team) : 0,515 ms; il en trouve 1 659 et les trie. (created_at) : 0,028 ms; il parcourt les plus récents d’abord, examine 126 billets et s’arrête à 21. L’index composite : 0,013 ms; il lit 20 entrées.

Fermés, expédition. Aucun index : 16,1 ms. (status) : 15,5 ms; le planificateur ignore l’index et lit toute la table. (status, team) : 10,4 ms; il trouve 76 160 billets, puis les récupère et les trie tous. (created_at) : 0,275 ms; il examine 2 080 billets. L’index composite : 0,012 ms; il lit 20 entrées.

Tailles des index : (status) 2,0 Mo, (status, team) 2,1 Mo, (created_at) 6,4 Mo, l’index composite 17,1 Mo.

Ce que disent les mesures :

• (status) aide la page des billets ouverts : 1,39 ms. Pour la page des billets fermés, le planificateur ne l’utilise pas du tout. 85 % des billets sont fermés. Accéder à 254 364 lignes une par une coûte plus cher que lire la table dans l’ordre.
• (status, team) trouve exactement le bon groupe. Mais le groupe n’est pas trié par date. La base de données récupère donc les 76 160 billets fermés d’expédition et les trie pour en garder 20. Cette page passe de 16,1 à seulement 10,4 ms.
• (created_at) est une surprise : 0,028 et 0,275 ms. Il parcourt les billets les plus récents d’abord. Comme les billets ouverts sont récents, il en trouve 20 rapidement. Cette vitesse dépend des données. Pour une page dont les billets sont anciens ou rares, le parcours devient long.
• L’index composite lit 20 entrées pour n’importe quelle page : 0,013 et 0,012 ms, pour 1 659 ou 76 160 correspondances. C’est aussi le plus gros : 17,1 Mo.

Le cours a aussi mesuré les bonnes colonnes dans le mauvais ordre, (created_at DESC, status, team) : 0,026 et 0,042 ms, 11,8 Mo. Il se comporte comme (created_at) seul. La première colonne fixe l’ordre. La base de données parcourt donc toujours les billets par heure et vérifie chacun. Le graphique compare la taille de chaque candidat à son temps sur la page la plus difficile :

• Aucun index
• Index composite
• Autres candidats

Taille de chaque index candidat comparée au temps de la page des billets fermés d'expédition (76 160 billets correspondants), sur le grand jeu de données. Médianes de 5 exécutions avec cache chaud; tailles des index tirées de pg_relation_size.

Description textuelle du graphique

Un nuage de points. Chaque point représente un index candidat. L’axe horizontal représente la taille de l’index en Mo, et l’axe vertical, le temps de la page des billets fermés d’expédition en ms. Aucun index : 0 Mo, 16,111 ms. (status) : 2,01 Mo, 15,515 ms. (status, team) : 2,05 Mo, 10,441 ms. (created_at) : 6,44 Mo, 0,275 ms. L’index composite (status, team, created_at, ticket_id) : 17,06 Mo, 0,012 ms.

Les petits index, près de 2 Mo, font gagner peu de temps sur cette page. Le temps tombe presque à zéro seulement avec les index qui donnent les lignes dans l’ordre des dates. L’index composite est le plus gros et le plus rapide. Il ne dépend pas d’une répartition favorable des données. Mei le garde.

Migration 008 : un index par requête mesurée

Les deux autres requêtes lentes ont besoin d’index plus simples, selon la même règle. « La dernière exécution d’IA d’un billet » compare ticket_id et trie par created_at DESC. « Les messages d’un billet » compare ticket_id. La migration 008 contient donc trois index :

migrations/008_ticket_list_indexes.sql (from line 7)

-- CONCURRENTLY builds an index without blocking writes; it cannot run in a transaction.
CREATE INDEX CONCURRENTLY IF NOT EXISTS tickets_queue_idx
    ON tickets (status, team, created_at DESC, ticket_id DESC);
CREATE INDEX CONCURRENTLY IF NOT EXISTS ai_runs_ticket_idx
    ON ai_runs (ticket_id, created_at DESC);
CREATE INDEX CONCURRENTLY IF NOT EXISTS messages_ticket_idx
    ON messages (ticket_id);

La première ligne du fichier est -- migrate: no-transaction, et les lignes 2 à 6 expliquent chaque index. CONCURRENTLY construit un index pendant que l’API continue d’écrire, comme l’explique Migrations. Sur le grand jeu de données, l’essai a pris 0,97 s pour les trois, sans faire attendre aucune lecture.

Après 008, le cours a mesuré de nouveau les trois requêtes (médianes de 5 exécutions) :

Requête	Avant 008	Après 008	Plan après
La page des billets ouverts de facturation (requête du module 3)	11,0 ms	0,013 ms	Index Scan using tickets_queue_idx (parcours avec l’index tickets_queue_idx), 17 pages
La dernière exécution d’IA d’un billet	8,3 ms	0,004 ms	Index Scan using ai_runs_ticket_idx (parcours avec l’index ai_runs_ticket_idx), 4 pages
Les messages d’un billet	9,4 ms	0,006 ms	Index Scan using messages_ticket_idx (parcours avec l’index messages_ticket_idx), puis un tri de 3 lignes

Le point de terminaison de la liste de billets conserve les 41 requêtes du module 3. Sous la même charge de travail, sa médiane passe de 375 ms à 13,3 ms. La prochaine leçon va plus loin.

Les index accélèrent aussi les suppressions

Rappelez-vous les règles de suppression de Contraintes d’intégrité. Quand un billet est supprimé, PostgreSQL doit vérifier chaque table qui pointe vers lui : y a-t-il des messages, des exécutions d’IA, des fichiers? Chaque vérification est une requête sur la table enfant, par ticket_id. Une clé étrangère ne crée pas d’index sur la colonne enfant. Sans index, chaque vérification fait donc un parcours complet.

Le cours a supprimé un billet avant et après 008, dans une transaction qu’il a annulée :

Vérification de clé étrangère	Avant 008	Après 008
messages_ticket_fk	21,48 ms	0,02 ms
ai_runs_ticket_fk	18,67 ms	0,1 ms
attachments_ticket_fk	3,91 ms	2,08 ms
Le DELETE complet	46,52 ms	2,23 ms

Mei a créé les index de 008 pour les lectures. Ils ont aussi rendu la suppression 20 fois plus rapide. attachments n’a toujours pas d’index sur ticket_id, et sa vérification fait toujours un parcours complet. Pour un billet, cela prend quelques millisecondes. Dans Restauration et conservation, l’effacement du client d’affaires C-3835 supprime 4 322 billets à la fois. Il prend 7,7 s sans cet index, et 0,39 s avec lui. Cette leçon l’ajoute donc. Indexez la colonne de référence d’une clé étrangère quand vous supprimez des parents ou cherchez des enfants par leur parent.

Ce que coûte un index

Chaque index est une autre copie triée. Il occupe donc de l’espace disque, et chaque écriture doit le mettre à jour. Le cours a mesuré le coût des écritures sur une copie de tickets : 10 000 nouveaux billets dans une transaction, médianes de 3 exécutions.

Insertion de 10 000 billets dans une copie de tickets (300 000 lignes) avec la clé primaire seulement, puis avec l'index de la file en plus, puis avec un index supplémentaire sur chacune des 11 colonnes. Médianes de 3 exécutions.

Description textuelle du graphique

Un diagramme à colonnes des secondes nécessaires pour insérer 10 000 billets. Clé primaire seulement : 0,026 s, avec 9,3 Mo d’index. Avec l’index de la file en plus : 0,047 s, 29,5 Mo. Avec 11 index supplémentaires, un sur chaque colonne : 0,113 s, 92 Mo d’index pour une table de 134,2 Mo.

Un index bien choisi a presque doublé le temps d’insertion (de 0,026 à 0,047 s) et ajouté 20 Mo. Pour le service de soutien, c’est un coût raisonnable : les billets sont lus beaucoup plus souvent qu’ils sont écrits. Onze index supplémentaires ont rendu les insertions 4,3 fois plus lentes qu’avec les clés seules. Les index ont atteint 92 Mo. C’est environ les deux tiers des 134 Mo de la table elle-même.

Erreur courante

Un index sur chaque colonne

Symptôme : Tomás ajoute un index sur chacune des 11 colonnes de tickets, en plus de l’index de la file. Les insertions prennent 4,3 fois plus de temps qu’avec les clés seules, et les index occupent 92 Mo. La liste de billets n’est pas plus rapide qu’avec l’index de la file seul.

Cause : un index aide seulement une requête dont les conditions et l’ordre lui conviennent. Personne n’a mesuré à quelles requêtes servaient les 11 index. Un index par colonne ne peut pas non plus remplacer un index composite. Les mesures l’ont montré : (status) et (team) seuls aident à peine une page qui filtre sur les deux et trie par heure.

Diagnostic : PostgreSQL compte combien de fois le planificateur utilise chaque index. Exécutez la charge de travail, puis interrogez ces compteurs :

SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes WHERE relname = 'tickets' ORDER BY idx_scan;

Le cours a exécuté la charge de travail de la liste de billets et la liste de clients avec les 13 index de Tomás. Ensuite, seulement 2 des 13 avaient un idx_scan supérieur à zéro : l’index de la file et celui sur created_at. Les 11 autres occupaient de l’espace et ralentissaient les écritures pour rien.

Correction : supprimez les index qu’aucune requête mesurée n’utilise, avec DROP INDEX CONCURRENTLY <name>; dans une migration sans transaction. Ensuite, ajoutez un index seulement pour une requête lente que vous avez mesurée. Vérifiez son plan, puis mesurez de nouveau la requête et les écritures. Consultez idx_scan de temps en temps : une requête peut changer et laisser son index inutilisé.

Exercice guidé : ajouter les index et mesurer

1. Mesurez une suppression avant les index. Dans psql (docker compose exec db psql -U tickets -d tickets), supprimez le billet T-399001 dans une transaction, puis annulez-la :

   BEGIN;
   EXPLAIN (ANALYZE, COSTS OFF) DELETE FROM tickets WHERE ticket_id = 'T-399001';
   ROLLBACK;

   Notez les lignes Trigger for constraint (déclencheur de contrainte) et Execution Time (temps d’exécution). Quittez avec \q.

2. Copiez la migration 008 depuis l’instantané end-of-m04 :

   macOS / Linux

   S=../nextia-learning-labs/C20/snapshots/end-of-m04
   cp "$S/migrations/008_ticket_list_indexes.sql" migrations/

   Windows (PowerShell)

   $S = "..\nextia-learning-labs\C20\snapshots\end-of-m04"
   Copy-Item "$S\migrations\008_ticket_list_indexes.sql" migrations\

3. Appliquez-la : python -m ticket_api.migrate.

4. Analysez de nouveau les trois requêtes : python -m scripts.explain queue, puis latest-run --ticket T-399000, puis messages --ticket T-399000. Chaque plan commence maintenant par un Index Scan.

5. Analysez la page difficile : python -m scripts.explain queue --status closed --team shipping.

6. Répétez la suppression de l’étape 1 et comparez les temps des déclencheurs.

7. Dans psql, consultez les tailles des nouveaux index : SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_stat_user_indexes WHERE indexrelname LIKE '%_idx';.

8. Mesurez le point de terminaison avec la même charge de travail qu’avant : python -m scripts.bench. Ensuite, exécutez les tests : python -m pytest.

Afficher le résultat attendu

Étape 1, la fin de l’exécution de l’auteur (vos temps seront différents) :

 Trigger for constraint messages_ticket_fk: time=20.313 calls=1
 Trigger for constraint attachments_ticket_fk: time=8.107 calls=1
 Trigger for constraint ai_runs_ticket_fk: time=25.373 calls=1
 Trigger for constraint ticket_embeddings_ticket_fk: time=0.196 calls=1
 Execution Time: 54.197 ms

L’étape 3 affiche applied 008_ticket_list_indexes (migration 008 appliquée) et Database is up to date (8 migrations). (base de données à jour, 8 migrations). Cela prend une ou deux secondes sur le grand jeu de données. Étape 4, le premier plan :

Limit  (cost=0.42..57.24 rows=20 width=201) (actual time=0.025..0.038 rows=20.00 loops=1)
  Buffers: shared hit=18
  ->  Index Scan using tickets_queue_idx on tickets t  (cost=0.42..4736.50 rows=1667 width=201) (actual time=0.025..0.035 rows=20.00 loops=1)
        Index Cond: ((status = 'open'::text) AND (team = 'billing'::text))
        Index Searches: 1
        Buffers: shared hit=18

Aucun Sort (tri) et aucun Filter (filtre) : l’index donne les 20 lignes dans l’ordre. L’étape 5 montre les deux mêmes étapes, avec Buffers: shared hit=24 (24 accès au cache partagé) dans l’exécution de l’auteur. Étape 6 :

 Trigger for constraint messages_ticket_fk: time=0.124 calls=1
 Trigger for constraint attachments_ticket_fk: time=3.653 calls=1
 Trigger for constraint ai_runs_ticket_fk: time=0.282 calls=1
 Trigger for constraint ticket_embeddings_ticket_fk: time=0.053 calls=1
 Execution Time: 4.212 ms

Étape 7 : tickets_queue_idx 17 Mo, ai_runs_ticket_idx 17 Mo, messages_ticket_idx 14 Mo. Étape 8 : toujours database queries per request: 41 (41 requêtes à la base de données par requête HTTP), avec une médiane d’environ dix à vingt millisecondes; le cours a mesuré 13,3 ms. Les tests affichent 79 passed : les index ne changent aucun résultat, seulement la vitesse.

À vous de jouer : la liste des billets du client

L’équipe de Grace ouvre les billets d’un même client plusieurs fois par jour : GET /v1/tickets?customer_id=C-3835. Elle utilise la même requête de liste, avec customer_id = … plutôt que le statut et l’équipe. python -m scripts.explain customer --customer C-3835 montre qu’elle lit encore toute la table : un Parallel Seq Scan (parcours séquentiel parallèle). Le cours a mesuré environ 12 ms pour une requête semblable.

Concevez un index pour cette requête en suivant la règle de cette leçon. Testez-le sans migration : dans psql, créez-le entre BEGIN; et ROLLBACK;. Exécutez ensuite EXPLAIN (ANALYZE, BUFFERS) sur la requête dans la même transaction. Puis répondez : quel est son coût, et la requête sert-elle assez souvent pour le justifier?

Afficher un exemple de réponse

La requête compare customer_id et trie selon created_at DESC, ticket_id DESC. L’index est donc (customer_id, created_at DESC, ticket_id DESC) :

BEGIN;
CREATE INDEX tickets_customer_idx ON tickets (customer_id, created_at DESC, ticket_id DESC);
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tickets t WHERE t.customer_id = 'C-3835' ORDER BY t.created_at DESC, t.ticket_id DESC LIMIT 20;
SELECT pg_size_pretty(pg_relation_size('tickets_customer_idx'));
ROLLBACK;

Les lignes du plan obtenu par l’auteur :

 Limit  (cost=0.42..72.70 rows=20 width=201) (actual time=0.036..0.053 rows=20.00 loops=1)
   Buffers: shared hit=18 read=3
   ->  Index Scan using tickets_customer_idx on tickets t  (cost=0.42..15467.18 rows=4280 width=201) (actual time=0.035..0.050 rows=20.00 loops=1)
         Index Cond: (customer_id = 'C-3835'::text)
         Index Searches: 1

21 pages plutôt que toute la table, et aucun tri. L’index occupe 14 Mo, et chaque nouveau billet doit le mettre à jour. Le service de soutien ouvre les pages des clients plusieurs fois par jour. Cet index en vaut donc la peine. Dans un vrai projet, vous le conserveriez dans une migration avec CONCURRENTLY, testée au préalable sur le grand jeu de données. Sans CONCURRENTLY, CREATE INDEX bloque les écritures dans tickets pendant la création de l’index. Entre BEGIN … ROLLBACK sur votre ordinateur, cela n’a pas d’importance. Dans ce cours, ne le conservez pas : les prochains modules continuent sans lui.

Quand ajouter un index, et quand s’en passer

Ajoutez un index quand une requête mesurée est lente, lit bien plus de lignes qu’elle en retourne et s’exécute souvent. Construisez-le pour cette requête : les colonnes = d’abord, puis les colonnes ORDER BY. Ensuite, vérifiez le plan et mesurez de nouveau les lectures et les écritures.

N’ajoutez pas d’index :

• pour une petite table. Sur les 200 billets du petit jeu de données, le planificateur préfère un parcours complet, et il a raison.
• pour une condition qui correspond à la plupart des lignes. Comme l’a montré status = 'closed', le planificateur l’ignore.
• « au cas où ». Chaque index ralentit chaque insertion et de nombreuses mises à jour, et utilise du disque et de la mémoire. Un index inutilisé ne fait qu’ajouter des coûts.

Les index sur la colonne qui porte une clé étrangère sont une exception à prévoir. La suppression des parents et la recherche des enfants en ont toutes deux besoin.

De bout en boutConcevoir et optimiser le stockage d'un jeu de données public ouvertÉtude de cas

L’étude de cas Boston 311 teste dix index candidats, un à la fois. Un index partiel de 6 Mo rend la file de travail environ 900 fois plus rapide.

Vérification des connaissances

Vérifiez votre compréhension

La liste des billets filtre selon status et team et trie selon created_at DESC, ticket_id DESC. Pourquoi (status, team, created_at DESC, ticket_id DESC) vaut-il mieux que deux index, un sur (status, team) et l'autre sur (created_at)?

Afficher la réponse

Aucun des deux index ne fait les deux tâches, et PostgreSQL ne peut pas les fusionner en une seule liste ordonnée. (status, team) trouve le groupe, mais pas dans l’ordre des dates. Il faut donc récupérer et trier 76 160 billets fermés de l’équipe d’expédition. (created_at) fournit l’ordre, mais la base de données vérifie ensuite le statut et l’équipe de chaque billet. L’index composite rassemble le groupe et place les plus récents en premier. Les 20 premières entrées donnent donc la réponse : 0,012 ms pour n’importe quelle page.

Vérifiez votre compréhension

Avec un index sur (status), la page des billets ouverts de facturation prenait 1,39 ms. Celle des billets fermés d'expédition prenait encore 15,5 ms, et son plan n'utilisait aucun index. Pourquoi le planificateur a-t-il ignoré l'index?

Afficher la réponse

254 364 des 300 000 billets sont fermés. Pour utiliser l’index, la base de données passerait d’une entrée à sa ligne pour chacun, dans un ordre de pages aléatoire. Lire toute la table dans l’ordre coûte moins cher. Un index est avantageux quand il réduit beaucoup le nombre de lignes à lire. Une valeur présente dans la plupart des lignes ne réduit rien.

Vérifiez votre compréhension

La migration 008 visait la liste des billets, mais supprimer un billet est devenu 20 fois plus rapide. Expliquez pourquoi et nommez la table dont la vérification reste lente.

Afficher la réponse

Supprimer un billet fait vérifier par PostgreSQL chaque table ayant une clé étrangère vers tickets, à partir de ticket_id. Sans index sur cette colonne, chaque vérification lit toute la table enfant. La migration 008 a indexé messages (ticket_id) et ai_runs (ticket_id, created_at DESC). Ces deux vérifications sont devenues des recherches par index, passant de 21,5 et 18,7 ms à 0,02 et 0,1 ms. Mais attachments n’a pas encore d’index sur ticket_id, donc sa vérification parcourt toujours la table.

Récapitulatif

• Un index est une copie triée de certaines colonnes qui pointe vers les lignes. Un index composite se trie par sa première colonne, puis la suivante. Il aide les requêtes qui fixent ses premières colonnes.
• Pour une requête, placez les colonnes = d’abord, puis les colonnes ORDER BY, dans le même ordre et le même sens. Pour la liste des billets : (status, team, created_at DESC, ticket_id DESC), 0,013 ms pour n’importe quelle page.
• Mesurez chaque candidat. Le planificateur ignorait (status) pour la page des billets fermés; (status, team) triait encore 76 160 lignes. (created_at) était rapide seulement parce que les billets ouverts sont récents.
• La migration 008 ajoute trois index. Le point de terminaison de la liste, toujours avec 41 requêtes, est passé de 375 ms à 13,3 ms. La suppression d’un billet est passée de 46,5 à 2,2 ms.
• Chaque index coûte de l’espace et du temps d’écriture. Un index sur chaque colonne : insertions 4,3 fois plus lentes, 92 Mo et 2 index utilisés sur 13. pg_stat_user_indexes montre lesquels servent.
• Ensuite : envoyer une seule requête plutôt que 41, et retourner une page qui reste rapide, quelle que soit sa profondeur.

Leçon suivante : Limiter la taille des résultats