Lire un plan de requête

Ce que vous saurez faire

Découvrez pourquoi une requête est lente avant de changer quoi que ce soit. Demandez à PostgreSQL comment il exécute la requête. Lisez sa réponse étape par étape et vérifiez si ses estimations étaient justes.

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

Ce que vous devez savoir

• WHERE (où), ORDER BY (trier par), LIMIT (limiter) et les jointures, vus dans Sélectionner et filtrer et Joindre des tables du cours SQL.
• Le point de terminaison de la liste des billets de soutien et ses 41 requêtes par page, vus dans Requêtes sûres.
• scripts.bench, la médiane et le p95, vus dans Gestion des connexions.

Ce qu’il vous faut sur votre ordinateur

• Le projet à la fin du module 3 : python -m pytest affiche 79 passed (79 tests réussis). Si votre projet ne fonctionne plus, copiez l’instantané end-of-m03, comme l’indique son fichier README.md.
• PostgreSQL en cours d’exécution, avec les données volumineuses chargées. En cas de doute, exécutez python -m scripts.load --size large --reset. Cela prend quelques secondes sur un ordinateur peu occupé, et jusqu’à une minute sur un ordinateur occupé. Ajoutez environ 15 secondes s’il faut recréer les données.

Cette leçon ajoute un fichier, scripts/explain.py. Tous les temps de ce module viennent d’un même ordinateur sans autre activité : un Apple M4 avec 16 Go et PostgreSQL 18.6 dans Docker. Vos temps seront différents; les résultats garderont la même forme. Testé sur macOS (puce Apple). Les commandes Windows ne sont pas testées.

Deux façons de trouver une recette

Vous cherchez la recette du gâteau au citron dans un gros livre de recettes. Vous pouvez tourner chaque page, de la première à la dernière, et regarder chaque titre. Ou vous pouvez ouvrir l’index à la fin, trouver « gâteau au citron, page 212 » et aller directement à cette page.

Les deux méthodes trouvent la recette : la première lit 400 pages, la seconde en lit deux. Mais pour « toutes les recettes avec des œufs », lire page par page peut être plus rapide. La plupart des recettes contiennent des œufs. Une base de données fait le même choix pour chaque requête. Elle peut vous dire quelle méthode elle a choisie.

Le problème : une page lente, sans savoir pourquoi

À la fin du module 3, la liste des billets fonctionne, mais elle est lente. Sur les données volumineuses (300 000 billets), scripts.bench a mesuré une médiane de 375 ms par page, avec 41 requêtes par page. Cela représentait 5 passages de 200 requêtes, 4 à la fois. Amira veut accélérer la page. Tomás a déjà une réponse : « Ajoutez un index sur chaque colonne. »

Mei l’arrête. « Quelle requête est lente? Qu’est-ce que PostgreSQL fait avec? » Personne ne le sait. Un index sur chaque colonne, c’est une supposition. Indexer de façon réfléchie en montre le coût. D’abord, regardez.

L’idée : le plan de requête

PostgreSQL n’exécute pas directement votre texte SQL. D’abord, une partie appelée le planificateur envisage plusieurs façons d’obtenir les lignes. Il peut lire toute la table, utiliser un index ou joindre les tables dans un autre ordre. Il estime le coût de chaque méthode, puis choisit la moins coûteuse. La méthode choisie est le plan de requête : une liste d’étapes où chaque étape transmet ses lignes à la suivante.

EXPLAIN (expliquer), placé avant une requête, affiche le plan sans exécuter la requête. Vous voyez les étapes et les estimations du planificateur. EXPLAIN ANALYZE (expliquer et analyser) exécute réellement la requête et affiche de nouveau le plan, avec ce qui s’est passé : les lignes trouvées à chaque étape et le temps écoulé. Ajoutez BUFFERS (tampons) pour compter aussi les pages de 8 ko lues. Dans le livre de recettes, le plan dit « page par page » ou « par l’index ». ANALYZE (analyser) compte les pages que vous avez réellement tournées.

Voici le plus petit exemple utile : la requête de la liste des billets sur les petites données, soit 200 billets. Le nouveau script du projet, scripts/explain.py, sera copié dans la pratique guidée ci-dessous. Il place EXPLAIN (ANALYZE, BUFFERS) devant la vraie requête du dépôt, avec ses paramètres.

