Les index d'une base de données représentent typiquement plus des trois quarts de l'espace de stockage utilisé par la base, voir souvent plus (80% est un chiffre qui n'est pas absurde).

Le but d'une base de données relationnelle est de stocker les données de manière efficiente, en évitant le gaspillage d'espace, c'est vrai, mais il faut aussi pouvoir lire les données de façon rapide par moment. Et cette rapidité de lecture, ou encore la rapidité à vérifier des contraintes en écriture, est apportée par les index.

Sans index la base de données serait réduite à l'examen complet des tables, séquentiellement, à chaque fois qu'une condition ou un tri serait appliqué.

Donc, vive les index, les index nous rendent service, les index sont bons. Mais ils sont coûteux en terme d'espace. Et puis ils peuvent aussi nous mettre des bâtons dans les roues.

Un petit exemple d'index embêtant

Nous allons jouer avec une petite table toute simple, qui possède une contrainte d'unicité. Nous prenons en cas d'exemple une table d'utilisateurs, qui possèdent un identifiant unique, classique, mais qui ont aussi une contrainte d'unicité sur la colonne "email", comme ça nous sommes certains de ne jamais enregistrer deux fois le même email dans la table.

Comme d'habitude avec nos article SQL nous utiliserons SQL Fiddle pour avoir de quoi tester en direct les exemples.

Et nous commençons tout suite en créant cette table.

http://sqlfiddle.com/#!17/d75ef/1

CREATE TABLE "user"
(
  "id" bigserial PRIMARY KEY,
  "email" text NOT NULL,
  "name" text NOT NULL,
  "date_created" timestamp with time zone NOT NULL DEFAULT NOW(),
  "active" bool DEFAULT true
);

CREATE UNIQUE INDEX unique_user_email_idx
  ON "user" (email);

INSERT INTO "user" ("email", "name") VALUES ('roger@example.com', 'Roger');
INSERT INTO "user" ("email", "name") VALUES ('pierette@example.com', 'Henriette');

Dans le bloc du SQL Fiddle qui contient `SELECT * FROM "user";` essayez de taper à la place une requête d'insertion avec un email déjà existant:

http://sqlfiddle.com/#!17/a7e9b/2

INSERT INTO "user" ("email", "name") VALUES ('pierette@example.com', 'Pierrette');
-- ERROR: duplicate key value violates unique constraint "unique_user_email_idx" Detail: Key (email)=(pierette@example.com) already exists.

C'est bien l'effet escompté.

Vous avez peut-être remarqué la colonne "active" dans cette table qui contient un booléen. L'application qui manipule cette table ne fait pas de vraies suppressions, quand une ligne est désactivée dans l'application on préfère passer le booléen "active" à faux plutôt que de supprimer la ligne.

Cette façon de procéder est assez courante et permet de ne pas avoir à nettoyer les références à cet utilisateur dans les autres tables. Une bonne utilisation des contraintes d'intégrité référentielles (`ON DELETE/UPDATE CASCADE/SET DEFAUL/SET NULL, etc.` pourrait résoudre une partie des problèmes, mais parfois on veut garder l'information, y compris sur un utilisateur qui n'est plus actif).

Et donc allons-y, en fait notre deuxième ligne avec Henriette était fausse, puisque l'email n'était pas le bon. Donc on désactive cette ligne avant d'insérer la nouvelle version :

http://sqlfiddle.com/#!17/d75ef/3

UPDATE "user"
 SET "active" = false
 WHERE "email" = 'pierette@example.com'
 AND name = 'Henriette';

INSERT INTO "user" ("email", "name") VALUES ('pierette@example.com', 'Pierrette');

Et là on a un problème, l'update passe bien, mais l'insertion de la nouvelle ligne ne passe toujours pas : `ERROR: duplicate key value violates unique constraint "unique_user_email_idx" Detail: Key (email)=(pierette@example.com) already exists.`

On se retrouve à briser la contrainte d'unicité parce qu'une ligne qui a le booléen `"active"` à faux contient déjà le même email.

Le mauvais fix

Si on pense un petit peu trop vite on peut se dire que l'erreur venait du fait que la contrainte d'unicité était uniquement sur l'email.

Donc on refait le schéma mais cette fois avec une contrainte d'unicité sur le couple `("email", "active")`.

http://sqlfiddle.com/#!17/fb8b3/1

CREATE TABLE "user"
(
  "id" bigserial PRIMARY KEY,
  "email" text NOT NULL,
  "name" text NOT NULL,
  "date_created" timestamp with time zone NOT NULL DEFAULT NOW(),
  "active" bool DEFAULT true
);

CREATE UNIQUE INDEX unique_user_email_idx
  ON "user" ("email", "active");

INSERT INTO "user" ("email", "name") VALUES ('roger@example.com', 'Roger');
INSERT INTO "user" ("email", "name") VALUES ('pierette@example.com', 'Henriette');

UPDATE "user"
 SET "active" = false
 WHERE "email" = 'pierette@example.com'
 AND name = 'Henriette';
 
 INSERT INTO "user" ("email", "name") VALUES ('pierette@example.com', 'Pierrette');

On dirait que ça marche.

Mais en fait pas du tout. Disons qu'en fait Pierrette est pas hyper sympa comme utilisatrice, genre un troll absolu, et qu'on veut lui désactiver son compte:

http://sqlfiddle.com/#!17/fb8b3/2

UPDATE "user"
 SET "active" = false
 WHERE "email" = 'pierette@example.com'
 AND name = 'Pierrette';

Et là oups : ERROR: duplicate key value violates unique constraint "unique_user_email_idx" Detail: Key (email, active)=(pierette@example.com, f) already exists.`.

Impossible de désactiver le compte de Pierrette puisqu'il existe déjà un enregistrement désactivé avec ce même email.

Le bon fix

La vraie solution à ce problème est de créer une contrainte d'unicité qui ne s'applique que sur les lignes actives.

Dans les lignes qui auront le booléen `"active"` à faux, on ne veut pas vérifier cette contrainte, on s'en fiche.

Et donc la bonne contrainte à créer sur cette table est:

http://sqlfiddle.com/#!17/e153f/1

CREATE UNIQUE INDEX unique_user_email_idx
  ON "user" ("email")
  WHERE "active";

Notez que `WHERE "active"` est la même chose que `WHERE "active" = true` mais ça fait moins petit sixième qui rentre au collège.

Les index filtrés

Nous venons d'en voir un à l’œuvre, il faut bien sur imaginer que ce `WHERE` utilisé dans l'index peut être plus complexe qu'un simple filtrage sur une colonne de booléens.

Vous pourriez avoir besoin de filtrer certaines requêtes de façon un peu brutale pour un sous ensemble de vos données, et donc avoir besoin d'optimiser ce filtrage uniquement pour ce sous ensemble.

Par exemple avec une requête comme celle-ci:

http://sqlfiddle.com/#!17/926b17/1

SELECT "name", "date_created"
FROM "user"
WHERE "active"
  AND substring("email" from '@(.*)$')  = 'example.com'
ORDER BY
  "name" ASC,
  "date_created" DESC
;

En imaginant qu'on a des volumes plus imposants dans cette table que ce que l'on a effectivement dans notre démo, on pourrait avoir besoin d'un index (là le temps gagné est ridicule, mais il faut imaginer la même chose avec plus de volume):

http://sqlfiddle.com/#!17/2b663/1

CREATE INDEX user_email_example_com_name_date_idx
  ON "user" ("name", "date_created" DESC)
  WHERE substring("email" from '@(.*)$') = 'example.com'
    AND "active";

Cet index est alors très précisément taillé pour cette requête, sur ce domaine `example.com`, mais sa taille finale sur disque est moins importante que s'il avait été créé sans ses conditions `WHERE`.

CREATE INDEX user_email_example_com_name_date_idx
  ON "user" ("name", "date_created" DESC)
  WHERE substring("email" from '@(.*)$') = 'example.com'
    AND "active";

Au fait, n'oubliez jamais d'examiner l'usage réel de vos index sur la base de production, et de le comparer à l'espace disque utilisé. Voici une requête légèrement adaptée de https://wiki.postgresql.org/wiki/Index_Maintenance#Index_size.2Fusage_statistics qui vous donnera une petite liste d'index triés par taille de l'index, regardez s'il est vraiment utilisé. Notez qu'en dehors de la production vos volumes de données sont sans doute trop faibles pour que l'analyseur de requête décide d'utiliser les index, ce qui expliquera la présence de très nombreux index non utilisés en dehors de la production (espèce de petit sixième).

SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid AND psai.schemaname = 'public' )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY pg_relation_size(quote_ident(indexrelname)::text) DESC, pg_relation_size(quote_ident(indexrelname)::text) DESC;

mais vous pouvez aussi aller vous balader plus simplement dans les vues du catalogue :

SELECT * FROM pg_catalog.pg_stat_user_indexes;
SELECT * FROM pg_catalog.pg_statio_user_indexes;