Description
CREATE INDEX construit un index index_name sur la table spécifiée.
 | Les index sont créés d'abord pour améliorer les performances de la base. Mais un usage inapproprié peut en ralentir les performances. |
Dans la première syntaxe ci-dessus, les champs clé pour l'index sont spécifiés comme noms de colonne. De multiples champs peuvent être spécifiées si la méthode d'accès à l'index supporte les indexes multicolonnes.
Dans la seconde syntaxe, un index est défini sur le résultat d'une fonction utilisateur func_name appliquée à une ou plusieurs colonnes d'une table unique. Ces functional indexes peuvent être utilisés pour obtenir des accès plus rapides aux données basées sur les opérateurs qui nécessitent normalement certaines transformations pour s'appliquer aux données de la base.
PostgreSQL fournit les méthodes d'accès B-tree, R-tree, hash, and GiST access aux indexes. La méthode d'accès B-tree est une implémentation des arborescence-B de haute-concurence de Lehman-Yao. La méthode R-tree implémente le standard arborescence-R de Guttman. La méthode hash est un implémentation du hachage linéaire de Litwin. Nous mentionnons ces algorithmes seulement pour indiquer que ces méthodes sont pleinement dynamiques et n'ont pas à être optimisées périodiquement (comme c'est le cas avec, par exemple, les méthodes d'accès de hachage statique).
Qquand la clause WHERE est présente, un partial index est créé. Un index partiel est un index qui contient des entrées pour une aprtie de la table seulement, habituellement une partie qui est quelque part plus intéressante que le restant de la table. Par exemple, si vous avez une table qui contient des commandes facturées et non facturées où les commandes non facturées prennent une petite partie du total de la table mais qui sont cependant les sections les plus utilisées, vous pouvez améliorer les performances en créant un index sur ces seules sections. Une autre application possible est d'utiliser WHERE avec UNIQUE pour renforcer l'unicité sur un sous-ensemble de la table.
L'expression utilisée dans la clause WHERE peut se référer seulement aux colonnes de la table sous-jacente (mais elle peut utiliser toutes les colonnes, pas seulement celle(s) qui sont indexées). Actuellement, les sous-sélections et les expressions agrégats sont aussi interdites dans WHERE.
Toutes les fonctions et les opérateurs utilisés dans une définition d'index doivent être cachable, leurs résultats doivent dépendre seulement de leurs arguments d'entrée et jamais d'une influence extérieure (comme les contenus d'une autre table ou l'heure actuelle). Cette restriction permet de s'assurer que le comportement de l'index est correctement défini. Pour se servir d'une fonction utilisateur dans un index, souvenez vous de noter la fonction comme cachable quand vous la créez.
Utilisez DROP INDEX pour supprimer un index.
Notes
L'optimiseur de requête PostgreSQL considérera qu'il utilise un index B-tree chaque fois qu'un attribut indexé est inséré dans une comparaison utilisant un des : <, <=, =, >=, >
L'optimiseur de requête PostgreSQL considérera qu'il utilise un index R-tree chaque fois qu'un attribut indexé est inséré dans une comparaison utilisant un des : <<, &<, &>, >>, @, ~=, &&
L'optimiseur de requête PostgreSQL considérera qu'il utilise un index de hachage chaque fois qu'un attribut indexé est inséré dans une comparaison qui utilise l'opérateur =.
Couramment, seules les méthodes d'accès B-tree et gist supportent les indexes multi-colonnes. Plus de 16 clés doivent être spécifiées par défaut (cette limite peut être modifiée lors de la compilation de PostgreSQL). Seul B-tree supporte actuellement les indexes uniques.
Une classe opérateur peut être spécifiée pour chaque colonne d'un index.. La classe opérateur identifie les opérateurs que l'index utilisera pour cette colonne. Par exemple, un index B-tree sur des entiers de quatre-octets utilisera la classe int4_ops; cette classe opérateur inclut des fonctions de comparaison pour les entiers de quatre-octets. En pratique la classe opérateur par défaut pour le type du champ est habituellement suffisant. Le point principal pour avoir des classes opérateur est que pour certains types, ce peut être d'avantage qu'un moyen d'ordonnancement. Par exemple, nous voulons trier un type nombre complexe soit par sa valeur absolue soit par sa partie réelle. Nous pouvons faire ceci en définissant deux classes opérateur pour le type et en sélectionnant la classe propre en faisant un index. Il y a aussi quelques classes opérateur avec un but spécial :
Les classes opérateur box_ops et bigbox_ops supportent toutes les deux les indexes R-tree sur le type box. La différence entre elles est que bigbox_ops réduit les coordonnées de la boîte, pour éviter les exceptions décimales en faisant des multiplications, additions, ou soustractions sur de très grosses coordonnées décimales. (Note: ceci était vrai il y a quelques temps, mais actuellement les deux classes opérateur utilisent les décimales et sont effectivement identiques).
La requête suivante indique toutes les classes opérateur définies :
SELECT am.amname AS acc_method,
opc.opcname AS ops_name,
opr.oprname AS ops_comp
FROM pg_am am, pg_opclass opc, pg_amop amop, pg_operator opr
WHERE opc.opcamid = am.oid AND
amop.amopclaid = opc.oid AND
amop.amopopr = opr.oid
ORDER BY acc_method, ops_name, ops_comp;
|