PostgreSQLLa base de données la plus sophistiquée au monde.

CREATE INDEX

CREATE INDEX — Définir un nouvel index

Synopsis

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ nom ] ON table [ USING méthode ]
    ( { colonne | ( expression ) } [ classeop ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( parametre_stockage = valeur [, ... ] ) ]
    [ TABLESPACE espacelogique ]
    [ WHERE prédicat ]

Description

CREATE INDEX construit un index sur le (ou les) colonne(s) spécifiée(s) de la table spécifié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) et GIN. 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, « Index partiels » 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

UNIQUE

Le système vérifie la présence de valeurs dupliquées dans la table à la création de l'index (si des données existent déjà) et à chaque fois qu'une donnée est ajoutée. Les tentatives d'insertion ou de mises à jour qui résultent en des entrées dupliquées engendrent une erreur.

CONCURRENTLY

Quand cette option est utilisée, PostgreSQL™ construira l'index sans prendre de verrous qui bloquent les insertions, mises à jour, suppression en parallèle sur cette table ; la construction d'un index standard verrouille les écritures (mais pas les lectures) sur la table jusqu'à la fin de la construction. Il est nécessaire d'avoir quelques connaissances avant d'utiliser cette option -- voir la section intitulée « Construire des index en parallèle ».

nom

Le nom de l'index à créer. Aucun nom de schéma ne peut être inclus ici ; l'index est toujours créé dans le même schéma que sa table parent. Si le nom est omis, PostgreSQL™ choisit un nom convenable basé sur le nom de la table parent et celui des colonnes indexées.

table

Le nom de la table à indexer (éventuellement qualifié du nom du schéma).

méthode

Le nom de la méthode à utiliser pour l'index. Les choix sont btree, hash, gist et gin. La méthode par défaut est btree.

colonne

Le nom d'une colonne de la table.

expression

Une expression basée sur une ou plusieurs colonnes de la table. L'expression doit habituellement être écrite entre parenthèses, comme la syntaxe le précise. Néanmoins, les parenthèses peuvent être omises si l'expression a la forme d'un appel de fonction.

classeop

Le nom d'une classe d'opérateur. Voir plus bas pour les détails.

ASC

Spécifie un ordre de tri ascendant (valeur par défaut).

DESC

Spécifie un ordre de tri descendant.

NULLS FIRST

Spécifie que les valeurs NULL sont présentées avant les valeurs non NULL. Ceci est la valeur par défaut quand DESC est indiqué.

NULLS LAST

Spécifie que les valeurs NULL sont présentées après les valeurs non NULL. Ceci est la valeur par défaut quand ASC est indiqué.

paramètre_stockage

Le nom d'un paramètre de stockage spécifique à la méthode d'indexage. Voir Paramètres de stockage des index pour les détails.

espacelogique

Le tablespace dans lequel créer l'index. S'il n'est pas précisé, default_tablespace est consulté, sauf si la table est temporaire auquel cas temp_tablespaces est utilisé.

prédicat

L'expression de la contrainte pour un index partiel.

Paramètres de stockage des index

La clause WITH optionnelle spécifie des paramètres de stockage pour l'index. Chaque méthode d'indexage peut avoir son propre ensemble de paramètres de stockage. Les méthodes d'indexage B-tree, hash et GiST acceptent toutes un seul paramètre :

FILLFACTOR

Le facteur de remplissage pour un index est un pourcentage qui détermine à quel point les pages d'index seront remplies par la méthode d'indexage. Pour les B-tree, les pages enfants sont remplies jusqu'à ce pourcentage lors de la construction initiale de l'index, et aussi lors de l'extension de l'index sur la droite (ajoutant les valeurs de clé les plus importantes). Si les pages deviennent ensuite totalement remplies, elles seront partagées, amenant une dégradation graduelle de l'efficacité de l'index. Les arbres B-tree utilisent un facteur de remplissage de 90% par défaut mais toute valeur entière comprise entre 10 et 100 peut être choisie. Si la table est statique, alors un facteur de 100 est meilleur pour minimiser la taille physique de l'index. Pour les tables mises à jour régulièrement, un facteur de remplissage plus petit est meilleur pour minimiser le besoin de pages divisées. Les autres méthodes d'indexage utilisent un facteur de remplissage de façon différente mais en gros analogue ; le facteur de remplissage varie suivant les méthodes.

Les index GIN acceptent un paramètre supplémentaire :

FASTUPDATE

