Description

CREATE INDEX construit un index sur le (ou les) colonne(s) spécifiée(s) de la relation spécifiée, qui peut être une table ou une vue matérialisée. Les index sont principalement utilisés pour améliorer les performances de la base de données (bien qu'une utilisation inappropriée puisse produire l'effet inverse).

Les champs clé pour l'index sont spécifiés à l'aide de noms des colonnes ou par des expressions écrites entre parenthèses. Plusieurs champs peuvent être spécifiés si la méthode d'indexation supporte les index multi-colonnes.

Un champ d'index peut être une expression calculée à partir des valeurs d'une ou plusieurs colonnes de la ligne de table. Cette fonctionnalité peut être utilisée pour obtenir un accès rapide à des données obtenues par transformation des données basiques. Par exemple, un index calculé sur upper(col) autorise la clause WHERE upper(col) = 'JIM' à utiliser un index.

PostgreSQL fournit les méthodes d'indexation B-tree (NDT : arbres balancés), hash (NDT : hachage), GiST (NDT : arbres de recherche généralisés), SP-GiST, GIN et BRIN. Il est possible, bien que compliqué, de définir des méthodes d'indexation utilisateur.

Lorsque la clause WHERE est présente, un index partiel est créé. Un index partiel est un index ne contenant des entrées que pour une portion d'une table, habituellement la portion sur laquelle l'indexation est la plus utile. Par exemple, si une table contient des ordres facturés et d'autres qui ne le sont pas, et que les ordres non facturés n'occupent qu'une petite fraction du total de la table, qui plus est fréquemment utilisée, les performances sont améliorées par la création d'un index sur cette portion. Une autre application possible est l'utilisation de la clause WHERE en combinaison avec UNIQUE pour assurer l'unicité sur un sous-ensemble d'une table. Voir Section 11.8 pour plus de renseignements.

L'expression utilisée dans la clause WHERE peut ne faire référence qu'à des colonnes de la table sous-jacente, mais elle peut utiliser toutes les colonnes, pas uniquement celles indexées. Actuellement, les sous-requêtes et les expressions d'agrégats sont aussi interdites dans la clause WHERE. Les mêmes restrictions s'appliquent aux champs d'index qui sont des expressions.

Toutes les fonctions et opérateurs utilisés dans la définition d'index doivent être « immutable » (NDT : immuable), c'est-à-dire que leur résultat ne doit dépendre que de leurs arguments et jamais d'une influence externe (telle que le contenu d'une autre table ou l'heure). Cette restriction permet de s'assurer que le comportement de l'index est strictement défini. Pour utiliser une fonction utilisateur dans une expression d'index ou dans une clause WHERE, cette fonction doit être marquée immutable lors de sa création.

Paramètres

	postgres=# \d tab
	Table "public.tab"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 col    | integer |           |          |
	Indexes:
	"idx" btree (col) INVALID
    

Notes

Chapitre 11 présente des informations sur le moment où les index peuvent être utilisés, quand ils ne le sont pas et dans quelles situations particulières ils peuvent être utiles.

Actuellement, seules les méthodes d'indexation B-tree, GiST, GIN et BRIN supportent les index multi-colonnes. Qu'il puisse y avoir plusieurs colonnes clés est indépendant du fait que des colonnes INCLUDE puissent être ajoutées à l'index. Les index peuvent avoir jusqu'à 32 colonnes, ceci incluant les colonnes INCLUDE.(Cette limite peut être modifiée à la compilation de PostgreSQL.) Seul B-tree supporte actuellement les index uniques.

Une classe d'opérateur with optional parameters peut être spécifiée pour chaque colonne d'un index. La classe d'opérateur identifie les opérateurs à utiliser par l'index pour cette colonne. Par exemple, un index B-tree sur des entiers codés sur quatre octets utilise la classe int4_ops, qui contient des fonctions de comparaison pour les entiers sur quatre octets. En pratique, la classe d'opérateur par défaut pour le type de données de la colonne est généralement suffisant. Les classes d'opérateur trouvent leur intérêt principal dans l'existence, pour certains types de données, de plusieurs ordonnancements significatifs.

