PostgreSQL : index et performances

Comprendre pourquoi une requête est lente et la corriger, depuis psql. Les exemples utilisent les tables clients et commandes.

Les index

Un index accélère la recherche, au prix d’un peu d’espace et d’écritures plus lentes. Sans index, PostgreSQL parcourt toute la table (Seq Scan).

CREATE INDEX idx_commandes_client ON commandes (client_id);
CREATE INDEX idx_commandes_statut_date ON commandes (statut, passee_le);   -- index composé
CREATE UNIQUE INDEX uq_clients_nom_ville ON clients (nom, ville);
CREATE INDEX idx_commandes_payees ON commandes (passee_le) WHERE statut = 'payee';   -- index partiel
CREATE INDEX idx_clients_email_min ON clients (lower(email));                        -- index sur expression
\di
DROP INDEX idx_commandes_payees;

Type	Usage
`btree` (défaut)	Égalité, comparaisons, tri (`=`, `<`, `>`, `BETWEEN`, `ORDER BY`)
`gin`	`jsonb`, tableaux, recherche plein texte
`gist`	Géométrie, plages, recherche par proximité
`brin`	Très grandes tables ordonnées naturellement (horodatage) : index minuscule

PostgreSQL n’indexe pas automatiquement les clés étrangères : indexer client_id de commandes quand les jointures et suppressions en cascade sont fréquentes.

Dans un index composé, l’ordre des colonnes compte : (statut, passee_le) sert aux filtres sur statut, ou sur statut et passee_le, mais pas sur passee_le seul.

Créer un index sans bloquer les écritures

CREATE INDEX CONCURRENTLY idx_commandes_montant ON commandes (montant);

CONCURRENTLY évite de verrouiller la table en écriture pendant la construction (plus lent, et ne s’utilise pas dans une transaction).

`EXPLAIN` : lire le plan d’exécution

EXPLAIN SELECT * FROM commandes WHERE client_id = 1;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM commandes WHERE client_id = 1;   -- exécute et mesure

Élément du plan	Signification
`Seq Scan`	Parcours complet de la table (acceptable sur une petite table)
`Index Scan`, `Index Only Scan`, `Bitmap Heap Scan`	Utilisation d’un index
`cost=0.28..8.30`	Coût estimé (démarrage..total)
`rows=`	Lignes estimées ; avec `ANALYZE`, comparées aux lignes réelles (`actual rows`)
`Execution Time`	Durée réelle (avec `ANALYZE`)

Maintenance : `VACUUM` et `ANALYZE`

PostgreSQL conserve les anciennes versions des lignes modifiées ; VACUUM récupère l’espace et ANALYZE met à jour les statistiques utilisées par le planificateur. Le démon autovacuum le fait automatiquement.

VACUUM (VERBOSE, ANALYZE) commandes;
ANALYZE;                                     -- statistiques de toute la base
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
REINDEX TABLE CONCURRENTLY commandes;        -- reconstruit les index d'une table

Voir ce qui s’exécute

SELECT pid, usename, state, now() - query_start AS duree, left(query, 80) AS requete
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duree DESC;

SELECT pg_cancel_backend(12345);      -- interrompt la requête du processus 12345
SELECT pg_terminate_backend(12345);   -- coupe la connexion

Requêtes lentes

Journaliser les requêtes qui dépassent une durée :

ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();

sudo tail -f /var/log/postgresql/postgresql-18-main.log

L’extension pg_stat_statements agrège les statistiques par requête :

-- Prérequis : shared_preload_libraries = 'pg_stat_statements' dans postgresql.conf, puis redémarrage
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT calls, round(total_exec_time::numeric, 1) AS ms_total,
       round(mean_exec_time::numeric, 2) AS ms_moyen, left(query, 60) AS requete
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Tailles

SELECT pg_size_pretty(pg_database_size('boutique'));
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS taille
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
\l+
\dt+

Configuration

SHOW shared_buffers;
SHOW work_mem;
SHOW max_connections;
ALTER SYSTEM SET work_mem = '16MB';     -- écrit dans postgresql.auto.conf
SELECT pg_reload_conf();
SELECT name, setting, unit, pending_restart FROM pg_settings WHERE name IN ('shared_buffers', 'work_mem');

Paramètre	Rôle
`shared_buffers`	Cache de données de PostgreSQL : environ 25 % de la mémoire sur un serveur dédié (redémarrage nécessaire)
`work_mem`	Mémoire par opération de tri ou de jointure (par requête, à augmenter avec prudence)
`maintenance_work_mem`	Mémoire pour `VACUUM` et `CREATE INDEX`
`max_connections`	Connexions simultanées (au-delà de quelques centaines, utiliser un répartiteur comme PgBouncer)

Réflexes pour une requête lente

EXPLAIN (ANALYZE, BUFFERS) : repérer Seq Scan sur une grande table et les écarts entre rows estimées et réelles.
Ajouter l’index manquant (filtre, jointure, tri).
Mettre les statistiques à jour (ANALYZE) si les estimations sont très éloignées.
Ne sélectionner que les colonnes nécessaires, limiter et paginer.
Éviter les fonctions sur une colonne indexée dans le WHERE (utiliser un index sur expression si besoin).

Pour aller plus loin

Manuel : Performance Tips
Sauvegarde : Sauvegarde et restauration