Les procédures décrites jusqu'à maintenant permettent de définir de nouveaux types, de nouvelles fonctions et de nouveaux opérateurs. Néanmoins, nous ne pouvons pas encore définir un index sur une colonne d'un nouveau type de données. Pour cela, nous devons définir une classe d'opérateur pour le nouveau type de données. Plus loin dans cette section, nous illustrerons ce concept avec un exemple : une nouvelle classe d'opérateur pour la méthode d'indexation B-tree qui enregistre et trie des nombres complexes dans l'ordre ascendant des valeurs absolues.
Les classes d'opérateur peuvent être groupées en familles d'opérateur pour afficher les relations entre classes compatibles sémantiquement. Quand un seul type de données est impliqué, une classe d'opérateur est suffisant, donc nous allons nous fixer sur ce cas en premier puis retourner aux familles d'opérateur.
La table pg_am
contient une ligne pour chaque méthode
d'indexation (connue en interne comme méthode d'accès). Le support pour l'accès
normal aux tables est implémenté dans PostgreSQL
mais toutes les méthodes d'index sont décrites dans
pg_am
. Il est possible d'ajouter une nouvelle méthode
d'accès aux index en écrivant le code nécessaire et en ajoutant
ensuite une ligne dans la table pg_am
-- mais ceci
est au-delà du sujet de ce chapitre (voir le Chapitre 64).
Les routines pour une méthode d'indexation n'ont pas à connaître directement
les types de données sur lesquels opère la méthode d'indexation. Au lieu de
cela, une classe d'opérateur identifie l'ensemble d'opérations
que la méthode d'indexation doit utiliser pour fonctionner avec un type
particulier de données. Les classes d'opérateurs sont ainsi dénommées parce
qu'une de leur tâche est de spécifier l'ensemble des opérateurs de la clause
WHERE
utilisables avec un index (c'est-à-dire, qui peuvent être
requalifiés en balayage d'index). Une classe d'opérateur peut également
spécifier des fonctions de support, nécessaires pour les
opérations internes de la méthode d'indexation mais sans correspondance
directe avec un quelconque opérateur de clause WHERE
pouvant être
utilisé avec l'index.
Il est possible de définir plusieurs classes d'opérateurs pour le même type de données et la même méthode d'indexation. Ainsi, de multiples ensembles de sémantiques d'indexation peuvent être définis pour un seul type de données. Par exemple, un index B-tree exige qu'un tri ordonné soit défini pour chaque type de données auquel il peut s'appliquer. Il peut être utile pour un type de donnée de nombre complexe de disposer d'une classe d'opérateur B-tree qui trie les données selon la valeur absolue complexe, une autre selon la partie réelle, etc. Typiquement, une des classes d'opérateur sera considérée comme plus utile et sera marquée comme l'opérateur par défaut pour ce type de données et cette méthode d'indexation.
Le même nom de classe d'opérateur peut être utilisé pour plusieurs méthodes
d'indexation différentes (par exemple, les méthodes d'index B-tree et hash ont
toutes les deux des classes d'opérateur nommées int4_ops
)
mais chacune de ces classes est une entité indépendante et doit être définie
séparément.
Les opérateurs associés à une classe d'opérateur sont identifiés par des
« numéros de stratégie », servant à identifier la sémantique de
chaque opérateur dans le contexte de sa classe d'opérateur. Par exemple, les
B-trees imposent un classement strict selon les clés, du plus petit au plus
grand. Ainsi, des opérateurs comme « plus petit que » et « plus
grand que » sont intéressants pour un B-tree. Comme
PostgreSQL permet à l'utilisateur de définir des
opérateurs, PostgreSQL ne peut pas rechercher le
nom d'un opérateur (par exemple, <
ou >=
) et
rapporter de quelle comparaison il s'agit. Au lieu de cela, la méthode
d'indexation définit un ensemble de « stratégies », qui peuvent être
comprises comme des opérateurs généralisés. Chaque classe d'opérateur
spécifie l'opérateur effectif correspondant à chaque stratégie pour un type
de donnée particulier et pour une interprétation de la sémantique d'index.
La méthode d'indexation B-tree définit cinq stratégies, qui sont exposées dans le Tableau 38.3.
Tableau 38.3. Stratégies B-tree
Opération | Numéro de stratégie |
---|---|
plus petit que | 1 |
plus petit ou égal | 2 |
égal | 3 |
plus grand ou égal | 4 |
plus grand que | 5 |
Les index de découpage permettent seulement des comparaisons d'égalité et utilisent ainsi une seule stratégie exposée dans le Tableau 38.4.
Tableau 38.4. Stratégies de découpage
Opération | Numéro de stratégie |
---|---|
égal à | 1 |
Les index GiST sont plus flexibles : ils n'ont pas du tout un ensemble fixe de stratégies. À la place, la routine de support de « cohérence » de chaque classe d'opérateur GiST interprète les numéros de stratégie comme elle l'entend. Comme exemple, plusieurs des classes d'opérateurs GiST indexe les objets géométriques à deux dimensions fournissant les stratégies « R-tree » affichées dans Tableau 38.5. Quatre d'entre elles sont des vrais tests à deux dimensions (surcharge, identique, contient, contenu par) ; quatre autres considèrent seulement la direction X ; et les quatre dernières fournissent les mêmes tests dans la direction Y.
Tableau 38.5. Stratégies « R-tree » pour GiST à deux dimensions
Opération | Numéro de stratégie |
---|---|
strictement à gauche de | 1 |
ne s'étend pas à droite de | 2 |
surcharge | 3 |
ne s'étend pas à gauche de | 4 |
strictement à droite de | 5 |
identique | 6 |
contient | 7 |
contenu par | 8 |
ne s'étend pas au dessus | 9 |
strictement en dessous | 10 |
strictement au dessus | 11 |
ne s'étend pas en dessous | 12 |
Les index SP-GiST sont similaires aux index GiST en flexibilité : ils n'ont pas un ensemble fixe de stratégie. À la place, les routines de support de chaque classe d'opérateur interprètent les numéros de stratégie suivant la définition du classe d'opérateur. Comme exemple, les numéros des stratégies utilisés par les classes d'opérateur sur des points sont affichés dans Tableau 38.6.
Tableau 38.6. Stratégies point SP-GiST
Opération | Numéro de stratégie |
---|---|
strictement à gauche | 1 |
strictement à droite | 5 |
identique | 6 |
contenu par | 8 |
strictement en dessous | 10 |
strictement au dessus | 11 |
Les index GIN sont similaires aux index GiST et SP-GiST, dans le fait qu'ils n'ont pas d'ensemble fixé de stratégies. À la place, les routines support de chaque opérateur de classe interprètent les numéros de stratégie suivant la définition de la classe d'opérateur. Comme exemple, les numéros de stratégie utilisés par la classe d'opérateur interne pour les tableaux sont affichés dans Tableau 38.7.
Tableau 38.7. Stratégies des tableaux GIN
Opération | Numéro de stratégie |
---|---|
surcharge | 1 |
contient | 2 |
est contenu par | 3 |
identique | 4 |
Les index BRIN sont similaires aux index GiST, SP-GiST et GIN dans le fait
qu'ils n'ont pas un ensemble fixe de stratégies. À la place, les routines de
support de chaque classe d'opérateur interprètent les numéros de stratégie
suivant la définition de la classe d'opérateur. Par exemple, les numéros de
stratégie utilisés par les classes d'opérateur Minmax
sont indiqués dans Tableau 38.8.
Tableau 38.8. Stratégies MinMax pour BRIN
Opération | Numéro de stratégie |
---|---|
inférieur | 1 |
inférieur ou égal | 2 |
égal | 3 |
supérieur ou égal | 4 |
supérieur | 5 |
Notez que tous les opérateurs ci-dessus renvoient des valeurs de type
booléen. Dans la pratique, tous les opérateurs définis comme index method
search operators doivent renvoyer un type boolean
puisqu'ils
doivent apparaître au plus haut niveau d'une clause WHERE
pour
être utilisés avec un index.
(Certaines méthodes d'accès aux index prennent également en charge les
opérateurs de tri, qui ne renvoient généralement pas
de valeurs booléennes ; cette fonctionnalité est discutée dans
Section 38.16.7.)
Généralement, les stratégies n'apportent pas assez d'informations au système pour indiquer comment utiliser un index. Dans la pratique, les méthodes d'indexation demandent des routines d'appui additionnelles pour fonctionner. Par exemple, les méthodes d'index B-tree doivent être capables de comparer deux clés et de déterminer laquelle est supérieure, égale ou inférieure à l'autre. De la même façon, la méthode d'indexation hash doit être capable de calculer les codes de hachage pour les valeurs de clés. Ces opérations ne correspondent pas à des opérateurs utilisés dans les commandes SQL ; ce sont des routines administratives utilisées en interne par des méthodes d'index.
Comme pour les stratégies, la classe d'opérateur énumère les fonctions spécifiques et le rôle qu'elles doivent jouer pour un type de donnée donné et une interprétation sémantique donnée. La méthode d'indexation définit l'ensemble des fonctions dont elle a besoin et la classe d'opérateur identifie les fonctions exactes à utiliser en les assignant aux « numéros de fonction d'appui » spécifiés par la méthode d'indexage.
De plus, certaines classes d'opérateur autorisent les utilisateurs à
indiquer des paramètres pour contrôler leur comportement. Chaque méthode
native d'accès aux index a une fonction de support optionnelle appelée
options
, qui définit un ensemble de paramètres
spécifiques à la classe d'opérateur.
Les B-trees requièrent une fonction support de comparaison et permettent quatre fonctions support supplémentaires à fournir comme option de la classe d'opérateur, comme indiqué dans Tableau 38.9. Les prérequis pour ces fonctions support sont expliqués en détails dans Section 67.3.
Tableau 38.9. Fonctions d'appui de B-tree
Fonction | Numéro d'appui |
---|---|
Comparer deux clés et renvoyer un entier inférieur à zéro, zéro ou supérieure à zéro indiquant si la première clé est inférieure, égale ou supérieure à la deuxième. | 1 |
Renvoyer les adresses des fonctions de support de tri, appelables en C (optionnel) | 2 |
Comparer une valeur test à une valeur de base plus/moins un décalage, et renvoyer true ou false suivant le résultat de la comparaison (optionnel) | 3 |
Détermine s'il est sûr que les index utilisant la classe d'opérateur s'appliquent à l'optimisation de déduplication des Btree (optionnel) | 4 |
Définit des options spécifiques à cette classe d'opérateur (optionnel) | 5 |
Les index hash requièrent une fonction d'appui, et permettent deux fonctions supplémentaires à fournir à la classe d'opérateur, comme indiqué dans Tableau 38.10.
Tableau 38.10. Fonctions d'appui pour découpage
Fonction | Numéro d'appui |
---|---|
Calculer la valeur de hachage 32 bits pour une clé | 1 |
Calcule la valeur de hachage sur 64 bits d'une clé pour un sel de 64 bits donné ; si le sel vaut 0, les 32 bits de poids faible du résultat doivent correspondre à la valeur qui aurait été calculée par la fonction 1 (facultative) | 2 |
Définit des options spécifiques à cette classe d'opérateur (optionnel) | 3 |
Les index GiST ont onze fonctions d'appui, dont six facultatives, exposées dans le Tableau 38.11. (Pour plus d'informations, voir Chapitre 68.)
Tableau 38.11. Fonctions d'appui pour GiST
Fonction | Description | Numéro d'appui |
---|---|---|
consistent | détermine si la clé satisfait le qualifiant de la requête (variante Booléenne) (facultatif si la fonction d'appui 6 est présente) | 1 |
union | calcule l'union d'un ensemble de clés | 2 |
compress | calcule une représentation compressée d'une clé ou d'une valeur à indexer (optionnelle) | 3 |
decompress | calcule une représentation décompressée d'une clé compressée (optionnelle) | 4 |
penalty | calcule la pénalité pour l'insertion d'une nouvelle clé dans un sous-arbre avec la clé du sous-arbre indiqué | 5 |
picksplit | détermine les entrées d'une page qui sont à déplacer vers la nouvelle page et calcule les clés d'union pour les pages résultantes | 6 |
same | compare deux clés et renvoie true si elles sont identiques | 7 |
distance | détermine la distance de la clé à la valeur de la requête (optionnel) | 8 |
fetch | calcule la représentation originale d'une clé compressée pour les parcours d'index seul (optionnel) | 9 |
options | Définit des options spécifiques à cette classe d'opérateur (optionnel) | 10 |
sortsupport | fournit un comparateur utilisé pour la construction rapide d'index (optionnelle) | 11 |
Les index SP-GiST ont six fonctions d'appui, dont une facultative, comme indiquées dans Tableau 38.12. (Pour plus d'informations, voir Chapitre 69.)
Tableau 38.12. Fonctions de support SP-GiST
Fonction | Description | Numéro de support |
---|---|---|
config | fournit des informations basiques sur la classe d'opérateur | 1 |
choose | détermine comment insérer une nouvelle valeur dans une ligne interne | 2 |
picksplit | détermine comment partitionner un ensemble de valeurs | 3 |
inner_consistent | détermine la sous-partition à rechercher pour une requête | 4 |
leaf_consistent | détermine si la clé satisfait le qualificateur de la requête | 5 |
triConsistent | détermine si la valeur satisfait le qualificateur de la requête (variante ternaire) (facultatif si la fonction de support 4 est présente) | 6 |
Les index GIN ont sept fonctions d'appui, dont quatre optionnelles, exposées dans le Tableau 38.13. (Pour plus d'informations, voir Chapitre 70.)
Tableau 38.13. Fonctions d'appui GIN
Fonction | Description | Numéro d'appui |
---|---|---|
compare | Compare deux clés et renvoie un entier plus petit que zéro, zéro ou plus grand que zéro, indiquant si la première clé est plus petit, égal à ou plus grand que la seconde. | 1 |
extractValue | Extrait les clés à partir d'une condition de requête | 2 |
extractQuery | Extrait les clés à partir d'une condition de requête | 3 |
consistent | Détermine la valeur correspondant à la condition de requête | 4 |
comparePartial | compare la clé partielle de la requête et la clé de l'index, et renvoie un entier négatif, nul ou positif, indiquant si GIN doit ignorer cette entrée d'index, traiter l'entrée comme une correspondance ou arrêter le parcours d'index (optionnelle) | 5 |
triConsistent | détermine si la valeur correspond à la condition de la requête (variante ternaire) (optionnelle si la fonction d'appui 4 est présente) | 6 |
options | Définit des options spécifiques à cette classe d'opérateur (optionnel) | 7 |
Les index BRIN ont cinq fonctions d'appui basiques, dont une optionnelle, comme indiqué dans Tableau 38.14 ; ces versions des fonctions basiques nécessitent des fonctions d'appui supplémentaires. (Pour plus d'informations, voir Section 71.3.)
Tableau 38.14. Fonctions de support BRIN
Fonction | Description | Numéro de support |
---|---|---|
opcInfo | renvoie des informations internes décrivant les données de résumé des colonnes indexées | 1 |
add_value | ajoute une nouvelle valeur à un enregistrement d'index existant | 2 |
consistent | détermine si la valeur correspond à une condition de la requête | 3 |
union | calcule l'union de deux enregistrements résumés | 4 |
options | Définit des options spécifiques à cette classe d'opérateur (optionnel) | 5 |
Contrairement aux opérateurs de recherche, les fonctions d'appui renvoient le type de donnée, quelqu'il soit, que la méthode d'indexation particulière attend, par exemple, dans le cas de la fonction de comparaison des B-trees, un entier signé. Le nombre et le type des arguments pour chaque fonction de support peuvent dépendre de la méthode d'indexage. Pour les index B-tree et de hachage, les fonctions de support pour la comparaison et le hachage prennent les mêmes types de données en entrée que les opérateurs inclus dans la classe d'opérateur, mais ce n'est pas le cas pour la plupart des fonctions de support GiST, SP-GiST, GIN et BRIN.
Maintenant que nous avons vu les idées, voici l'exemple promis de création
d'une nouvelle classe d'opérateur. Cette classe d'opérateur encapsule les
opérateurs qui trient les nombres complexes selon l'ordre de la valeur
absolue, aussi avons-nous choisi le nom de
complex_abs_ops
. En premier lieu, nous avons besoin d'un
ensemble d'opérateurs. La procédure pour définir des opérateurs a été
discutée dans la Section 38.14. Pour une classe d'opérateur sur les
B-trees, nous avons besoin des opérateurs :
Le plus simple moyen de définie un ensemble d'opérateurs de comparaison est d'écrire en premier la fonction de comparaison B-tree, puis d'écrire les autres fonctions en tant que wrapper de la fonction de support. Ceci réduit les risques de résultats incohérents pour les cas spécifiques. En suivant cette approche, nous devons tout d'abord écrire :
#define Mag(c) ((c)->x*(c)->x + (c)->y*(c)->y) static int complex_abs_cmp_internal(Complex *a, Complex *b) { double amag = Mag(a), bmag = Mag(b); if (amag < bmag) return -1; if (amag > bmag) return 1; return 0; }
Maintenant, la fonction plus-petit-que ressemble à ceci :
PG_FUNCTION_INFO_V1(complex_abs_lt); Datum complex_abs_lt(PG_FUNCTION_ARGS) { Complex *a = (Complex *) PG_GETARG_POINTER(0); Complex *b = (Complex *) PG_GETARG_POINTER(1); PG_RETURN_BOOL(complex_abs_cmp_internal(a, b) < 0); }
Les quatre autres fonctions diffèrent seulement sur la façon dont ils comparent le résultat de la fonction interne au zéro.
Maintenant, déclarons en SQL les fonctions et les opérateurs basés sur ces fonctions :
CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool
AS 'nom_fichier
', 'complex_abs_lt'
LANGUAGE C IMMUTABLE STRICT;
CREATE OPERATOR < (
leftarg = complex, rightarg = complex, procedure = complex_abs_lt,
commutator = > , negator = >= ,
restrict = scalarltsel, join = scalarltjoinsel
);
Il est important de spécifier les fonctions de sélectivité de restriction et de jointure, sinon l'optimiseur sera incapable de faire un usage effectif de l'index.
Voici d'autres choses importantes à noter :
Il ne peut y avoir qu'un seul opérateur nommé, disons, =
et acceptant un type complex
pour ses deux opérandes. Dans le
cas présent, nous n'avons aucun autre opérateur =
pour
complex
mais, si nous construisons un type de donnée
fonctionnel, nous aurions certainement désiré que =
soit
l'opération ordinaire d'égalité pour les nombres complexes (et non pour
l'égalité de leurs valeurs absolues). Dans ce cas, nous aurions eu besoin
d'utiliser un autre nom d'opérateur pour notre fonction
complex_abs_eq
.
Bien que PostgreSQL puisse se débrouiller avec
des fonctions ayant le même nom SQL, tant qu'elles ont en argument des
types de données différents, en C il ne peut exister qu'une fonction globale
pour un nom donné. Aussi ne devons-nous pas donner un nom simple
comme abs_eq
. Habituellement, inclure le nom du type
de données dans le nom de la fonction C est une bonne habitude pour ne
pas provoquer de conflit avec des fonctions pour d'autres types de donnée.
Nous aurions pu faire de abs_eq
le nom
SQL de la fonction, en laissant à PostgreSQL
le soin de la distinguer de toute autre fonction SQL de même nom par les
types de données en argument. Pour la simplicité de l'exemple, nous
donnerons à la fonction le même nom au niveau de C et au niveau de SQL.
La prochaine étape est l'enregistrement de la routine d'appui nécessaire pour les B-trees. Le code exemple C qui implémente ceci est dans le même fichier qui contient les fonctions d'opérateur. Voici comment déclarer la fonction :
CREATE FUNCTION complex_abs_cmp(complex, complex)
RETURNS integer
AS 'filename
'
LANGUAGE C;
Maintenant que nous avons les opérateurs requis et la routine d'appui, nous pouvons enfin créer la classe d'opérateur.
CREATE OPERATOR CLASS complex_abs_ops DEFAULT FOR TYPE complex USING btree AS OPERATOR 1 < , OPERATOR 2 <= , OPERATOR 3 = , OPERATOR 4 >= , OPERATOR 5 > , FUNCTION 1 complex_abs_cmp(complex, complex);
Et c'est fait ! Il devrait être possible maintenant de créer et
d'utiliser les index B-tree sur les colonnes complex
.
Nous aurions pu écrire les entrées de l'opérateur de façon plus explicite comme dans :
OPERATOR 1 < (complex, complex) ,
mais il n'y a pas besoin de faire ainsi quand les opérateurs prennent le même type de donnée que celui pour lequel la classe d'opérateur a été définie.
Les exemples ci-dessus supposent que vous voulez que cette nouvelle classe
d'opérateur soit la classe d'opérateur B-tree par défaut pour le type de
donnée complex
. Si vous ne voulez pas, supprimez simplement le
mot DEFAULT
.
Jusqu'à maintenant, nous avons supposé implicitement qu'une classe d'opérateur s'occupe d'un seul type de données. Bien qu'il ne peut y avoir qu'un seul type de données dans une colonne d'index particulière, il est souvent utile d'indexer les opérations qui comparent une colonne indexée à une valeur d'un type de données différent. De plus, s'il est intéressant d'utiliser un opérateur inter-type en connexion avec une classe d'opérateur, souvent cet autre type de donnée a sa propre classe d'opérateur. Rendre explicite les connexions entre classes en relation est d'une grande aide pour que le planificateur optimise les requêtes SQL (tout particulièrement pour les classes d'opérateur B-tree car le planificateur sait bien comme les utiliser).
Pour gérer ces besoins, PostgreSQL utilise le concept d'une famille d'opérateur . Une famille d'opérateur contient une ou plusieurs classes d'opérateur et peut aussi contenir des opérateurs indexables et les fonctions de support correspondantes appartenant à la famille entière mais pas à une classe particulière de la famille. Nous disons que ces opérateurs et fonctions sont « lâches » à l'intérieur de la famille, en opposition à être lié à une classe spécifique. Typiquement, chaque classe d'opérateur contient des opérateurs de types de données simples alors que les opérateurs inter-type sont lâches dans la famille.
Tous les opérateurs et fonctions d'une famille d'opérateurs doivent avoir une sémantique compatible où les pré-requis de la compatibilité sont dictés par la méthode indexage. Du coup, vous pouvez vous demander la raison pour s'embarrasser de distinguer les sous-ensembles de la famille en tant que classes d'opérateur. En fait, dans beaucoup de cas, les divisions en classe sont inutiles et la famille est le seul groupe intéressant. La raison de la définition de classes d'opérateurs est qu'ils spécifient à quel point la famille est nécessaire pour supporter un index particulier. S'il existe un index utilisant une classe d'opérateur, alors cette classe d'opérateur ne peut pas être supprimée sans supprimer l'index -- mais les autres parties de la famille d'opérateurs, donc les autres classes et les opérateurs lâches, peuvent être supprimées. Du coup, une classe d'opérateur doit être indiquée pour contenir l'ensemble minimum d'opérateurs et de fonctions qui sont raisonnablement nécessaire pour travailler avec un index sur un type de données spécifique, et ensuite les opérateurs en relation mais peuvent être ajoutés en tant que membres lâches de la famille d'opérateur.
Comme exemple, PostgreSQL a une famille d'opérateur
B-tree interne integer_ops
, qui inclut les classes
d'opérateurs int8_ops
, int4_ops
et
int2_ops
pour les index sur les colonnes bigint
(int8
), integer
(int4
) et
smallint
(int2
) respectivement. La famille
contient aussi des opérateurs de comparaison inter-type permettant la
comparaison de deux de ces types, pour qu'un index parmi ces types puisse
être parcouru en utilisant une valeur de comparaison d'un autre type. La
famille peut être dupliqué par ces définitions :
CREATE OPERATOR FAMILY integer_ops USING btree; CREATE OPERATOR CLASS int8_ops DEFAULT FOR TYPE int8 USING btree FAMILY integer_ops AS -- standard int8 comparisons OPERATOR 1 < , OPERATOR 2 <= , OPERATOR 3 = , OPERATOR 4 >= , OPERATOR 5 > , FUNCTION 1 btint8cmp(int8, int8) , FUNCTION 2 btint8sortsupport(internal) , FUNCTION 3 in_range(int8, int8, int8, boolean, boolean) , FUNCTION 4 btequalimage(oid) ; CREATE OPERATOR CLASS int4_ops DEFAULT FOR TYPE int4 USING btree FAMILY integer_ops AS -- standard int4 comparisons OPERATOR 1 < , OPERATOR 2 <= , OPERATOR 3 = , OPERATOR 4 >= , OPERATOR 5 > , FUNCTION 1 btint4cmp(int4, int4) , FUNCTION 2 btint4sortsupport(internal) , FUNCTION 3 in_range(int4, int4, int4, boolean, boolean) , FUNCTION 4 btequalimage(oid) ; CREATE OPERATOR CLASS int2_ops DEFAULT FOR TYPE int2 USING btree FAMILY integer_ops AS -- standard int2 comparisons OPERATOR 1 < , OPERATOR 2 <= , OPERATOR 3 = , OPERATOR 4 >= , OPERATOR 5 > , FUNCTION 1 btint2cmp(int2, int2) , FUNCTION 2 btint2sortsupport(internal) , FUNCTION 3 in_range(int2, int2, int2, boolean, boolean) , FUNCTION 4 btequalimage(oid) ; ALTER OPERATOR FAMILY integer_ops USING btree ADD -- cross-type comparisons int8 vs int2 OPERATOR 1 < (int8, int2) , OPERATOR 2 <= (int8, int2) , OPERATOR 3 = (int8, int2) , OPERATOR 4 >= (int8, int2) , OPERATOR 5 > (int8, int2) , FUNCTION 1 btint82cmp(int8, int2) , -- cross-type comparisons int8 vs int4 OPERATOR 1 < (int8, int4) , OPERATOR 2 <= (int8, int4) , OPERATOR 3 = (int8, int4) , OPERATOR 4 >= (int8, int4) , OPERATOR 5 > (int8, int4) , FUNCTION 1 btint84cmp(int8, int4) , -- cross-type comparisons int4 vs int2 OPERATOR 1 < (int4, int2) , OPERATOR 2 <= (int4, int2) , OPERATOR 3 = (int4, int2) , OPERATOR 4 >= (int4, int2) , OPERATOR 5 > (int4, int2) , FUNCTION 1 btint42cmp(int4, int2) , -- cross-type comparisons int4 vs int8 OPERATOR 1 < (int4, int8) , OPERATOR 2 <= (int4, int8) , OPERATOR 3 = (int4, int8) , OPERATOR 4 >= (int4, int8) , OPERATOR 5 > (int4, int8) , FUNCTION 1 btint48cmp(int4, int8) , -- cross-type comparisons int2 vs int8 OPERATOR 1 < (int2, int8) , OPERATOR 2 <= (int2, int8) , OPERATOR 3 = (int2, int8) , OPERATOR 4 >= (int2, int8) , OPERATOR 5 > (int2, int8) , FUNCTION 1 btint28cmp(int2, int8) , -- cross-type comparisons int2 vs int4 OPERATOR 1 < (int2, int4) , OPERATOR 2 <= (int2, int4) , OPERATOR 3 = (int2, int4) , OPERATOR 4 >= (int2, int4) , OPERATOR 5 > (int2, int4) , FUNCTION 1 btint24cmp(int2, int4) , -- cross-type in_range functions FUNCTION 3 in_range(int4, int4, int8, boolean, boolean) , FUNCTION 3 in_range(int4, int4, int2, boolean, boolean) , FUNCTION 3 in_range(int2, int2, int8, boolean, boolean) , FUNCTION 3 in_range(int2, int2, int4, boolean, boolean) ;
Notez que cette définition « surcharge » la stratégie de l'opérateur et les numéros de fonction support : chaque numéro survient plusieurs fois dans la famille. Ceci est autorisé aussi longtemps que chaque instance d'un numéro particulier a des types de données distincts en entrée. Les instances qui ont les deux types en entrée égalent au type en entrée de la classe d'opérateur sont les opérateurs primaires et les fonctions de support pour cette classe d'opérateur et, dans la plupart des cas, doivent être déclarées comme membre de la classe d'opérateur plutôt qu'en tant que membres lâches de la famille.
Dans une famille d'opérateur B-tree, tous les opérateurs de la famille doivent trier de façon compatible, comme c'est spécifié en détail dans Section 67.2. Pour chaque opérateur de la famille, il doit y avoir une fonction de support pour les deux mêmes types de données en entrée que celui de l'opérateur. Il est recommandé qu'une famille soit complète, c'est-à-dire que pour chaque combinaison de types de données, tous les opérateurs sont inclus. Chaque classe d'opérateur doit juste inclure les opérateurs non inter-types et les fonctions de support pour ce type de données.
Pour construire une famille d'opérateurs de hachage pour plusieurs types de données, des fonctions de support de hachage compatibles doivent être créées pour chaque type de données supporté par la famille. Ici, compatibilité signifie que les fonctions sont garanties de renvoyer le même code de hachage pour toutes les paires de valeurs qui sont considérées égales par les opérateurs d'égalité de la famille, même quand les valeurs sont de type différent. Ceci est habituellement difficile à accomplir quand les types ont différentes représentations physiques, mais cela peut se faire dans la plupart des cas. De plus, convertir une valeur à partir d'un type de données représenté dans la famille d'opérateur vers un autre type de données aussi représenté dans la famille d'opérateur via une coercion implicite ou binaire ne doit pas changer la valeur calculée du hachage. Notez qu'il y a seulement une fonction de support par type de données, pas une par opérateur d'égalité. Il est recommandé qu'une famille soit terminée, c'est-à-dire fournit un opérateur d'égalité pour chaque combinaison de types de données. Chaque classe d'opérateur doit inclure l'opérateur d'égalité non inter-type et la fonction de support pour ce type de données.
Les index GIN, SP-GiST et GiST n'ont pas de notion explicite d'opérations inter-types. L'ensemble des opérateurs supportés est simplement ce que les fonctions de support primaire peuvent supporter pour un opérateur donné.
Dans BRIN, les pré-requis dépendent de l'ensemble de travail fourni par les
classes d'opérateur. Pour les classes basées sur minmax
,
le comportement requis est le même que pour les familles d'opérateur
B-tree : tous les opérateurs d'une famille doivent avoir un tri
compatible, et les conversions ne doivent pas changer l'ordre de tri
associé.
Avant PostgreSQL 8.3, le concept des familles d'opérateurs n'existait pas. Donc, tous les opérateurs inter-type dont le but était d'être utilisés avec un index étaient liés directement à la classe d'opérateur de l'index. Bien que cette approche fonctionne toujours, elle est obsolète car elle rend trop importantes les dépendances de l'index et parce que le planificateur peut gérer des comparaisons inter-type avec plus d'efficacité que quand les typdes de données ont des opérateurs dans la même famille d'opérateur.
PostgreSQL utilise les classe d'opérateur pour inférer les propriétés des opérateurs de plusieurs autres façons que le seul usage avec les index. Donc, vous pouvez créer des classes d'opérateur même si vous n'avez pas l'intention d'indexer une quelconque colonne de votre type de donnée.
En particulier, il existe des caractéristiques de SQL telles que
ORDER BY
et DISTINCT
qui requièrent la comparaison et
le tri des valeurs. Pour implémenter ces caractéristiques sur un type de
donnée défini par l'utilisateur, PostgreSQL
recherche la classe d'opérateur B-tree par défaut pour le type de donnée. Le
membre « equals » de cette classe d'opérateur définit pour le système
la notion d'égalité des valeurs pour GROUP BY
et
DISTINCT
, et le tri ordonné imposé par la classe d'opérateur
définit le ORDER BY
par défaut.
S'il n'y a pas de classe d'opérateur B-tree par défaut pour le type de donnée, le système cherchera une classe d'opérateur de découpage. Mais puisque cette classe d'opérateur ne fournit que l'égalité, il est seulement capable de supporter le regroupement mais pas le tri.
Quand il n'y a pas de classe d'opérateur par défaut pour un type de donnée, vous obtenez des erreurs telles que « could not identify an ordering operator » si vous essayez d'utiliser ces caractéristiques SQL avec le type de donnée.
Dans les versions de PostgreSQL antérieures à
la 7.4, les opérations de tri et de groupement utilisaient implicitement
les opérateurs nommés =
, <
et
>
. Le nouveau comportement qui repose sur les classes
d'opérateurs par défaut évite d'avoir à faire une quelconque supposition
sur le comportement des opérateurs avec des noms particuliers.
Trier par une classe d'opérateur B-tree qui n'est pas celle par défaut est
possible en précisant l'opérateur inférieur-à de la classe dans une option
USING
, par exemple
SELECT * FROM mytable ORDER BY somecol USING ~<~;
Sinon, préciser l'opérateur supérieur-à de la classe dans un
USING
sélectionne un tri par ordre décroissant.
La comparaison de tableaux d'un type de données utilisateur repose également sur la sémantique définie par la classe d'opérateur B-tree par défaut du type. S'il n'y a pas de classe d'opérateur B-tree par défaut, mais qu'il y a une classe d'opérateur de type hash, alors l'égalité de tableau est supportée, mais pas la comparaison pour les tris.
Une autre fonctionnalité SQL qui nécessite une connaissance encore plus
spécifique du type de données est l'option
RANGE
offset
PRECEDING
/FOLLOWING
de fenêtre
pour les fonctions de fenêtrage (voir Section 4.2.8).
Pour une requête telle que
SELECT sum(x) OVER (ORDER BY x RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING) FROM mytable;
il n'est pas suffisant de savoir comment trier par x
;
la base de données doit également comprendre comment
« soustraire 5 » ou « additionner 10 » à la valeur de
x
de la ligne courante pour identifier les limites de la
fenêtre courante. Comparer les limites résultantes aux valeurs de
x
des autres lignes est possible en utilisant les
opérateurs de comparaison fournis par la classe d'opérateur B-tree qui
définit le tri de l'ORDER BY
-- mais les opérateurs
d'addition et de soustraction ne font pas partie de la classe d'opérateur,
alors lesquels devraient être utilisés ? Inscrire en dur ce choix ne serait
pas désirable, car différents ordres de tris (différentes classes d'opérateur
B-tree) pourraient nécessiter des comportements différents. Ainsi, une
classe d'opérateur B-tree peut préciser une fonction de support
in_range qui encapsule les comportements d'addition
et de soustraction faisant sens pour son ordre de tri. Elle peut même
fournir plus d'une fonction de support in_range, s'il fait sens d'utiliser
plus d'un type de données comme offset dans la clause
RANGE
.
Si la classe d'opérateur B-tree associée à la clause ORDER
BY
de la fenêtre n'a pas de fonction de support in_range
correspondante, l'option
RANGE
offset
PRECEDING
/FOLLOWING
n'est pas supportée.
Un autre point important est qu'un opérateur apparaissant dans une famille d'opérateur de hachage est un candidat pour les jointures de hachage, les agrégations de hachage et les optimisations relatives. La famille d'opérateur de hachage est essentiel ici car elle identifie le(s) fonction(s) de hachage à utiliser.
Certaines méthodes d'accès aux index (actuellement seulement GiST et SP-GiST)
supportent le concept d'opérateurs de tri. Nous
avons discuté jusqu'à maintenant d'opérateurs de
recherche. Un opérateur de recherche est utilisable pour
rechercher dans un index toutes les lignes satisfaisant le prédicat
WHERE
colonne_indexée
operateur
constante
.
Notez que rien n'est promis sur l'ordre dans lequel les lignes
correspondantes seront renvoyées. Au contraire, un opérateur de tri ne
restreint pas l'ensemble de lignes qu'il peut renvoyer mais, à la place,
détermine leur ordre. Un opérateur de tri est utilisé pour que l'index
puisse être parcouru pour renvoyer les lignes dans l'ordre représenté par
ORDER BY
colonne_indexée
opérateur
constante
.
Le but de définir des opérateurs de tri de cette façon est de supporter les
recherches du type plus-proche-voisin si l'opérateur sait mesurer les
distances. Par exemple, une requête comme
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
trouve les dix emplacements les plus proches d'un point cible donné. Un
index GiST sur la colonne location peut faire cela de façon efficace
parce que <->
est un opérateur de tri.
Bien que les opérateurs de recherche doivent renvoyer des résultats
booléens, les opérateurs de tri renvoient habituellement d'autres types,
tel que des float ou numeric pour les distances. Ce type n'est
habituellement pas le même que le type de données indexé. Pour éviter les
suppositions en dur sur le comportement des différents types de données, la
définition d'un opérateur de tro doit nommer une famille d'opérateur B-tree
qui spécifie l'ordre de tri du type de données résultant. Comme indiqué
dans la section précédente, les familles d'opérateur B-tree définissent la
notion de tri de PostgreSQL, donc c'est une
représentation naturelle. Comme l'opérateur <->
renvoie float8
, il peut être indiqué dans la commande de
création d'une classe d'opérateur :
OPERATOR 15 <-> (point, point) FOR ORDER BY float_ops
où float_ops
est la famille d'opérateur interne qui
inclut les opérations sur float8
. Cette déclaration indique
que l'index est capable de renvoyer des lignes dans l'ordre de valeurs de
plus en plus hautes de l'opérateur <->
.
Il y a deux caractéristiques spéciales des classes d'opérateur dont nous n'avons pas encore parlées, essentiellement parce qu'elles ne sont pas utiles avec les méthodes d'index les plus communément utilisées.
Normalement, déclarer un opérateur comme membre d'une classe ou d'une famille
d'opérateur
signifie que la méthode d'indexation peut retrouver exactement l'ensemble de
lignes qui satisfait la condition WHERE
utilisant cet opérateur.
Par exemple :
SELECT * FROM table WHERE colonne_entier < 4;
peut être accompli exactement par un index B-tree sur la colonne entière.
Mais il y a des cas où un index est utile comme un guide inexact vers la
colonne correspondante. Par exemple, si un index GiST enregistre seulement
les rectangles limite des objets géométriques, alors il ne peut pas exactement satisfaire
une condition WHERE
qui teste le chevauchement entre des objets
non rectangulaires comme des polygones. Cependant, nous pourrions utiliser
l'index pour trouver des objets dont les rectangles limites chevauchent les
limites de l'objet cible. Dans ce cas, l'index est dit être à perte
pour l'opérateur. Les recherches par index à perte sont implémentées en ayant
une méthode d'indexage qui renvoie un drapeau recheck
quand une ligne pourrait ou non satisfaire la condition de la requête. Le
système principal testera ensuite la condition originale de la requête sur
la ligne récupérée pour s'assurer que la correspondance est réelle. Cette
approche fonctionne si l'index garantit de renvoyer toutes les lignes
requises, ainsi que quelques lignes supplémentaires qui pourront être
éliminées par la vérification. Les méthodes d'indexage qui supportent
les recherches à perte (actuellement GiST, SP-GiST et GIN) permettent aux fonctions
de support des classes individuelles d'opérateurs de lever le drapeau
recheck, et donc c'est essentiellement une fonctionnalité pour les
classes d'opérateur.
Considérons à nouveau la situation où nous gardons seulement dans l'index le
rectangle délimitant un objet complexe comme un polygone. Dans ce cas, il
n'est pas très intéressant de conserver le polygone entier dans l'index -
nous pouvons aussi bien conserver seulement un objet simple du type
box
. Cette situation est exprimée par l'option STORAGE
dans la commande CREATE OPERATOR CLASS
: nous aurons à
écrire quelque chose comme :
CREATE OPERATOR CLASS polygon_ops DEFAULT FOR TYPE polygon USING gist AS ... STORAGE box;
Actuellement, seules les méthodes d'indexation GiST, SP-GiST, GIN et BRIN
supportent un type STORAGE
qui soit différent du type
de donnée de la colonne. Les routines d'appui de GiST pour la compression
(compress
) et la décompression
(decompress
) doivent s'occuper de la conversion du
type de donnée quand STORAGE
est utilisé. De la même
manière, SP-GiST nécessite une fonction d'appui
compress
pour convertir le type de stockage quand il
est différent ; si une classe d'opérateur SP-GiST supporte aussi la
récupération des données, la conversion inverse doit être gérée par la
fonction consistent
. Avec GIN, le type
STORAGE
identifie le type des valeurs
« key », qui est normalement différent du type de la colonne
indexée -- par exemple, une classe d'opérateur pour des colonnes de
tableaux d'entiers pourrait avoir des clés qui sont seulement des entiers.
Les routines de support GIN extractValue
et
extractQuery
sont responsables de l'extraction des
clés à partir des valeurs indexées. BRIN est similaire à GIN : le
type STORAGE
identifie le type de valeurs résumées
stockées, et les procédures de support des classes d'opérateur sont
responsables de l'interprétation correcte des valeurs résumées.