python -m scripts.explain queue

Limit  (cost=14.12..14.14 rows=8 width=199) (actual time=0.114..0.120 rows=8.00 loops=1)
  Buffers: shared hit=17
  ->  Sort  (cost=14.12..14.14 rows=8 width=199) (actual time=0.113..0.114 rows=8.00 loops=1)
        Sort Key: created_at DESC, ticket_id DESC
        Sort Method: quicksort  Memory: 27kB
        Buffers: shared hit=17
        ->  Seq Scan on tickets t  (cost=0.00..14.00 rows=8 width=199) (actual time=0.024..0.058 rows=8.00 loops=1)
              Filter: ((status = 'open'::text) AND (team = 'billing'::text))
              Rows Removed by Filter: 192
              Buffers: shared hit=11
Planning:
  Buffers: shared hit=148 dirtied=3
Planning Time: 0.607 ms
Execution Time: 0.161 ms

Le plan est un arbre. Chaque -> représente une étape, qui transmet ses lignes à l’étape au-dessus. La ligne la plus en retrait s’exécute donc en premier. Lisez un plan de bas en haut :

Le plan de la liste des billets sur les petites données, dans l'ordre d'exécution des étapes. EXPLAIN affiche les trois mêmes étapes à l'envers : la ligne la plus en retrait s'exécute en premier.

Description textuelle du diagramme

La table tickets contient 200 lignes. L’étape 1, un Seq Scan sur tickets, lit les 200 lignes. Son filtre, status = ‘open’ AND team = ‘billing’, garde 8 lignes et en retire 192. Le planificateur prévoyait 8 lignes et en a trouvé 8. L’étape 2, un Sort, trie les 8 lignes selon created_at DESC, puis ticket_id DESC. Elle doit voir toutes les lignes avant de pouvoir transmettre la première. L’étape 3, un Limit, renvoie les premières lignes, au plus 20; ici, les 8. Les 8 lignes vont à l’API.

Chaque ligne d’étape contient deux groupes entre parenthèses :

• (cost=… rows=8 width=199) est le plan, créé avant l’exécution de la requête. rows (lignes) est l’estimation du nombre de lignes que l’étape renvoie. cost (coût) utilise les unités propres au planificateur, pas des millisecondes. Utilisez-le seulement pour comparer les étapes d’un même plan.
• (actual time=0.024..0.058 rows=8.00 loops=1) montre ce qui s’est réellement passé. rows indique les lignes trouvées. time (temps) est en millisecondes : la première ligne est arrivée après 0,024 ms, la dernière après 0,058 ms. loops (boucles) indique combien de fois l’étape s’est exécutée.

Les lignes en retrait sous une étape donnent ses détails. Filter (filtre) est la condition vérifiée sur chaque ligne. Rows Removed by Filter (lignes retirées par le filtre) compte les lignes qui ne la respectaient pas. Buffers: shared hit=11 (tampons : 11 pages partagées trouvées en mémoire) indique que le parcours a lu 11 pages, toutes dans la mémoire de PostgreSQL (« hit »). Une page provenant du disque apparaît sous read (lecture).

En mots simples : lire les 200 billets, garder les 8 billets ouverts de facturation, les trier et les renvoyer. Pour 200 lignes, c’est le bon plan : lire une petite table entière coûte moins cher qu’utiliser un index.

La page lente de Larkfield, étape par étape

Voici maintenant la même requête sur les données volumineuses. Ce plan vient de l’ordinateur du cours :

