Chapitre 1 · Leçon 2
EXPLAIN ANALYZE, index composites et stratégies d'optimisation
Section 1
Step 1
Un index est une structure de données auxiliaire qui accélère la recherche. Sans index, PostgreSQL fait un Sequential Scan (lire toutes les lignes). Avec index, c'est un Index Scan (lecture directe).
snippet.sql
1-- Index B-Tree (défaut) — pour =, <, >, BETWEEN, LIKE 'prefix%'
2CREATE INDEX idx_tutorials_slug ON tutorials(slug);
3CREATE INDEX idx_tutorials_level ON tutorials(level);
4CREATE UNIQUE INDEX idx_users_email ON users(email);
5
6-- Index composite — pour les requêtes multi-colonnes
7-- Requête : WHERE category_slug = 'web' AND level = 'Beginner'
8CREATE INDEX idx_tutorials_cat_level ON tutorials(category_slug, level);
9-- ⚠️ Ordre des colonnes important : mettre la colonne la plus sélective en 1er
10
11-- Index partiel — indexer seulement une partie des lignes
12-- Requête : WHERE published_at IS NOT NULL (tutoriels publiés)
13CREATE INDEX idx_tutorials_published ON tutorials(published_at)
14 WHERE published_at IS NOT NULL;
15-- Plus petit → plus rapide
16
17-- Index GIN — pour JSONB, tableaux et full-text search
18CREATE INDEX idx_lessons_content ON lessons USING gin(content jsonb_path_ops);
19-- Permet : WHERE content @> '{"mode": "steps"}'
20
21-- Index GiST — pour la recherche géographique, full-text
22CREATE INDEX idx_tutorials_title_search ON tutorials
23 USING gin(to_tsvector('french', title || ' ' || description));
24
25-- Analyser l'utilisation des index
26SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch
27FROM pg_stat_user_indexes
28WHERE relname = 'tutorials'
29ORDER BY idx_scan DESC;Step 2
EXPLAIN ANALYZE est l'outil indispensable pour comprendre comment PostgreSQL exécute une requête et identifier les goulots d'étranglement.