"ORDER BY ... Clause "USING" dans PostgreSQL

Question

La clause ORDER BY est décrite dans la documentation de PostgreSQL comme suit :

ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]

Quelqu'un peut-il me donner des exemples d'utilisation de l'option USING operator ? Est-il possible d'obtenir un ordre alterné de l'ensemble des résultats ?

Answer 1

Un exemple très simple serait :

> SELECT * FROM tab ORDER BY col USING <

Mais c'est ennuyeux, parce qu'il n'y a rien que vous ne puissiez obtenir avec le traditionnel ORDER BY col ASC .

De plus, le catalogue standard ne mentionne rien d'excitant au sujet des fonctions/opérateurs de comparaison étranges. Vous pouvez en obtenir une liste :

    > SELECT amoplefttype::regtype, amoprighttype::regtype, amopopr::regoper 
      FROM pg_am JOIN pg_amop ON pg_am.oid = pg_amop.amopmethod 
      WHERE amname = 'btree' AND amopstrategy IN (1,5);

Vous remarquerez qu'il y a surtout < y > pour les types primitifs comme integer , date etc. et d'autres pour les tableaux et les vecteurs, etc. Aucun de ces opérateurs ne vous aidera à obtenir une commande personnalisée.

Sur le plus Dans les cas où une commande personnalisée est nécessaire, vous pouvez vous en sortir en utilisant quelque chose comme ... ORDER BY somefunc(tablecolumn) ... donde somefunc met en correspondance les valeurs de manière appropriée. Comme cela fonctionne avec toutes les bases de données, c'est aussi la méthode la plus courante. Pour les choses simples, vous pouvez même écrire une expression au lieu d'une fonction personnalisée.

Passer à la vitesse supérieure

ORDER BY ... USING a du sens dans plusieurs cas :

• La commande est si peu commune, que les somefunc L'astuce ne fonctionne pas.
• Vous travaillez avec un type non primitif (comme point , circle ou des nombres imaginaires) et vous ne voulez pas vous répéter dans vos requêtes avec des calculs étranges.
• L'ensemble de données que vous voulez trier est si grand que l'utilisation d'un index est souhaitée, voire nécessaire.

Je me concentrerai sur les types de données complexes : il y a souvent plus d'une façon de les trier de manière raisonnable. Un bon exemple est point : Vous pouvez les "ordonner" en fonction de la distance à (0,0), ou par x d'abord, puis par y ou simplement par y ou tout ce que vous voulez.

Bien sûr, PostgreSQL a des opérateurs prédéfinis pour point :

    > CREATE TABLE p ( p point );
    > SELECT p <-> point(0,0) FROM p;

Mais aucun d'entre eux est déclaré utilisable pour ORDER BY par défaut (voir ci-dessus) :

    > SELECT * FROM p ORDER BY p;
    ERROR:  could not identify an ordering operator for type point
    TIP:  Use an explicit ordering operator or modify the query.

Des opérateurs simples pour point sont les opérateurs "inférieur" et "supérieur". <^ y >^ . Ils comparent simplement les y une partie de l'argument. Mais :

    >  SELECT * FROM p ORDER BY p USING >^;
    ERROR: operator > is not a valid ordering operator
    TIP: Ordering operators must be "<" or ">" members of __btree__ operator families.

ORDER BY USING nécessite un opérateur avec une sémantique définie : Il faut évidemment que ce soit un opérateur binaire, qu'il accepte le même type que les arguments et qu'il retourne un booléen. Je pense qu'il doit également être transitif (si a < b et b < c alors a < c). Il peut y avoir d'autres exigences. Mais toutes ces conditions sont également nécessaires pour un bon btree -ordre d'indexation. Ceci explique les étranges messages d'erreur contenant la référence à btree .

ORDER BY USING exige aussi non seulement un opérateur à définir mais un classe d'opérateurs et un famille d'opérateurs . Alors qu'un pourrait implémenter le tri avec un seul opérateur, PostgreSQL essaie de trier efficacement et de minimiser les comparaisons. Par conséquent, plusieurs opérateurs sont utilisés même lorsque vous n'en spécifiez qu'un seul - les autres doivent respecter certaines contraintes mathématiques - j'ai déjà mentionné la transitivité, mais il y en a d'autres.

Changement de vitesse

Définissons quelque chose de convenable : Un opérateur pour les points qui ne compare que les y partie.

La première étape consiste à créer une famille d'opérateurs personnalisée qui peut être utilisée par l'équipe de l btree méthode d'accès à l'index. voir

    > CREATE OPERATOR FAMILY xyzfam USING btree;   -- superuser access required!
    CREATE OPERATOR FAMILY

