SQL Academy · Leçon

Index partiels et index d’expressions

Indexez uniquement les lignes qui vous intéressent avec des clauses WHERE, et indexez des expressions calculées (lower(email), date_trunc('day', ts)).

Index partiels et index d’expressions est une leçon SQL Academy gratuite sur CoddyKit. Ceci est la leçon 3 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.

Index partiel : indexer un sous-ensemble

Un index partiel ne couvre que les lignes correspondant à une clause WHERE lors de la création de l’index. Il est plus petit, plus rapide et idéal pour la requête courante :

CREATE INDEX users_active_email_idx
  ON users(email)
  WHERE deleted_at IS NULL;

Quand les index partiels sont avantageux

Utilisez-les lorsque :

Vous filtrez toujours selon le même prédicat (par exemple, la suppression logique)
Une valeur domine la colonne (par exemple, 95 % des lignes ont status='closed')
Vous voulez un petit index fréquemment utilisé qui reste en cache

Exemple : sessions actives

La plupart des sessions ont expiré ; seules les sessions actives sont interrogées :

CREATE INDEX sessions_active_idx ON sessions(user_id)
  WHERE expires_at > NOW();
-- Caveat: planner can't use NOW() in the index predicate — use a fixed timestamp
-- and reindex periodically, OR use a boolean column.

Mieux : utiliser un prédicat stable

Le prédicat de l’index doit être IMMUTABLE. Les expressions dépendant du temps, comme NOW(), ne conviennent pas. Utilisez plutôt une colonne comme is_active :

CREATE INDEX sessions_active_idx ON sessions(user_id)
  WHERE is_active = true;

-- The query must use the same predicate to match the index:
SELECT * FROM sessions WHERE is_active = true AND user_id = 42;

Index d’expression

Indexez une fonction appliquée à une colonne :

CREATE INDEX users_email_lower_idx ON users(LOWER(email));

SELECT * FROM users WHERE LOWER(email) = LOWER('Alice@Example.com');
-- Uses the expression index.

Index d’expression courants

LOWER(email) pour une recherche insensible à la casse
(price * tax_rate) pour un tri calculé
date_trunc('day', created_at) pour les rapports quotidiens
(data ->> 'user_id')::BIGINT pour extraire des données JSONB

L’expression doit être IMMUTABLE

L’expression doit être marquée IMMUTABLE — son résultat dépend uniquement de l’entrée. RANDOM(), NOW() et CURRENT_USER ne conviennent pas.

Combinaison d’index partiel et d’expression

Les deux peuvent être combinés :

CREATE INDEX articles_active_title_idx
  ON articles(LOWER(title))
  WHERE published_at IS NOT NULL;

Index partiels uniques

Le motif classique de la « une seule ligne active » :

CREATE UNIQUE INDEX users_one_active_email
  ON users(email)
  WHERE deleted_at IS NULL;

-- Same email can exist many times in deleted users, but only once active.

Le prédicat de l’index doit correspondre à la requête

Le planificateur n’utilise l’index partiel que lorsque la clause WHERE de la requête « implique » le prédicat de l’index :

-- Index:  WHERE is_active = true
-- Match:  WHERE is_active = true AND user_id = 42       ✓
-- Match:  WHERE is_active AND user_id = 42             ✓
-- No:     WHERE user_id = 42                           ✗
-- No:     WHERE is_active IS NOT FALSE                 ✗ (logically same but planner may not realise)

N’en abusez pas

De nombreux index partiels couvrant des sous-ensembles disjoints peuvent ralentir les écritures (chaque INSERT doit mettre à jour tous les index concernés). Utilisez-les avec parcimonie pour les requêtes très sollicitées.

Récapitulatif

Les index partiels et d’expression offrent davantage de performances pour chaque octet.

Partiel : indexer uniquement le sous-ensemble interrogé
Expression : indexer des valeurs calculées
Les deux nécessitent des prédicats IMMUTABLE
La requête doit utiliser le même prédicat pour correspondre

Vérification rapide

Vous voulez effectuer des recherches insensibles à la casse par adresse e-mail. Quel index est le plus efficace ?

Questions Fréquemment Posées

La leçon « Index partiels et index d’expressions » est-elle gratuite ?

Oui — le texte complet de « Index partiels et index d’expressions » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Index partiels et index d’expressions » ?

Indexez uniquement les lignes qui vous intéressent avec des clauses WHERE, et indexez des expressions calculées (lower(email), date_trunc('day', ts)). Tu pratiques SQL Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Academy ?

Aucune expérience préalable n'est requise. SQL Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 3 sur 4.

Combien de temps prend la leçon « Index partiels et index d’expressions » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Academy ?

Oui. Chaque leçon SQL Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

← Retour à SQL Academy