Limit  (cost=20075.88..20078.21 rows=20 width=201) (actual time=10.036..10.833 rows=20.00 loops=1)
  Buffers: shared hit=2577 read=14698
  ->  Gather Merge  (cost=20075.88..20263.39 rows=1610 width=201) (actual time=10.036..10.831 rows=20.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=2577 read=14698
        ->  Sort  (cost=19075.86..19077.53 rows=671 width=201) (actual time=9.154..9.155 rows=14.33 loops=3)
              Sort Key: created_at DESC, ticket_id DESC
              Sort Method: top-N heapsort  Memory: 35kB
              Buffers: shared hit=2577 read=14698
              Worker 0:  Sort Method: top-N heapsort  Memory: 35kB
              Worker 1:  Sort Method: top-N heapsort  Memory: 35kB
              ->  Parallel Seq Scan on tickets t  (cost=0.00..19058.00 rows=671 width=201) (actual time=0.283..8.997 rows=553.00 loops=3)
                    Filter: ((status = 'open'::text) AND (team = 'billing'::text))
                    Rows Removed by Filter: 99447
                    Buffers: shared hit=2485 read=14698
Planning Time: 0.030 ms
Execution Time: 10.841 ms

Lisez-le de bas en haut, comme avant :

1. Parallel Seq Scan (parcours séquentiel parallèle) : un parcours séquentiel lit la table page par page, du début à la fin. « Parallèle » signifie que trois processus partagent le travail : deux Workers (processus de travail) et le processus principal, donc loops=3. En moyenne, chaque processus a gardé 553 lignes et en a retiré 99 447. Ensemble, ils ont lu 300 000 lignes pour garder 553 × 3 = 1 659 billets ouverts de facturation.
2. Sort (tri) : chaque processus trie ses propres lignes. top-N heapsort (tri par tas des N premiers éléments) garde seulement les 20 meilleures en mémoire pendant la lecture. Cela coûte peu.
3. Gather Merge (regroupement avec fusion) : cette étape fusionne les trois listes triées en une seule, toujours dans l’ordre.
4. Limit (limite) : cette étape prend les 20 premières lignes et s’arrête.

Le plan a lu 17 275 pages (hit plus read) : environ 135 Mo, soit toute la table, pour renvoyer 20 lignes. La médiane de 5 exécutions était de 11,0 ms. Le planificateur prévoyait 1 610 lignes (à l’étape Gather Merge) et en a trouvé 1 659 : une bonne estimation. Votre plan peut varier un peu : le Sort peut plutôt se trouver au-dessus d’un Gather (regroupement). Le parcours et ses lignes restent les mêmes.

Est-ce que ces 11 ms sont le problème? La page envoie 41 requêtes, alors regardez aussi les deux autres types. Pour chaque billet de la page, le code demande la dernière exécution de l’IA. Voici le plan de cette requête pour le billet T-399000, lors de la même exécution :

Limit  (cost=13822.57..13822.58 rows=1 width=23) (actual time=7.403..8.122 rows=1.00 loops=1)
  Buffers: shared hit=1974 read=8506 written=5
  ->  Sort  (cost=13822.57..13822.58 rows=2 width=23) (actual time=7.402..8.122 rows=1.00 loops=1)
        Sort Key: created_at DESC
        Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=1974 read=8506 written=5
        ->  Gather  (cost=1000.00..13822.56 rows=2 width=23) (actual time=7.336..8.119 rows=1.00 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              Buffers: shared hit=1974 read=8506 written=5
              ->  Parallel Seq Scan on ai_runs  (cost=0.00..12822.36 rows=1 width=23) (actual time=6.643..6.650 rows=0.33 loops=3)
                    Filter: (ticket_id = 'T-399000'::text)
                    Rows Removed by Filter: 149911
                    Buffers: shared hit=1974 read=8506 written=5
Planning Time: 0.025 ms
Execution Time: 8.128 ms

Encore un parcours complet : les 449 734 exécutions de l’IA, pour trouver la seule exécution d’un seul billet. Cela a pris 8,3 ms (médiane de 5 exécutions), et la page le fait 20 fois. Le troisième type de requête, le nom du client, est différent :

Index Scan using customers_pkey on customers  (cost=0.29..8.30 rows=1 width=12) (actual time=0.002..0.002 rows=1.00 loops=1)
  Index Cond: (customer_id = 'C-3835'::text)
  Index Searches: 1
  Buffers: shared hit=3

Un Index Scan (parcours d’index) correspond à l’index à la fin du livre de recettes. La clé primaire de customers est déjà un index. PostgreSQL y a donc cherché C-3835, lu 3 pages et accédé directement à la ligne. Index Cond (condition d’index) est la condition à laquelle l’index a répondu. Cela a pris 0,004 ms (médiane de 5 exécutions).

Additionnez maintenant les temps pour une page. La liste prend 11,0 ms, les dernières exécutions 20 × 8,3 ms, et les noms 20 × 0,004 ms. Cela représente environ 177 ms de travail dans la base de données. Les 20 parcours de ai_runs en prennent 166 ms. Tomás aurait commencé par la requête de la liste. Les plans montrent que le plus gros coût se cache dans une requête qui semble minuscule.

Exploration : une page, avec et sans le bon index

Un aperçu de la prochaine leçon : aucun index, puis l’index que la prochaine leçon conserve. Utilisez-le pour vous exercer à lire les étapes.

Explorer

Le plan d'une page de la liste des billets, sur 300 000 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 la liste des billets du module 3 (SELECT * … LIMIT 20) sur les données volumineuses. Les étapes et les lignes viennent 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’outil d’exploration offre deux commandes. La première choisit l’index sur tickets : aucun index, ou l’index (status, team, created_at, ticket_id). La seconde 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 prévues et celles trouvées. Un affichage indique le temps, les pages lues, les lignes examinées et la taille de l’index.

Sans index, billets ouverts de facturation : un Seq Scan dans 3 processus lit 300 000 lignes et en garde 1 659 (1 603 prévues). Chaque processus trie ses lignes, un Gather Merge les fusionne, et le Limit en renvoie 20. 11,0 ms, 17 275 pages.

Sans index, billets fermés d’expédition : les mêmes étapes; le parcours garde 76 160 lignes (76 190 prévues). 16,1 ms, 17 275 pages.

Avec l’index, billets ouverts de facturation : un Index Scan lit les 20 premières entrées de l’index, déjà triées. Le Limit les renvoie. 20 lignes examinées, 0,013 ms, 17 pages. L’index fait 17,1 Mo.

Avec l’index, billets fermés d’expédition : les mêmes étapes. 20 lignes examinées, 0,012 ms, 24 pages.

Sans index, la base de données examine les 300 000 lignes pour chacune des deux pages. Avec le bon index, elle en examine 20. Comparez aussi les deux colonnes de lignes : avec l’index, le parcours a trouvé 20 lignes, mais le planificateur en prévoyait 1 711. Ce n’est pas une mauvaise estimation. Elle porte sur toute la plage de l’index. Le parcours s’est arrêté tôt parce que le Limit avait assez de lignes.

Les jointures dans un plan

La liste du module 3 obtient les noms des clients avec 20 requêtes distinctes. Une jointure les obtiendrait en une seule requête. Un plan montre une jointure comme une étape avec deux entrées. Voici deux jointures dans psql, tirées de l’exécution de l’auteur. Vos temps seront différents; les lignes Buffers et les dernières lignes sont omises :

EXPLAIN ANALYZE
SELECT t.ticket_id, c.name, t.subject
FROM tickets t JOIN customers c ON c.customer_id = t.customer_id
WHERE t.status = 'open' AND t.team = 'billing';

 Gather  (cost=1696.00..21562.52 rows=1667 width=46) (actual time=2.815..17.836 rows=1659.00 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Hash Join  (cost=696.00..20395.82 rows=695 width=46) (actual time=3.099..15.056 rows=553.00 loops=3)
         Hash Cond: (t.customer_id = c.customer_id)
         ->  Parallel Seq Scan on tickets t  (cost=0.00..19698.00 rows=695 width=41) (actual time=0.317..11.973 rows=553.00 loops=3)
               Filter: ((status = 'open'::text) AND (team = 'billing'::text))
               Rows Removed by Filter: 99447
         ->  Hash  (cost=446.00..446.00 rows=20000 width=19) (actual time=2.671..2.671 rows=20000.00 loops=3)
               Buckets: 32768  Batches: 1  Memory Usage: 1270kB
               ->  Seq Scan on customers c  (cost=0.00..446.00 rows=20000 width=19) (actual time=0.006..0.948 rows=20000.00 loops=3)

Une Hash Join (jointure par hachage) a deux entrées. D’abord, l’étape Hash (hachage) lit les 20 000 clients dans une table de recherche en mémoire, avec customer_id comme clé. Puis chacun des 1 659 billets cherche son client dans cette table. Une jointure par hachage convient quand de nombreuses lignes rencontrent de nombreuses lignes.

Demandez maintenant un seul billet :

EXPLAIN ANALYZE
SELECT t.ticket_id, c.name, t.subject
FROM tickets t JOIN customers c ON c.customer_id = t.customer_id
WHERE t.ticket_id = 'T-399000';

 Nested Loop  (cost=0.71..16.75 rows=1 width=46) (actual time=0.016..0.016 rows=1.00 loops=1)
   ->  Index Scan using tickets_pkey on tickets t  (cost=0.42..8.44 rows=1 width=41) (actual time=0.011..0.011 rows=1.00 loops=1)
         Index Cond: (ticket_id = 'T-399000'::text)
   ->  Index Scan using customers_pkey on customers c  (cost=0.29..8.30 rows=1 width=19) (actual time=0.004..0.004 rows=1.00 loops=1)
         Index Cond: (customer_id = t.customer_id)

Une Nested Loop (boucle imbriquée) prend chaque ligne de sa première entrée et cherche les lignes correspondantes dans la seconde. Elle convient quand la première entrée est petite et que la seconde possède un index. Elle convient mal quand la première entrée contient des milliers de lignes et que la seconde n’a pas d’index. Chaque ligne coûte alors un parcours complet. Limiter la taille des résultats présente ce cas. Un troisième type, la Merge Join (jointure par fusion), parcourt deux entrées déjà triées sur la clé de jointure.

Le planificateur a choisi deux jointures différentes pour les deux mêmes tables. Il prévoyait 1 667 lignes dans un cas et 1 dans l’autre. Alors, ses estimations sont-elles justes?

Lignes estimées et lignes réelles

Le planificateur ne compte pas les lignes avant de créer le plan; cela coûterait autant que la requête. Il utilise des statistiques : un résumé de chaque colonne que ANALYZE crée à partir d’un échantillon de lignes. Les statistiques contiennent les valeurs les plus fréquentes et leur proportion de lignes, ainsi qu’un portrait approximatif des autres valeurs. Le planificateur s’en sert pour estimer les lignes de chaque étape.

Le cours a mesuré quatre estimations sur les données volumineuses. Les processus parallèles étaient désactivés, donc chaque nombre porte sur toute la table :

Requête sur tickets	Lignes prévues	Lignes trouvées	Pourquoi
customer_id = 'C-3835'	4 640	4 322	Une valeur fréquente : elle figure dans l’échantillon, avec sa proportion. L’estimation est proche.
customer_id = 'C-0042'	17	5	Une valeur rare : le planificateur utilise une moyenne pour les valeurs qu’il n’a pas vues.
status = 'open' AND created_at < '2026-01-01'	1 691	1 009	Le planificateur multiplie les deux proportions, comme si les conditions étaient indépendantes. Les billets ouverts sont surtout récents, donc elles ne le sont pas.
status IN ('resolved', 'closed') AND closed_at IS NULL	11 465	0	Une règle CHECK (vérification) rend cela impossible, mais le planificateur ne lit pas les règles CHECK.

Les nombres changent un peu entre les exécutions, car ANALYZE prend un nouvel échantillon chaque fois. Un écart d’un facteur de 2 ou 3 compte rarement. Un écart d’un facteur de 10 ou plus peut faire choisir la mauvaise méthode au planificateur. Par exemple, une boucle imbriquée sur des milliers de lignes. Comparez donc les deux valeurs rows= de chaque étape, de bas en haut, et multipliez-les par loops.

Pratique guidée : mesurer d’abord, puis lire les plans

1. Dans ~/projects/ticket-api, avec l’environnement virtuel actif, copiez le script de cette leçon depuis l’instantané end-of-m04 :

   macOS / Linux

   S=../nextia-learning-labs/C20/snapshots/end-of-m04
   cp "$S/scripts/explain.py" scripts/

   Windows (PowerShell)

   $S = "..\nextia-learning-labs\C20\snapshots\end-of-m04"
   Copy-Item "$S\scripts\explain.py" scripts\

   Ouvrez-le. Il construit la requête avec le list_query du dépôt. Il explique donc la requête que l’API envoie réellement.

2. Notez le point de départ de ce module. Fermez les autres programmes occupés. Puis exécutez le banc d’essai sur les données volumineuses et enregistrez chaque nombre dans un fichier :

   python -m scripts.bench --json bench-before.json

   Cela prend environ une demi-minute. Gardez bench-before.json : la dernière leçon de ce module l’utilise pour la comparaison.

3. Expliquez la liste des billets : python -m scripts.explain queue. Trouvez le parcours, les lignes retirées et les pages lues.

4. Expliquez la dernière exécution de l’IA pour un billet : python -m scripts.explain latest-run --ticket T-399000.

5. Expliquez les messages du même billet : python -m scripts.explain messages --ticket T-399000. La page du billet utilise cette requête.

6. Affichez seulement le plan, sans exécuter la requête : python -m scripts.explain queue --no-analyze.

7. Ouvrez psql avec docker compose exec db psql -U tickets -d tickets. Exécutez les deux jointures de cette leçon, puis la recherche par clé EXPLAIN ANALYZE SELECT name FROM customers WHERE customer_id = 'C-3835';. Quittez avec \q (quitter).

Afficher le résultat attendu

L’étape 2 affiche trois lignes. Une exécution sur l’ordinateur du cours a affiché :

200 requests, 4 at a time, pool on (max 10)
median 438.7 ms | p95 500.8 ms | slowest 574.7 ms | 9.0 requests/s
errors: 0 {200: 200} | database queries per request: 41

Votre médiane est de quelques centaines de millisecondes. Elle dépend de votre ordinateur et des autres programmes actifs : sur un ordinateur occupé, l’auteur a obtenu 558 ms. C’est pourquoi vous comparez seulement les exécutions de votre propre ordinateur, dans les mêmes conditions. Deux lignes WARNING ticket_api: API_KEY is not set (avertissement : API_KEY n’est pas définie) peuvent apparaître d’abord. Elles indiquent seulement que /v1/classify n’a pas de clé sur votre ordinateur.

Les étapes 3 et 4 montrent les deux parcours complets de cette leçon : Parallel Seq Scan on tickets t (parcours séquentiel parallèle sur tickets t) avec Rows Removed by Filter: 99447 (99 447 lignes retirées par le filtre), et Parallel Seq Scan on ai_runs (parcours séquentiel parallèle sur ai_runs) avec Rows Removed by Filter: 149911 (149 911 lignes retirées par le filtre). L’étape 5 en montre un troisième :

        ->  Parallel Seq Scan on messages  (cost=0.00..15385.30 rows=1 width=98) (actual time=12.755..12.758 rows=1.00 loops=3)
              Filter: (ticket_id = 'T-399000'::text)
              Rows Removed by Filter: 230098

Il lit les 690 297 messages, environ 230 099 par processus, pour trouver les 3 messages d’un seul billet. L’exécution du cours a pris 9,4 ms. L’étape 6 affiche les étapes de l’étape 3, avec seulement le premier groupe de parenthèses sur chaque ligne. L’étape 7 affiche les plans de cette leçon.

Vous savez maintenant où passe le temps : trois requêtes lisent des tables entières pour trouver quelques lignes. La prochaine leçon les corrige, un index à la fois.

Variante autonome : un gros client, un petit client

python -m scripts.explain customer --customer C-3835 explique la requête qui liste les billets d’un client. C-3835 est le client d’affaires, avec 4 322 billets; C-0042 en a 5. Faites d’abord une prédiction : laquelle des deux requêtes est la plus rapide, et de combien? Exécutez ensuite les deux, puis comparez les lignes de parcours et les temps.

Afficher un exemple de réponse

Les lignes de parcours lors de l’exécution de l’auteur :

              ->  Parallel Seq Scan on tickets t  (cost=0.00..19385.50 rows=1783 width=201) (actual time=0.107..13.372 rows=1440.67 loops=3)
                    Filter: (customer_id = 'C-3835'::text)
                    Rows Removed by Filter: 98559

              ->  Parallel Seq Scan on tickets t  (cost=0.00..19385.50 rows=7 width=201) (actual time=7.770..16.782 rows=1.67 loops=3)
                    Filter: (customer_id = 'C-0042'::text)
                    Rows Removed by Filter: 99998

Les deux temps étaient proches. Le cours a mesuré une requête semblable, avec moins de colonnes : 11,2 ms pour C-3835 et 11,6 ms pour C-0042. La plupart des gens prédisent que la requête pour 5 billets sera beaucoup plus rapide. Ce n’est pas le cas, car un parcours séquentiel lit toutes les lignes dans les deux cas. La taille de la table détermine le travail, pas le nombre de correspondances. Les estimations (1 783 et 7 par processus) suivent les tendances du tableau ci-dessus.

Erreur courante

Le planificateur pense encore que la table ressemble à celle de la semaine dernière

Voici un incident fictif. Imaginez une panne du système de paiement : en une heure, 50 000 nouveaux billets de facturation ouverts arrivent. Vous pouvez reproduire la situation sans risque dans une transaction, car ROLLBACK (annuler la transaction) supprime ensuite tout. Dans psql :

SET max_parallel_workers_per_gather = 0;
BEGIN;
INSERT INTO tickets (ticket_id, customer_id, subject, body, channel, team, priority)
SELECT 'T-' || (800000 + i), 'C-0001', 'Payment page is down', 'I cannot pay.', 'web_form', 'billing', 1
FROM generate_series(1, 50000) AS i;
EXPLAIN ANALYZE SELECT count(*) FROM tickets WHERE status = 'open' AND team = 'billing';

Symptôme : la ligne de parcours du plan indique :

   ->  Seq Scan on tickets  (cost=0.00..22323.00 rows=1667 width=0) (actual time=0.006..24.913 rows=51659.00 loops=1)

Le planificateur prévoyait 1 667 lignes et en a trouvé 51 659 : 31 fois plus. Ici, le plan prévoit un parcours complet dans les deux cas. Mais le planificateur préparerait une jointure pour 1 667 lignes. Cette jointure peut être très lente pour 51 659 lignes.

Cause : les statistiques sont plus anciennes que les nouvelles lignes. Le processus d’arrière-plan de PostgreSQL, autovacuum, relance ANALYZE (mettre à jour les statistiques) après de nombreux changements, mais pas immédiatement.

Diagnostic : cherchez un grand écart entre les lignes prévues et réelles à la première étape qui lit la table. Vérifiez ensuite quand PostgreSQL a produit les dernières statistiques : SELECT last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname = 'tickets';.

Correction : recueillez de nouvelles statistiques, puis expliquez de nouveau la requête :

ANALYZE tickets;
EXPLAIN ANALYZE SELECT count(*) FROM tickets WHERE status = 'open' AND team = 'billing';

   ->  Seq Scan on tickets  (cost=0.00..23073.00 rows=19589 width=0) (actual time=0.007..23.342 rows=51659.00 loops=1)

C’est mieux, mais l’estimation reste 2,6 fois trop faible. Le planificateur multiplie les deux proportions, comme si les conditions étaient indépendantes. Or, les nouveaux billets sont tous ouverts et liés à la facturation. Les statistiques étendues indiquent au planificateur que deux colonnes sont liées :

CREATE STATISTICS tickets_status_team (mcv) ON status, team FROM tickets;
ANALYZE tickets;
EXPLAIN ANALYZE SELECT count(*) FROM tickets WHERE status = 'open' AND team = 'billing';

   ->  Seq Scan on tickets  (cost=0.00..23073.00 rows=52593 width=0) (actual time=0.007..23.867 rows=51659.00 loops=1)

Le planificateur prévoit maintenant 52 593 lignes et en trouve 51 659. Nettoyez avec ROLLBACK; : cette commande supprime les billets, l’objet de statistiques et les nouvelles statistiques. Exécutez ensuite VACUUM ANALYZE tickets; pour libérer l’espace utilisé par les lignes supprimées, puis quittez. Vos estimations diffèrent un peu, car ANALYZE échantillonne les lignes.

Quand lire un plan, et à quel coût

Lisez un plan quand une requête est plus lente que prévu. Faites-le avant de modifier la requête ou d’ajouter un index, puis après le changement. Lisez-le sur des données semblables aux données réelles : le plan pour 200 lignes ne disait rien sur 300 000 lignes. Connaissez les limites :

• EXPLAIN ANALYZE exécute la requête. Pour un SELECT, rien ne change. Pour un INSERT, un UPDATE ou un DELETE, le changement a vraiment lieu. Placez donc la commande entre BEGIN et ROLLBACK, comme dans la prochaine leçon avec un DELETE.
• Une exécution est un échantillon. La première exécution peut lire des pages sur le disque; les suivantes les trouvent en mémoire. Les temps des plans du cours sont les médianes de 5 exécutions avec les données déjà en mémoire.
• Le plan montre seulement la base de données. Il n’inclut pas le réseau, Python ni l’attente d’une connexion. C’est pourquoi le projet contient à la fois scripts.explain et scripts.bench.
• EXPLAIN seul fournit uniquement des estimations. Il est sûr et rapide. Utilisez-le donc pour une requête qui prend des minutes.

De bout en boutRecherche vectorielle dans une collection de documents ouverteÉtude de cas

L’étude de cas arXiv examine les plans d’une recherche vectorielle filtrée. Pour une catégorie rare, le planificateur délaisse l’index vectoriel et effectue une recherche exacte.

Vérification des connaissances

Vérifiez votre compréhension

Une ligne du plan indique (actual time=0.283..8.997 rows=553.00 loops=3). Combien de lignes cette étape a-t-elle trouvées au total, et pourquoi pas 553?

Afficher la réponse

1 659. rows (lignes) est la moyenne par boucle. loops=3 indique que l’étape s’est exécutée trois fois : dans deux processus auxiliaires et dans le processus principal d’un parcours parallèle. 553 × 3 = 1 659. Multipliez toujours rows par loops (boucles) avant de comparer ce résultat avec un autre nombre.

Vérifiez votre compréhension

La requête de liste lit 300 000 billets pour en retourner 20. Celle de la dernière exécution lit 449 734 exécutions d'IA pour en retourner 1. Laquelle coûte le plus à la page de liste des billets, et comment le savez-vous?

Afficher la réponse

La requête de la dernière exécution. Une exécution prend 8,3 ms, un peu moins que les 11,0 ms de la requête de liste. Mais la page l’exécute 20 fois : environ 166 ms contre 11 ms. Le plan donne le temps d’une exécution, et le code indique combien de fois elle s’exécute. Il faut les deux pour trouver le coût le plus élevé.

Vérifiez votre compréhension

Le planificateur prévoit 17 billets pour C-0042 et en trouve 5. Devriez-vous vous inquiéter? Donnez un cas où un écart entre les lignes prévues et réelles aurait de l'importance.

Afficher la réponse

Pas ici : 17 et 5 sont deux petits nombres, et tout plan pour si peu de lignes est rapide. Un écart compte lorsqu’il est grand et change le choix du planificateur. Par exemple, après l’importation de 50 000 billets, le planificateur prévoit 1 667 lignes et choisit une boucle imbriquée. La recherche s’exécute alors 51 659 fois. La correction est ANALYZE, avec des statistiques étendues lorsque deux colonnes sont liées.

Récapitulatif

• Un plan de requête est la liste des étapes que PostgreSQL choisit pour une requête. EXPLAIN l’affiche; EXPLAIN ANALYZE exécute la requête et ajoute les nombres réels de lignes et les temps; BUFFERS ajoute les pages.
• Lisez un plan à partir de la ligne la plus en retrait, en remontant. Comparez l’estimation rows= avec la valeur réelle rows= × loops.
• Un Seq Scan (parcours séquentiel) lit toute la table; un Index Scan (parcours d’index) passe par un index. Un Hash Join (jointure par hachage) construit une table de recherche à partir d’une entrée. Une Nested Loop (boucle imbriquée) cherche dans la deuxième entrée pour chaque ligne de la première.
• Sur le grand jeu de données, trois requêtes lisaient des tables entières. La liste prenait 11,0 ms; la dernière exécution d’IA, 8,3 ms (20 fois par page); les messages, 9,4 ms.
• Les estimations viennent de statistiques fondées sur un échantillon. Corrigez les statistiques anciennes avec ANALYZE, et les estimations pour des colonnes liées avec des statistiques étendues.
• Vous avez enregistré bench-before.json. À venir : un index pour chaque requête lente, et son coût.

Leçon suivante : Indexer de façon réfléchie