EXPLIQUER ANALYSER dans PostgreSQL : comment lire un plan et quels index sont réellement nécessaires
Par où commencer : non pas avec un plan, mais avec une liste de demandes
Les index sont ajoutés pour des requêtes spécifiques, et non « pour toutes les colonnes de O ». Tout d’abord, recherchez quelles requêtes sont vraiment coûteuses. L'extension pg_stat_statements collecte des statistiques sur les requêtes normalisées :
CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- требует shared_preload_libraries
SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms,
left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;Le tri par total_exec_time montre où la base de données passe son temps au total. Une requête de 3 ms appelée un million de fois est souvent plus importante qu'un rapport de 2 secondes une fois par jour.
Comment supprimer un forfait
EXPLAIN (ANALYZE, BUFFERS) SELECT id, total, created_at FROM orders WHERE user_id = 4815 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;
ANALYZE exécute réellement la requête. Pour UPDATE et DELETE, enveloppez-le dans une transaction d'annulation :
BEGIN; EXPLAIN (ANALYZE, BUFFERS) DELETE FROM sessions WHERE expires_at < now(); ROLLBACK;
BUFFERS indique combien de pages ont été lues depuis le cache (shared hit) et depuis le disque (read). Dans PostgreSQL 18, ces informations sont affichées automatiquement avec ANALYZE ; dans les versions antérieures, le paramètre doit être spécifié explicitement.
Comment lire les nœuds
Plan - arbre. Lire de l'intérieur vers l'extérieur : les nœuds les plus imbriqués sont exécutés en premier. Chaque nœud possède deux groupes de nombres :
Limit (cost=0.00..1843.20 rows=20 width=24) (actual time=0.041..96.512 rows=20 loops=1)
-> Sort (cost=... rows=1180 ...) (actual time=96.501..96.506 rows=20 loops=1)
Sort Key: created_at DESC
Sort Method: top-N heapsort Memory: 27kB
-> Seq Scan on orders (cost=0.00..41250.00 rows=1180 width=24)
(actual time=0.030..95.880 rows=1342 loops=1)
Filter: ((user_id = 4815) AND (status = 'paid'::text))
Rows Removed by Filter: 1998658Il s'agit d'un plan illustratif, mais le modèle est typique :
- `cost` — évaluation du planificateur en unités conventionnelles. Cela n’a aucun sens de le comparer entre différentes demandes.
- `actual time=A..B` — millisecondes avant la première ligne et avant la dernière. Le temps des parents inclut le temps des enfants.
- `rows` dans l'évaluation et dans le fait. Un écart par ordre de grandeur est le principal signe d'un problème : l'ordonnanceur a choisi un plan pour un mauvais nombre de lignes.
- `loops` — combien de fois le nœud a été exécuté. Le temps réel et les chaînes doivent être multipliés par
loops. Nœud de boucle imbriquée interne avecloops=50000et le temps 0,02 ms est une seconde. - `Rows Removed by Filter` - lignes lues en vain. Ici, deux millions de lignes ont été consultées pour 1342 lignes nécessaires.
De quel index cette requête a-t-elle besoin ?
La requête filtre par user_id et status et trie par created_at. L'ordre des colonnes dans un index composite est d'abord égal aux colonnes, puis trié par la colonne.
CREATE INDEX CONCURRENTLY orders_user_status_created_idx ON orders (user_id, status, created_at DESC);
Avec un tel index, le plan se transforme en Index Scan avec Limit : la base de données lit exactement 20 enregistrements par index dans l'ordre requis et ne trie rien. CONCURRENTLY ne bloque pas l'écriture dans la table pendant la construction, mais ne fonctionne pas dans une transaction et, en cas d'échec, laisse un index invalide qui doit être supprimé et recréé.
Si presque toutes les requêtes examinent une seule valeur de statut, un index partiel est plus petit et moins coûteux à gérer :
CREATE INDEX CONCURRENTLY orders_pending_idx ON orders (created_at) WHERE status = 'pending';
Il ne sera utilisé que si la condition de requête inclut status = 'pending' littéralement.
Noeuds à surveiller
- `Sort Method: external merge Disk: ...` — le tri ne rentre pas dans
work_memet est allé sur le disque. Il est traité avec un index avec l'ordre souhaité ou croissantwork_mempour une session spécifique, et non globalement. - `Bitmap Heap Scan` avec `Recheck Cond` et `Heap Blocks: lossy` - le bitmap ne rentre pas dans la mémoire et est devenu paginé.
- `Index Only Scan` avec un grand `Heap Fetches` - l'index couvre la requête, mais la carte de visibilité est obsolète et la base de données va toujours dans la table.
VACUUMaide (ou définit l'autovacuum pour cette table). - `Hash Join` avec `Batches: 8` - la table de hachage ne rentre pas dans la mémoire.
- Estimation `rows=1`, mais fait - des milliers. Mise à jour des statistiques
ANALYZE orders;. Si les colonnes sont corrélées (ville et pays), aide des statistiques avancées :CREATE STATISTICS ... (dependencies) ON city, country FROM addresses;.
Pourquoi y a-t-il un index et n'est-il pas utilisé ?
- 1.Fonction ou cast sur une colonne :
WHERE lower(email) = $1n'utilise pas d'index suremail. Nous avons besoin d'un index basé sur l'expression(lower(email)). - 2.
LIKE '%abc'avec le pourcentage principal. B-tree ne vous aidera pas ici, vous avez besoin depg_trgmavec GIN. - 3.La condition sélectionne une grande partie du tableau. La lecture séquentielle est alors effectivement moins chère, et le planificateur a raison.
- 4.Différents types : colonne
bigint, paramètre passé commenumeric. - 5.Petite table. Pour quelques milliers de lignes,
Seq Scanest normal.
Les index supplémentaires coûtent également de l'argent
Chaque index ralentit les insertions et les mises à jour et prend de la place. Candidats à la suppression :
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;
Les statistiques sont locales sur le serveur : l'index, qui n'est pas nécessaire sur le serveur principal, peut fournir des rapports sur le réplica. Et ne supprimez pas les index qui fournissent UNIQUE ou la clé primaire.
Quand EXPLAIN n'affiche pas le problème
Si une requête est rapide lors d'une exécution manuelle, mais lente en production, cela peut être dû à un blocage, à un conflit de connexion ou à un plan différent pour la requête paramétrée. Activez auto_explain avec le seuil auto_explain.log_min_duration pour obtenir le plan d'exécution lent à partir des journaux et surveillez pg_stat_activity pour les attentes de Lock.