• Qu’est-ce qu’un index ?
• Comment indexer une base ?
• Les index B-tree dans PostgreSQL

• Comprendre ce qu’est un index
• Maîtriser le processus de création d’index
• Connaître les différents types d’index B-tree et leurs cas d’usages

• Uniquement destinés à l’optimisation
• À gérer d’abord par le développeur
  • Markus Winand : SQL Performance Explained

• Un index permet de :
  • trouver un enregistrement dans une table directement
  • récupérer une série d’enregistrements dans une table
  • voire tout récupérer dans l’index (Index Only Scan)
• Un index facilite :
  • certains tris
  • certains agrégats
• Obligatoires et automatique pour clés primaires & unicité
  • conseillé pour clés étrangères (FK)

Un index améliore les SELECT

• Sans index :

=# SELECT * FROM test WHERE id = 10000;
Temps : 1760,017 ms

• Avec index :

=# CREATE INDEX idx_test_id ON test (id);

=# SELECT * FROM test WHERE id = 10000;
Temps : 27,711 ms

• L’index n’est pas gratuit !
• Ralentit les écritures
  • maintenance
• Place disque
• Compromis à trouver

• Lourd…

-- bloque les écritures !
CREATE INDEX ON matable ( macolonne ) ;
-- ne bloque pas, peut échouer
CREATE INDEX CONCURRENTLY ON matable ( macolonne ) ;

• Si fragmentation :

REINDEX INDEX nomindex ;
REINDEX TABLE CONCURRENTLY nomtable ;

• Paramètres :
  • maintenance_work_mem (sinon : fichier temporaire !)
  • max_parallel_maintenance_workers

• Défaut : B-tree classique (équilibré)
• UNIQUE (préférer la contrainte)
• Mais aussi multicolonne, fonctionnel, partiel, couvrant
• Index spécialisés : hash, GiST, GIN, BRIN, HNSW….

• Structure associant des clés (termes) à des localisations (pages)
• Structure de données spécialisée, plusieurs types
• Séparée de la table
• Analogies :
  • fiches en carton des bibliothèques avant l’informatique (B-tree)
  • index d’un livre technique (GIN)

• Un index ne résout pas tout
• Importance de la conception du schéma de données
• Importance de l’écriture de requêtes SQL correctes

• Type d’index le plus courant
  • et le plus simple
• Utilisable pour les contraintes d’unicité
• Supporte les opérateurs : <, <=, =, >=, >
• Supporte le tri
• Ne peut pas indexer des colonnes de plus de 2,6 ko

SELECT name FROM ma_table WHERE id = 22

• Possibilité d’indexer plusieurs colonnes :

    CREATE INDEX ON ma_table (id, name) ;

• Ordre des colonnes primordial
  • accès direct aux premières colonnes de l’index
  • pour les autres, PostgreSQL lira tout l’index ou ignorera l’index

• Index Scan
• Bitmap Scan
• Index Only Scan
  • idéal pour les performances
• et les variantes parallélisées

• On indexe pour une requête
  • ou idéalement une collection de requêtes
• Et pas « une table »

• Identifier les requêtes nécessitant un index
• Créer les index permettant de répondre à ces requêtes
• Valider le fonctionnement, en rejouant la requête avec :

     EXPLAIN (ANALYZE, BUFFERS)

• Indexation des colonnes faisant référence à une autre
• Performances des DML
• Performances des jointures

C’est souvent tout à fait normal

• Utiliser l’index est-il rentable ?
• La requête est-elle compatible ?
• Bug de l’optimiseur : rare

• L’optimiseur pense qu’il n’est pas rentable
  • sélectivité trop faible
  • meilleur chemin pour remplir d’autres critères
  • index redondant
  • Index Only Scan nécessite un VACUUM fréquent
• Les estimations de volumétries doivent être assez bonnes !
  • statistiques récentes, précises

• Pas le bon type (CAST plus ou moins explicite)

EXPLAIN SELECT * FROM clients WHERE client_id = 3::numeric;

• Utilisation de fonctions, comme :

SELECT * FROM ma_table WHERE to_char(ma_date, 'YYYY')='2014' ;

SELECT * FROM fournisseurs WHERE commentaire LIKE 'ipsum%';

• Solution :

CREATE INDEX idx1 ON ma_table (col_varchar varchar_pattern_ops) ;

• CREATE INDEX … CONCURRENTLY peut échouer

De nombreuses possibilités d’indexation avancée :

• Index partiels
• Index fonctionnels
• Index couvrants
• Classes d’opérateur

• N’indexe qu’une partie des données :

CREATE INDEX on evenements (type) WHERE  traite IS FALSE ;

• Ne sert que si la clause est logiquement équivalente !
  • ou partie de la clause (inégalités, IN)
• Intérêt : index beaucoup plus petit

• Données chaudes et froides
• Index dédié à une requête avec une condition fixe

• Éviter les index de type :

CREATE INDEX ON matable ( champ_filtre ) WHERE champ_filtre = …

• Préférer :

CREATE INDEX ON matable ( champ_resultat ) WHERE champ_filtre = …

• Un index sur a est inutilisable pour :

  SELECT … WHERE upper(a)='DUPOND'

• Indexer le résultat de la fonction :

   CREATE INDEX mon_idx ON ma_table (upper(a)) ;

• Critère identique à la fonction dans l’index
• Fonction impérativement IMMUTABLE !
  • délicat avec les conversions de dates/heures
• Ne pas espérer d’Index Only Scan

• Ne pas oublier ANALYZE après création d’un index fonctionnel
  • les statistiques peuvent même être l’intérêt majeur (<v14)
• La fonction ne doit jamais tomber en erreur
• Si modification de la fonction
  • réindexation

• But : obtenir un Index Only Scan

CREATE UNIQUE INDEX clients_idx1
ON clients (id_client) INCLUDE (nom_client) ;

• Répondent à la clause WHERE
• ET contiennent toutes les colonnes demandées par la requête :

SELECT id_client,nom_client FROM clients WHERE id_client > 100 ;

• …si l’index n’est pas trop gros
  • à comparer à un index multicolonne

• Un index utilise des opérateurs de comparaison
• Texte : différentes collations = différents tris… complexes
  • Index inutilisable sur :

  WHERE col_varchar LIKE 'chaine%'

• Solution : opérateur varchar_pattern_ops :
  • force le tri caractère par caractère, sans la collation

  CREATE INDEX idx1
  ON ma_table (col_varchar varchar_pattern_ops)

• Plus généralement :
  • nombreux autres opérateurs pour d’autres types d’index

• Responsabilité de l’indexation
• Compréhension des mécanismes
• Différents types d’index, différentes stratégies

https://dali.bo/j4_quiz