Ce paramètre régit l'utilisation de la technique de mise à jour rapide décrite dans Section 53.3.1, « Technique GIN de mise à jour rapide ». C'est un paramètre booléen : ON active la mise à jour rapide, OFF la désactive. (Les autres façons d'écrire ON et OFF sont autorisées, comme décrit dans Section 18.1, « Paramètres de configuration ».) La valeur par défaut est ON.

[Note]

Note

Désactiver FASTUPDATE via ALTER INDEX empêche les insertions futures d'aller dans la liste d'entrées d'index à traiter, mais ne nettoie pas les entrées précédentes de cette liste. Vous voudrez peut être ensuite exécuter un VACUUM sur la table, afin de garantir que la liste à traiter soit vidée.

Construire des index en parallèle

Créer un index peut interférer avec les opérations normales d'une base de données. Habituellement, PostgreSQL™ verrouille la table à indexer pour la protéger des écritures et construit l'index complet avec un seul parcours de la table. Les autres transactions peuvent toujours lire la table mais s'ils essaient d'insérer, mettre à jour, supprimer des lignes dans la table, elles seront bloquées jusqu'à la fin de la construction de l'index. Ceci peut avoir un effet sérieux si le système est une base en production. Les très grosses tables peuvent demander plusieurs heures pour être indexées. Même pour les petites tables, une construction d'index peut bloquer les processus qui voudraient écrire dans la table pendant des périodes longues sur un système de production.

PostgreSQL™ supporte la construction des index sans verrouillage des écritures. Cette méthode est appelée en précisant l'option CONCURRENTLY de CREATE INDEX. Quand cette option est utilisée, PostgreSQL™ doit réaliser deux parcours de table et, en plus, il doit attendre que toutes les transactions existantes qui peuvent utiliser cet index se terminent. Du coup, cette méthode requiert plus de temps qu'une construction standard de l'index et est bien plus longue à se terminer. Néanmoins, comme cela autorise la poursuite des opérations pendant la construction de l'index, cette méthode est utile pour ajouter de nouveaux index dans un environnement en production. Bien sûr, la charge CPU et I/O supplémentaire imposée par la création de l'index peut ralentir les autres opérations.

Dans la construction en parallèle d'un index, l'index est enregistré dans le catalogue système dans une transaction, puis les deux parcours de table interviennent dans une deuxième puis une troisième transaction. Toutes les transactions actives au moment du lancement du deuxième parcours de la table, pas seulement celles qui ont déjà utilisées la table, ont le potentiel de bloquer la création en parallèle de l'index jusqu'à leur fin. Lors de la vérification des transactions qui pourraient toujours utiliser l'index original, la création en parallèle de l'index avance au travers des transactions plus anciennes potentiellement dangereuses, les vérifiant une par une, en obtenant des verrous partagés sur leur identifiant virtuel de transaction pour attendre leur fin. Si un problème survient lors du parcours de la table, comme une violation d'unicité dans un index unique, la commande CREATE INDEX échouera mais laissera derrière un index « invalide ». Cet index sera ignoré par les requêtes car il pourrait être incomplet ; néanmoins il consommera quand même du temps lors des mises à jour de l'index. La commande \d de psql rapportera cet index comme INVALID :

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

La méthode de récupération recommandée dans de tels cas est de supprimer l'index et de tenter de nouveau un CREATE INDEX CONCURRENTLY. (Une autre possibilité est de reconstruire l'index avec REINDEX. Néanmoins, comme REINDEX ne supporte pas la construction d'index en parallèle, cette option ne semble pas très attirante.)

Lors de la construction d'un index unique en parallèle, la contrainte d'unicité est déjà placée pour les autres transactions quand le deuxième parcours de table commence. Cela signifie que des violations de contraintes pourraient être rapportées dans les autres requêtes avant que l'index ne soit disponible, voire même dans des cas où la construction de l'index va échouer. De plus, si un échec survient dans le deuxième parcours, l'index « invalide » continue à forcer la contrainte d'unicité.

Les constructions en parallèle d'index avec expression et d'index partiels sont supportées. Les erreurs survenant pendant l'évaluation de ces expressions pourraient causer un comportement similaire à celui décrit ci-dessus pour les violations de contraintes d'unicité.

Les constructions d'index standards permettent d'autres construction d'index en parallèle sur la même table mais seul une construction d'index en parallèle peut survenir sur une table à un même moment. Dans les deux cas, aucun autre type de modification de schéma n'est autorisé sur la table. Une autre différence est qu'une commande CREATE INDEX normale peut être réalisée à l'intérieur d'un bloc de transactions mais CREATE INDEX CONCURRENTLY ne le peut pas.

Notes

Chapitre 11, Index 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 et GIN supportent les index multi-colonnes. Jusqu'à 32 champs peuvent être spécifiés par défaut. (Cette limite peut être modifiée à la compilation de PostgreSQL™.) Seul B-tree supporte actuellement les index uniques.

Une classe d'opérateur 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.9, « Classes et familles d'opérateurs » et dans Section 35.14, « Interfacer des extensions d'index ».

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.

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. Pour les index de type hash, la valeur de effective_cache_size est aussi importante pour le temps de création de l'index : PostgreSQL™ utilisera une des deux méthodes de création d'index hash disponibles selon que la taille estimée de l'index à créer est supérieure ou inférieure à effective_cache_size. Pour obtenir les meilleurs résultats, assurez-vous que ce paramètre est aussi positionné à quelque chose qui reflète la quantité de mémoire disponible, et soyez attentif à ce que la somme de maintenance_work_mem et effective_cache_size soit inférieure à la quantité de mémoire disponible de la machine pour PostgreSQL™ (en prenant en compte les autres programmes en cours d'exécution sur la machine).

DROP INDEX(7) est utilisé pour supprimer un index.

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.

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 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.)

[Attention]

Attention

Les opérations sur les index hash ne sont pas enregistrées dans les journaux de transactions. Du coup, les index hash doivent être reconstruit avec REINDEX après un arrêt brutal de la base de données si des modifications n'ont pas été écrites. De plus, les modifications dans les index hash ne sont pas répliquées avec la réplication Warm Standby après la sauvegarde de base initiale, donc ces index donneront de mauvaises réponses aux requêtes qui les utilisent. Pour ces raisons, l'utilisation des index hash est actuellement déconseillée.

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.