CREATE INDEX — Définir un nouvel index
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ]nom
] ON [ ONLY ]nom_table
[ USINGméthode
] ( {nom_colonne
| (expression
) } [ COLLATEcollation
] [classeop
[ (parametre_classeop
=valeur
[, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) [ INCLUDE (nom_colonne
[, ...] ) ] [ NULLS [ NOT ] DISTINCT ] [ WITH (parametre_stockage
=valeur
[, ... ] ) ] [ TABLESPACEnom_espacelogique
] [ WHEREprédicat
]
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.
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.
Des restrictions supplémentaires s'appliquent quand des index uniques sont appliquées aux tables partitionnées. Voir CREATE TABLE.
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 Construire des index en parallèle.
Pour les tables temporaires, CREATE INDEX
est toujours
non concurrent car aucune autre session n'y a accès, et la création d'index
non concurrent est moins coûteuse.
IF NOT EXISTS
Ne renvoie pas une erreur si une relation existe avec le même nom. Un
message est renvoyé dans ce cas. Notez qu'il n'existe pas de garantie
que l'index existant ressemble à celui qui aurait été créé. Le nom
d'index est requis quand IF NOT EXISTS
est spécifié.
INCLUDE
La clause optionnelle INCLUDE
indique une liste de
colonnes qui seront incluses dans l'index comme des colonnes
non clés. Une colonne non clé ne peut pas être
utilisée dans la qualification d'une recherche par parcours d'index, et
elle est ignorée pour la contrainte d'unicité ou d'exclusion assurée par
l'index. Néanmoins, un parcours d'index couvrant peut renvoyer le
contenu des colonnes non clés sans avoir à visiter la table de l'index
car il est directement disponible dans l'index. De façon, l'ajout de
colonnes non clés autorise l'utilisation de parcours d'index couvrants
pour les requêtes qui, autrement, ne les auraient pas utilisés.
Il est conseillé de rester prudent sur l'ajout de colonnes non clés dans un index, tout spécialement pour les colonnes larges. Si un enregistrement d'un index dépasse la taille maximale autorisée pour le type de l'index l'insertion de données échouera. Dans tous les cas, les colonnes non clés dupliquent les données de la table et augmentent la taille de l'index, ralentissant potentiellement les recherches. Furthermore, B-tree deduplication is never used with indexes that have a non-key column.
Les colonnes listées dans la clause INCLUDE
n'ont pas
besoin de classes d'opérateur appropriées. La clause peut contenir les
colonnes dont les types de données n'ont pas de classes d'opérateur
définis pour une méthode d'accès donnée.
Les expressions ne sont pas supportées comme colonnes incluses car elles ne peuvent pas être utilisées dans des parcours d'index couvrants.
Actuellement, seules les méthodes d'accès B-tree, GiST et SP-GiST tirent
parti de cette fonctionnalité.
Pour ces index, les valeurs des colonnes listées dans la clause
INCLUDE
sont incluses dans les enregistrements feuilles qui
correspondent à des enregistrements de lignes de table, mais ne sont pas incluses
dans les enregistrements de plus haut niveau.
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. Le nom de l'index doit être distinct du nom de toute autre relation (table, séquence, index, vue, vue matérialisée, ou table distante) pour ce schéma. Si le nom est omis, PostgreSQL choisit un nom convenable basé sur le nom de la table parent et celui des colonnes indexées.
ONLY
Indique de ne pas faire de récursion pour la création des index sur les partitions si la table est partitionnée. Par défaut, la récursion a lieu.
nom_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
, spgist
,
gin
et brin
.
ou les méthodes d'accès installées par l'utilisateur comme
bloom.
La méthode par défaut est btree
.
nom_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.
collation
Le nom du collationnement à utiliser pour l'index. Par défaut, l'index utilise le collationnement déclaré pour la colonne à indexer ou le collationnement résultant de l'expression à indexer. Les index avec des collationnements spécifiques peuvent être utiles pour les requêtes qui impliquent des expressions utilisant des collationnements spécifiques.
classeop
Le nom d'une classe d'opérateur. Voir plus bas pour les détails.
opclass_parameter
The name of an operator class parameter. See below for details.
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é.
NULLS DISTINCT
NULLS NOT DISTINCT
Spécifique pour un index d'unicité si les valeurs NULL doivent être considérées comme distinctes (différentes). Par défaut, elles sont considérées distinctes, pour qu'un index d'unicité permette de stocker plusieurs valeurs NULL dans la colonne associée.
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.
nom_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.
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'index B-tree, hash, GiST et SP-GiST acceptent toutes ce
paramètre :
fillfactor
(integer
)
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 fragmentation de la structure sur disque 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.
Les index B-tree sur des tables où de nombreuses insertions et/ou mises
à jour sont prévues peuvent bénéficier d'un facteur de remplissage
plus bas lors du CREATE INDEX
(suivant le
chargement en masse dans la table). Les valeurs dans l'intervalle 50 -
90 peut utilement « diminuer » le taux
de division de blocs au début de la vie de l'index B-tree
(baisser ainsi le facteur de remplissage peut même diminuer le nombre
absolu de divisions de blocs, bien que cet effet est fortement
dépendant de la charge de travail). La technique de suppression de
l'index B-Tree du bas vers le haut décrite dans Section 67.4.2 est dépendent sur la place
« supplémentaire » disponible dans les blocs pour des
versions « supplémentaires » de lignes, et ainsi peut être
affectée par le facteur de remplissage (bien que l'effet n'est
habituellement pas significatif).
Dans les autres cas spécifiques, il pourrait être utile d'augmenter le
facteur de remplissage à 100 au moment du CREATE
INDEX
comme moyen de maximiser l'utilisation de l'espace.
Vous devez seulement le considérer quand vous êtes complètement sûr
que la table est statique(autrement dit qu'elle ne sera jamais
affectée par des insertions ou des mises à jour). Une configuration du
facteur de remplissage à 100 risque autrement de
baisser les performances : même un petit
nombre de mises à jour ou d'insertions peut causer une soudaine
explosion des divisions de blocs.
Les autres méthodes d'index utilisent le facteur de remplissage de façon différente mais grossièrement identique ; le facteur de remplissage par défaut varie suivant les méthodes.
Les index B-tree acceptent aussi ce paramètre :
deduplicate_items
(boolean
)
Controls usage of the B-tree deduplication technique described
in Section 67.4.3. Set to
ON
or OFF
to enable or
disable the optimization. (Alternative spellings of
ON
and OFF
are allowed as
described in Section 20.1.) The default is
ON
.
Turning deduplicate_items
off via
ALTER INDEX
prevents future insertions from
triggering deduplication, but does not in itself make existing
posting list tuples use the standard tuple representation.
Les index GiST acceptent en option ce paramètre :
buffering
(enum
)
Détermine si la technique de construction par tampon décrite dans Section 68.4.1 est utilisé pour construire l'index.
À OFF
, cette technique est désactivée. À
ON
, elle est activée. À AUTO
,
elle est initialement désactivée mais peut être activée quand la
taille de l'index atteint effective_cache_size.
La valeur par défaut est AUTO
. Notez que si la
construction triée est possible, elle sera utilisée à la place de la
construction par tampon à moins que buffering=ON
ne
soit spécifié.
Les index GIN acceptent plusieurs paramètres supplémentaires :
fastupdate
(boolean
)
Ce paramètre régit l'utilisation de la technique de mise à jour rapide
décrite dans Section 70.4.1. C'est un paramètre
booléen : ON
active la mise à jour rapide,
OFF
la désactive. La valeur par défaut est
ON
.
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 ou exécuter la fonction
gin_clean_pending_list
, afin de garantir que
la liste à traiter soit vidée.
gin_pending_list_limit
(integer
)
Personnalise le paramètre gin_pending_list_limit. Cette valeur est spécifiée en ko.
Les index BRIN acceptent différents paramètres :
pages_per_range
(integer
)
Définit le nombre de blocs de table qui sera résumé en un intervalle de
blocs pour chaque entrée dans un index BRIN (voir
Section 71.1 pour plus de détails). La valeur par
défaut est 128
.
autosummarize
(boolean
)
Définit si le lancement d'un calcul de résumé doit être mis en queue pour
l'intervalle de blocs précédent chaque fois qu'une insertion est détectée
sur l'intervalle suivant.
Voir Section 71.1.1 pour plus de détails.
La valeur par défaut est off
.
Les index GiST acceptent en plus ce paramètre :
buffering
Détermine si la technique de construction avec tampons décrite dans
Section 68.4.1 est utilisée pour construire l'index.
À OFF
, cette technique n'est pas utilisée. À
ON
, elle est utilisée. À AUTO
, elle
est au départ désactivée mais elle est activée une fois que la taille de
l'index atteint effective_cache_size. La valeur
par défaut est AUTO
.
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 modifier ou 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é sous
la forme d'un index « invalide » dans
les catalogues systèmes dans une transaction, puis les deux parcours de
table interviennent dans deux transactions supplémentaires. Avant chaque
parcours de table, la construction de l'index doit attendre la fin des
transactions en cours qui ont modifié la table. Après le deuxième
parcours, la construction doit attendre la fin de toute transactions
ayant une image de base (un snapshot, voir Chapitre 13) datant
d'avant le deuxième parcours pour se terminer, ceci incluant les
transactions utilisées par toute phase des constructions concurrentes
d'index sur les autres tables, si les index impliqués sont partiels ou
ont des colonnes qui ne sont pas des références de colonne simple.
Ensuite, l'index peut être marqué comme « valide » et utilisable,
et la commande
CREATE INDEX
se termine. Néanmoins, même après cela,
l'index pourrait ne pas être immédiatement utilisable pour les autres
requêtes : dans le pire des cas, il ne peut pas être utilisé tant
que des transactions datant d'avant le début de la création de l'index
existent.
Si un problème survient lors du parcours de la table, comme un deadlock ou
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 | Collation | Nullable | Default --------+---------+-----------+----------+--------- 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 CONCURRENTLY
.)
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 constructions
d'index en simultanée 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, la modification du schéma de la table n'est pas autorisé pendant la
construction de l'index. Une autre différence est qu'une commande
CREATE INDEX
normale peut être réalisée à l'intérieur
d'un bloc de transactions alors que CREATE INDEX
CONCURRENTLY
ne le peut pas.
Les constructions en parallèle des index sur les tables partitionnées ne sont pas actuellement supportées. Néanmoins, vous pouvez construire l'index en parallèle sur chaque partition individuel, puis créer l'index partitionné sans CONCURRENTLY pour réduire le temps où les écritures seront bloquées sur la table partitionnée. Dans ce cas, construire l'index partitionné est une opération sur les méta-données uniquement.
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.
Vous pourriez vouloir réinitialiser parallel_workers
après l'avoir configuré pour permettre une construction d'index. Ceci
évite des changements inattendus dans les plans de requêtes, vu que
parallel_workers
affecte tous les
parcours parallélisés de table.
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.
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é);
CREATE INDEX
est une extension du langage
PostgreSQL. Les index n'existent pas dans le
standard SQL.