Index partiel
Qu’est-ce qu’un index partiel dans PostgreSQL ?
Un index partiel ne couvre que les lignes qui satisfont une condition WHERE, ce qui le garde petit et rapide lorsque les requêtes ciblent un sous-ensemble bien défini d’une table.
Un index partiel est un index construit sur un sous-ensemble d’une table, défini par une condition dans CREATE INDEX ... WHERE. Seules les lignes qui satisfont la condition reçoivent des entrées d’index.
Les index partiels sont rentables lorsque les requêtes se concentrent sur une petite partie d’une table, comme les tâches non terminées ou les commandes impayées, alors que la plupart des lignes sont rarement interrogées. Le planificateur ne peut utiliser l’index que s’il peut prouver que la clause WHERE de la requête implique la condition de l’index : écrivez donc la condition de la requête de façon à ce qu’elle lui corresponde. Un index unique partiel peut aussi imposer l’unicité au sein de ce seul sous-ensemble.
Exemple
L’exemple n’indexe que le 1 % de tâches non terminées. EXPLAIN montre que la requête sur les tâches ouvertes utilise tasks_open_idx.
CREATE TABLE tasks (id int PRIMARY KEY, done boolean NOT NULL, title text);
INSERT INTO tasks
SELECT g, g % 100 <> 0, 'task ' || g FROM generate_series(1, 100000) AS g;
CREATE INDEX tasks_open_idx ON tasks (id) WHERE NOT done;
ANALYZE tasks;
EXPLAIN SELECT id, title FROM tasks WHERE NOT done;Du SQL simple pour PostgreSQL 16 ou 17 standard, sans aucune extension. Exécutez-le dans une base de données vide que vous pouvez jeter ensuite.
Références
Documentation officielle de PostgreSQL (en anglais).