index de tables
Cette figure illustre la façon dont sont recherchés des champs indexés par une valeur numérique.
Les fonctions et opérateurs utilisés dans la définition d'index doivent être immutables
, c'est à dire que leur résultat
ne doit dépendre que de leurs arguments et jamais d'une influence externe (par exemple 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.
Il est possible de définir des méthodes d'indexation utilisateur.
La clause WHERE a la même syntaxe que celle utilisée lors d'un SELECT.
La clause UNIQUE entraînera une erreur si l'indexation est appliquée sur des valeurs répétées.
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 ]CREATE INDEX villes_idx ON villes (nom)
WHERE nom like '_____';pour qu'un index puisse être utilisé sur une recherche de type LIKE 'prefixe%' il faudra créer l'index avec l'option text_pattern_ops comme par exemple :
CREATE INDEX villes_idx ON villes (nom text_pattern_ops)
Rendre l'écriture et l'indexation possible augmente considérablement la durée de l'indexation (2 à 3 fois plus long). Cette fonctionnalité sera donc à n'utiliser que s'il est absolument nécessaire de réaliser simultanément les 2 opérations.
L'opération de reindexation n'est à priori nécessaire que dans quelques rares circonstances telle que la réduction significative du nombre de lignes dans une table. Dans ce cas cette opération réduira la taille de cet index au strict nécessaire en éliminant ainsi toute référence sur des lignes mortes.
CREATE INDEX villes_nom_idx ON villes (nom);
CREATE INDEX villes_len_idx ON villes (nom)
WHERE nom like '_____';EXPLAIN SELECT * FROM villes WHERE nom = 'Toulouse';
QUERY PLAN
--------------------------------------------------------------------
Index Scan using villes_nom_idx on villes (cost=0.00..8.27
rows=1 width=50)
Index Cond: ((nom)::text = 'Toulouse'::text)
(2 lignes)Pour la lisibilité il est également possible de forcer l'affichage dans l'un des formats TEXT (par défaut), JSON, XML ou YAML par la directive (FORMAT nom_du_format) comme l'illustre l'exemple suivant :
EXPLAIN (FORMAT JSON) SELECT * FROM villes WHERE nom = 'Toulouse';
QUERY PLAN
------------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Index Scan", +
"Scan Direction": "Forward", +
"Index Name": "villes_nom_idx", +
"Relation Name": "villes", +
"Alias": "villes", +
"Startup Cost": 0.00, +
"Total Cost": 8.27, +
"Plan Rows": 1, +
"Plan Width": 50, +
"Index Cond": "(nom = 'Toulouse'::text)"+
} +
} +
]
(1 ligne)
EXPLAIN SELECT * FROM villes WHERE nom LIKE '_____';
QUERY PLAN
--------------------------------------------------------------------
Bitmap Heap Scan on villes (cost=43.07..435.52 rows=1476 width=50)
Recheck Cond: ((nom)::text ~~ '_____'::text)
-> Bitmap Index Scan on villes_len_idx (cost=0.00..42.71
rows=1476 width=0)
(3 lignes)EXPLAIN SELECT * FROM villes WHERE nom LIKE '__';
QUERY PLAN
-----------------------------------------------------------
Seq Scan on villes (cost=0.00..832.48 rows=383 width=50)
Filter: ((nom)::text ~~ '__'::text)
(2 lignes)On remarque que les requêtes indexées utilisent bien les index comme le fait like '_____' ce qui n'est pas le cas lors de recherche non indexée comme pour l'opérateur like '__'.
Clusteriserune table sur un index
cachede la table à parcourir.
clusterisation.
clusterisationde la table villes :
CLUSTER villes USING villes_nom_idx; CLUSTER
L'opération de clusterisation
n'est possible que sur les index de type BTREE.
\diS+
\diS+ nom_index
\dS+ nom_index
Le principe des index de tables est comparable à celui de l'index présent dans un livre : les termes et concepts fréquemment recherchés par les lecteurs sont listés par ordre alphabétique à la fin du livre. Le lecteur qui recherche un mot particulier peut facilement parcourir l'index, puis aller directement à la page (ou aux pages) indiquée(s). De la même façon que l'auteur doit anticiper les sujets que les lecteurs risquent de rechercher, il est de la responsabilité du programmeur de prévoir les index qui sont utiles.