Index-Only Scan — Un index qui saute la table

Sauter la consultation de la table — l'Index-Only Scan

Une recherche par index classique trouve la ligne cible dans l'index, puis retourne à la table de base pour lire les autres colonnes une ligne à la fois.

Ce retour s'appelle une consultation de table (table lookup : l'étape qui tire une ligne de la table de base via l'index).

Quand l'ensemble de résultats est grand, ces allers-retours s'accumulent et commencent à coûter du temps réel.

Si tu utilises un index qui contient toutes les colonnes que la requête référence, toutes les valeurs sont déjà dans l'index, et inutile de retourner à la table elle-même.

Ce motif, où l'index seul livre le résultat, s'appelle un Index-Only Scan (index couvrant) (un index qui couvre toutes les colonnes que la requête touche est aussi appelé covering index en anglais).

-- Agréger region avec un index qui ne contient que region
-- La colonne référencée (region) est entièrement dans l'index → pas de retour à la table de base
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region);

EXPLAIN QUERY PLAN
SELECT region, COUNT(*)
FROM perf_sales
GROUP BY region;

Imagine l'exigence : « On veut le total des ventes par commercial. » Cette requête ne touche que deux colonnes : emp_id et amount. Construis un index qui contient les deux, et confirme que le plan livre le résultat depuis l'index seul, sans retourner à la table de base. Supprime et reconstruis l'index dans cette seule exécution de console pour que le plan soit totalement autonome. (Exécute-le correctement pour révéler l'explication.)

① Supprime l'index avec DROP INDEX IF EXISTS.

② Crée un index qui contient à la fois emp_id et amount — les colonnes que la requête référence.

③ Utilise EXPLAIN QUERY PLAN sur la requête d'agrégation qui somme amount par emp_id, et confirme que le plan ne lit jamais la table de base.

Manque une seule colonne et tu retournes à la table de base

Un Index-Only Scan ne se produit que quand toutes les colonnes que la requête touche — dans SELECT, WHERE, GROUP BY, etc. — sont dans l'index.

Manque une seule colonne et la base doit retourner à la table de base pour la lire, et USING COVERING INDEX disparaît du plan.

Par exemple, avec un index sur (emp_id, amount), écrire SELECT emp_id, SUM(amount), region ajoute region, qui n'est pas dans l'index — donc la requête retourne à la table de base pour le récupérer.

-- Avec un index sur (region, amount),
-- ajouter product aux colonnes référencées casse l'Index-Only Scan
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, amount);

-- region, SUM(amount) seulement → entièrement couvert par l'index
EXPLAIN QUERY PLAN
SELECT region, SUM(amount) FROM perf_sales GROUP BY region;

-- Ajoute product → pas dans l'index, donc retour à la table de base
EXPLAIN QUERY PLAN
SELECT region, SUM(amount), MAX(product) FROM perf_sales GROUP BY region;

En utilisant le même index, compare deux agrégations : une dont les colonnes référencées tiennent dans l'index, et une qui ajoute une seule colonne qui n'y est pas. Construis un index composite sur emp_id et amount, puis regarde les deux plans côte à côte.

① Supprime l'index avec DROP INDEX IF EXISTS.

② Crée un index composite contenant emp_id et amount.

③ Montre EXPLAIN QUERY PLAN pour l'agrégation qui somme amount par emp_id (tient dans l'index).

④ Montre ensuite EXPLAIN QUERY PLAN pour la même agrégation plus le maximum de region, et compare avec ③ pour voir comment le plan change (region n'est pas dans l'index).

Garde l'Index-Only Scan en incluant aussi les colonnes filtre du WHERE

SELECT n'est pas le seul endroit où les colonnes apparaissent dans ta requête.

Les colonnes filtre du WHERE comptent aussi comme des colonnes touchées par la requête, donc elles doivent être dans l'index aussi — sinon tu finis encore par retourner à la table de base.

Plie-les toutes dans un seul index dans l'ordre « colonnes filtre → colonnes de sortie / d'agrégation », et le filtrage comme la récupération des valeurs se terminent dans le même index.

Par exemple, pour filtrer avec WHERE region = 'East' et calculer SUM(amount), mets la colonne filtre region en premier et la colonne d'agrégation amount en second : (region, amount).

L'index restreint aux lignes cibles par region et lit amount depuis le même index, donc aucun retour à la table de base n'est nécessaire.

-- Plier une colonne filtre (status) et une colonne d'agrégation (amount) dans un seul index
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(status, amount);

EXPLAIN QUERY PLAN
SELECT SUM(amount)
FROM perf_sales
WHERE status = 'pending';

Imagine l'exigence : « On veut le total des ventes pour une region précise. » La requête filtre sur region dans WHERE et calcule le total de amount. Plie la colonne filtre et la colonne d'agrégation dans un seul index, et confirme que le plan ne retourne jamais à la table de base.

① Supprime l'index avec DROP INDEX IF EXISTS.

② Crée un index composite avec la colonne filtre region en premier et la colonne d'agrégation amount en second.

③ Utilise EXPLAIN QUERY PLAN sur l'agrégation qui filtre par region et somme amount, et confirme que le plan ne lit jamais la table de base.