Soit l'exemple d'un type de données « nombre complexe » qui doit être classé par sa valeur absolue ou par sa partie réelle. Cela peut être réalisé par la définition de deux classes d'opérateur pour le type de données, puis par la sélection de la classe appropriée lors de la création d'un index.

De plus amples informations sur les classes d'opérateurs sont disponibles dans Section 11.10 et dans Section 38.16.

Quand CREATE INDEX est appelé sur une table partitionnée, le comportement par défaut est de vérifier que toutes les partitions ont un index correspondant. Chaque partition est tout d'abord vérifiée pour déterminer si un index équivalent existe déjà. Si c'est le cas, cet index sera attaché comme index la partition avec l'index en cours de création, qui deviendra son index parent. Si aucun index correspondant n'existe, un nouvel index sera créé et attaché automatiquement. Le nom du nouvel index dans chaque partition sera déterminé comme si aucun nom d'index n'avait été spécifié dans la commande. Si l'option ONLY est indiquée, aucune récursion n'est réalisée et l'index est marqué invalide. (ALTER INDEX ... ATTACH PARTITION marque l'index comme valide une fois que toutes les partitions ont acquis l'index correspondant.) Néanmoins, notez que toute partition créée dans le futur en utilisant CREATE TABLE ... PARTITION OF contiendra automatiquement l'index correspondant que cette option soit spécifiée ou non.

Pour les méthodes d'indexage qui supportent les parcours ordonnés (actuellement seulement pour les B-tree), les clauses optionnelles ASC, DESC, NULLS FIRST et/ou NULLS LAST peuvent être spécifiées pour modifier l'ordre de tri normal de l'index. Comme un index ordonné peut être parcouru en avant et en arrière, il n'est habituellement pas utile de créer un index DESC sur une colonne -- ce tri est déjà disponible avec un index standard. L'intérêt de ces options se révèle avec les index multi-colonnes. Ils peuvent être créés pour correspondre à un tri particulier demandé par une requête, comme SELECT ... ORDER BY x ASC, y DESC. Les options NULLS sont utiles si vous avez besoin de supporter le comportement « nulls sort low », plutôt que le « nulls sort high » par défaut, dans les requêtes qui dépendent des index pour éviter l'étape du tri.

Le système récupère régulièrement des statistiques sur toutes les colonnes d'une table. Les index nouvellement créés et sans expression peuvent immédiatement utiliser ces statistiques pour déterminer l'utilité d'un index. Pour les nouveaux index à expression, il est nécessaire d'exécuter ANALYZE ou d'attendre que le the processus en tâche de fond autovacuum analyse la table pour générer des statistiques pour ces index.

Pour la plupart des méthodes d'indexation, la vitesse de création d'un index est dépendante du paramètre maintenance_work_mem. Une plus grande valeur réduit le temps nécessaire à la création d'index, tant qu'elle ne dépasse pas la quantité de mémoire vraiment disponible, afin d'éviter que la machine ne doive paginer.

PostgreSQL peut construire des index en utilisant plusieurs CPU pour traiter plus rapidement les lignes de la table. Cette fonctionnalité est connue sous le nom de construction d'index parallélisée. Pour les méthodes d'indexage qui supportent la construction d'index en parallèle (actuellement seulement les B-tree), maintenance_work_mem indique la quantité maximale de mémoire pouvant être utilisée pour chaque opération de construction d'index, quelque soit le nombre de processus workers démarrés. Habituellement, un modèle de coût détermine automatiquement le nombre de workers à exécuter.