Ensuite, nous devons fournir une fonction comparateur qui renvoie -1, 0, +1 lors de la comparaison de deux points. Cette fonction WILL être appelé en interne !

    > CREATE FUNCTION xyz_v_cmp(p1 point, p2 point) RETURNS int 
      AS $$BEGIN RETURN btfloat8cmp(p1[1],p2[1]); END $$ LANGUAGE plpgsql;
    CREATE FUNCTION

Ensuite, nous définissons la classe d'opérateurs pour la famille. Voir le manuel pour une explication des chiffres.

    > CREATE OPERATOR CLASS xyz_ops FOR TYPE point USING btree FAMILY xyzfam AS 
        OPERATOR 1 <^ ,
        OPERATOR 3 ?- ,
        OPERATOR 5 >^ ,
        FUNCTION 1 xyz_v_cmp(point, point) ;
    CREATE OPERATOR CLASS

Cette étape combine plusieurs opérateurs et fonctions et définit également leur relation et leur signification. Par exemple OPERATOR 1 moyens : C'est l'opérateur pour less-than tests.

Maintenant, les opérateurs <^ y >^ peut être utilisé dans ORDER BY USING :

> INSERT INTO p SELECT point(floor(random()*100), floor(random()*100)) FROM generate_series(1, 5);
INSERT 0 5
> SELECT * FROM p ORDER BY p USING >^;
    p    
---------
 (17,8)
 (74,57)
 (59,65)
 (0,87)
 (58,91)

Voila - trié par y .

Pour résumer : ORDER BY ... USING est un regard intéressant sous le capot de PostgreSQL. Mais rien dont vous n'aurez besoin de sitôt, à moins que vous ne travailliez dans le domaine de l'informatique. muy des domaines spécifiques de la technologie des bases de données.

Un autre exemple peut être trouvé dans la documentation de Postgres. avec le code source de l'exemple aquí y aquí . Cet exemple montre également comment créer les opérateurs.

Answer 2

Des échantillons :

CREATE TABLE test
(
  id serial NOT NULL,
  "number" integer,
  CONSTRAINT test_pkey PRIMARY KEY (id)
)

insert into test("number") values (1),(2),(3),(0),(-1);

select * from test order by number USING > //gives 3=>2=>1=>0=>-1

select * from test order by number USING < //gives -1=>0=>1=>2=>3

Donc, c'est équivalent à desc y asc . Mais vous pouvez utiliser votre propre opérateur, c'est la caractéristique essentielle de l'UE. USING

Answer 3

Bonnes réponses, mais ils n'ont pas mentionné un seul cas valable pour "UTILISER".

Lorsque vous avez créé un index avec une famille d'opérateurs non par défaut, par exemple varchar_pattern_ops ( ~>~ , ~<~ , ~>=~ , ... ) au lieu de < , > , >= alors si vous recherchez sur la base d'un index et que vous voulez utiliser l'index dans la clause order by vous devez spécifier USING avec l'opérateur approprié.

Ceci peut être illustré par un tel exemple :

CREATE INDEX index_words_word ON words(word text_pattern_ops); 

Comparons ces deux requêtes :

SELECT * FROM words WHERE word LIKE 'o%' LIMIT 10;

y

SELECT * FROM words WHERE word LIKE 'o%' ORDER BY word LIMIT 10;

La différence entre leurs exécutions est de près de 100 fois dans une BD de 500K mots ! De plus, les résultats peuvent ne pas être corrects dans une locale non-C.

Comment cela a-t-il pu arriver ?

Lorsque vous effectuez une recherche avec LIKE y ORDER BY clause, vous faites réellement cet appel :

SELECT * FROM words WHERE word ~>=~ 'o' AND word ~<~'p' ORDER BY word USING < LIMIT 10;

Votre index créé avec ~<~ à l'esprit, donc PG ne peut pas utiliser un index donné dans un ORDER BY clause. Pour bien faire les choses, la requête doit être réécrite sous cette forme :

SELECT * FROM words WHERE word ~>=~ 'o' AND word ~<~'p' ORDER BY word USING ~<~ LIMIT 10;

ou

SELECT * FROM words WHERE word LIKE 'o%' ORDER BY word USING ~<~ LIMIT 10;

Answer 4

En option, on peut ajouter le mot clé ASC (ascendant) ou DESC. (descendant) après toute expression de la clause ORDER BY. S'il n'est pas spécifié, ASC est supposé par défaut. On peut également ajouter un mot-clé nom d'opérateur d'ordre spécifique peut être spécifié dans la clause USING. Une adresse doit être un membre inférieur ou supérieur d'un opérateur B-tree. famille d'opérateurs B-tree. ASC est généralement équivalent à USING < et DESC est généralement équivalent à USING >.

PostgreSQL 9.0

Cela peut ressembler à quelque chose comme ceci je pense (je n'ai pas postgres pour vérifier cela en ce moment, mais je vérifierai plus tard)

SELECT Name FROM Person
ORDER BY NameId USING >