Les constructions d'index parallélisées pourraient bénéficier d'une augmentation du maintenance_work_mem, là où une construction équivalente mais non parallélisée ne verrait que peu ou pas de bénéfices. Notez que maintenance_work_mem peut influencer le nombre de processus workers demandés car les workers parallélisés doivent avoir au moins 32 Mo provenant du maintenance_work_mem global. Il doit aussi rester 32 Mo pour le processus leader. Augmenter max_parallel_maintenance_workers pourrait permettre l'utilisation d'un plus grand nombre de workers, ce qui réduirait le temps nécessaire pour la création de l'index, à condition que cette création ne soit pas déjà freiné par les disques. Bien sûr, il doit rester suffisamment de CPU qui auraient été autrement inutilisés.

Configurer une valeur pour parallel_workers via ALTER TABLE contrôle directement le nombre de processus workers parallélisés réclamé par un CREATE INDEX sur la table. Ceci contourne complètement le modèle de coût, et empêche maintenance_work_mem d'affcter le nombre demandé de workers parallélisés. Configurer parallel_workers à 0 via ALTER TABLE désactivera les constructions d'index parallélisées sur la table dans tous les cas.

Bien que CREATE INDEX avec l'option CONCURRENTLY accepte les constructions parallélisées sans restrictions particulières, seul le premier parcours de table est réellement exécuté en parallèle.

DROP INDEX est utilisé pour supprimer un index.

Comme pour toute transaction longue, CREATE INDEX sur une table peut affecter les lignes pouvant être supprimées par un VACUUM concurrent sur toute autre table.

Les versions précédentes de PostgreSQL ont aussi une méthode d'index R-tree. Cette méthode a été supprimée car elle n'a pas d'avantages par rapport à la méthode GiST. Si USING rtree est indiqué, CREATE INDEX l'interprétera comme USING gist pour simplifier la conversions des anciennes bases à GiST.

Chaque processus exécutant un CREATE INDEX indiquera sa progression dans la vue pg_stat_progress_create_index. Voir Section 28.4.2 pour les détails.

Exemples

Créer un index B-tree sur la colonne titre dans la table films :

CREATE UNIQUE INDEX title_idx ON films (title);
   

Pour créer un index B-tree unique sur la colonne title avec les colonnes incluses director et rating de la table films :

CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);
   

To create a B-Tree index with deduplication disabled:

CREATE INDEX title_idx ON films (title) WITH (deduplicate_items = off);
   

Pour créer un index sur l'expression lower(titre), permettant une recherche efficace quelque soit la casse :

CREATE INDEX ON films ((lower(titre)));
   

(dans cet exemple, nous avons choisi d'omettre le nom de l'index, donc le système choisira un nom, typiquement films_lower_idx.)

Pour créer un index avec un collationnement spécifique :

CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");
   

Pour créer un index avec un ordre de tri des valeurs NULL différent du standard :

CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);
   

Pour créer un index avec un facteur de remplissage différent :

CREATE UNIQUE INDEX idx_titre ON films (titre) WITH (fillfactor = 70);
   

Pour créer un index GIN avec les mises à jour rapides désactivées :

CREATE INDEX gin_idx ON documents_table USING GIN (locations) WITH (fastupdate = off);
   

Créer un index sur la colonne code de la table films et donner à l'index l'emplacement du tablespace espaceindex :

CREATE INDEX code_idx ON films (code) TABLESPACE espaceindex;
   

Pour créer un index GiST sur un attribut point, de façon à ce que nous puissions utiliser rapidement les opérateurs box sur le résultat de la fonction de conversion :

CREATE INDEX pointloc
    ON points USING gist (box(location,location));
SELECT * FROM points
    WHERE box(location,location) && '(0,0),(1,1)'::box;
   

Pour créer un index sans verrouiller les écritures dans la table :

CREATE INDEX CONCURRENTLY index_quentite_ventes ON table_ventes (quantité);
   

Compatibilité

CREATE INDEX est une extension du langage PostgreSQL. Les index n'existent pas dans le standard SQL.

Voir aussi