Обсуждение: Custom column ordering

<font face=3D"Default Sans Serif,Verdana,Arial,Helvetica,sans-serif" size=
=3D"2">Hi all,I'm trying to order some rows based on port names, a =
text column, using some domain-specific knowledge for Netdisco, an open-sou=
rce application. In particular, I'm trying to do this without havin=
g to redo the entire design for the database. Note that in this database, t=
here are no foreign key constraints, mostly because they weren't considered=
 in the original inception of the software. The rough idea of the s=
olution I've come up with is to create a new composite data type called "<f=
ont face=3D"Default Monospace,Courier New,Courier,monospace">port" w=
ith a single text column. Then I created the relevant comparison functions,=
 operators and operator classes under the "<font face=3D"Default Monospace,=
Courier New,Courier,monospace">port=5Fops" operator family. Then, I =
created a function "<font face=3D"Default Monospace,Courier New,Courier,mon=
ospace">cast=5Fto=5Fport(text)" that casts "<font face=3D"Default Mo=
nospace,Courier New,Courier,monospace">text" data types to "<font fa=
ce=3D"Default Monospace,Courier New,Courier,monospace">port" (which =
simply creates a tuple with the single text value, see the code for details=
). Finally, I created an index on the "<font face=3D"Default Monospace,Cour=
ier New,Courier,monospace">device=5Fport" table with "<font face=3D"=
Default Monospace,Courier New,Courier,monospace">cast=5Fto=5Fport(port)</fo=
nt>" as the indexed column using  "port=5Fops". However, when I run "<font =
face=3D"Default Monospace,Courier New,Courier,monospace">select port from d=
evice=5Fport order by cast=5Fto=5Fport(port)", it doesn't use the in=
dex I created and doesn't even order using the operators I created. Instead=
, it orders by the lexicographical ordering of the original text column.<br=
>Questions:  - Why is PostgreSQL not using the functional inde=
x I created and why is it not being ordered correctly?  - Is creat=
ing a separate data type and using a functional index on the casts to this =
data type the right approach to a custom ordering?Steven<br=
>Details:Creating the "<font face=3D"Default Monospace,Courier New,Cour=
ier,monospace">port" type:<font face=3D"Default Monospace,Courie=
r New,Courier,monospace">  create type port as (f1 text);<b=
r>Creating the comparator function, substituted with much shorter code as a=
n example. I can include the original code, but it's much shorter to provid=
e this.It returns -1 if the first argument is "less than" the second ar=
gument, 0 if they are equal and 1 otherwise. In this example, "less than" m=
eans lexicographically greater, because of the negation.<font face=3D"D=
efault Monospace,Courier New,Courier,monospace">  create or replace fu=
nction port=5Fcmp(port,port)      RETURNS integer as=
  $$    my ($a, $b) =3D @=5F;    retu=
rn -( $a cmp $b ); // comparison function does the opposite of cmp =
; $$ language plperl;Creating the "<font face=3D"Default Mon=
ospace,Courier New,Courier,monospace">port=5Fops" operators and oper=
ator classes for the "<font face=3D"Default Monospace,Courier New,Courier,m=
onospace">port" type:<font face=3D"Default Monospace,Courier New=
,Courier,monospace">CREATE OR REPLACE FUNCTION port=5Flt(port, port)&nb=
sp;   RETURNS boolean AS$$    BEGIN&nb=
sp;       RETURN port=5Fcmp($1, $2) < 0;&nb=
sp;   END;$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCT=
ION port=5Fgt(port, port)    RETURNS boolean AS$$<br=
>    BEGIN        RETURN p=
ort=5Fcmp($1, $2) > 0;    END;$$ LANGUAGE plpgsql=
;CREATE OR REPLACE FUNCTION port=5Flte(port, port)  &=
nbsp; RETURNS boolean AS$$    BEGIN  &=
nbsp;     RETURN port=5Fcmp($1, $2) <=3D 0; &nbs=
p;  END;$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION por=
t=5Fgte(port, port)    RETURNS boolean AS$$ =
;   BEGIN        RETURN port=5F=
cmp($1, $2) >=3D 0;    END;$$ LANGUAGE plpgsql;<b=
r>CREATE OR REPLACE FUNCTION port=5Feq(port, port)   =
; RETURNS boolean AS$$    BEGIN   =
;     RETURN port=5Fcmp($1, $2) =3D 0;   =
 END;$$ LANGUAGE plpgsql;CREATE OPERATOR < ( PROCEDURE=3Dpor=
t=5Flt, LEFTARG=3Dport, RIGHTARG=3Dport);CREATE OPERATOR <=3D ( PROC=
EDURE=3Dport=5Flte, LEFTARG=3Dport, RIGHTARG=3Dport);CREATE OPERATOR &g=
t;=3D ( PROCEDURE=3Dport=5Fgte, LEFTARG=3Dport,  RIGHTARG=3Dport);=
CREATE OPERATOR > ( PROCEDURE=3Dport=5Fgte, LEFTARG=3Dport, RIGHTARG=3Dp=
ort);<font face=3D"Default Monospace,Courier New,Courier,monospa=
ce">CREATE OPERATOR =3D ( PROCEDURE=3Dport=5Feq, LEFTARG=3Dport, RIGHTARG=
=3Dport);CREATE OPERATOR CLASS port=5Fops DEFAULTFOR TYPE port =
USING btreeASOPERATOR        1&n=
bsp;      <  ,OPERATOR  &nbs=
p;     2       <=3D ,<=
br>OPERATOR        3   &n=
bsp;   =3D  ,OPERATOR      =
;  4       >=3D ,OPERATOR &n=
bsp;      5       &g=
t;  ,FUNCTION        1 &nb=
sp;     port=5Fcmp(port, port);Creat=
ing the function to cast "<font face=3D"Default Monospace,Courier New,Couri=
er,monospace">text" data to "<font face=3D"Default Monospace,Courier=
 New,Courier,monospace">port" data:<font face=3D"Default Monospa=
ce,Courier New,Courier,monospace">CREATE OR REPLACE FUNCTION cast=5Fto=5Fpo=
rt(text) RETURNS port LANGUAGE sqlAS $function$&n=
bsp; SELECT ($1);$function$ IMMUTABLE;Creating the index=
:CREATE =
INDEX idx=5Fdevice=5Fport=5Fport=5Fproper on device=5Fport (cast=5Fto=5Fpor=
t(port) port=5Fops desc);Sample table structure, shortened f=
or brevity:<font face=3D"Default Monospace,Courier New,Courier,monospac=
e">            =
         Table "public.device=5Fpor=
t"    Column    |    =
        Type    &nbs=
p;        |     =
;  Modifiers--------------+-----------------------------+---------=
--------------- ip        =
   | inet         &n=
bsp;            =
;  | not null port       &=
nbsp; | text          &nb=
sp;            =
 | not null

>   - Why is PostgreSQL not using the functional index I created and why is it
> not being ordered correctly?

Your example works for me:

> hasegeli=# CREATE TABLE device_port (port text);
> CREATE TABLE
>
> hasegeli=# CREATE INDEX idx_device_port_port_proper ON device_port (cast_to_port(port) port_ops DESC);
> CREATE INDEX
>
> hasegeli=# INSERT INTO device_port VALUES ('a'), ('b'), ('c');
> INSERT 0 3
>
> hasegeli=# SELECT port FROM device_port ORDER BY port;
> port
> ------
> c
> b
> a
> (3 rows)
>
> hasegeli=# SET enable_seqscan = 0;
> SET
>
> hasegeli=# EXPLAIN ANALYZE SELECT port FROM device_port ORDER BY cast_to_port(port);
>                                                                        QUERY PLAN
>
--------------------------------------------------------------------------------------------------------------------------------------------------------
>  Index Scan Backward using idx_device_port_port_proper on device_port  (cost=0.15..408.55 rows=1360 width=32) (actual
time=0.042..0.053rows=3 loops=1) 
>  Planning time: 0.079 ms
>  Execution time: 0.079 ms
> (3 rows)

>   - Is creating a separate data type and using a functional index on the
> casts to this data type the right approach to a custom ordering?

You don't need to create a type for this.  You can just create a
non-default operator class and use it with your text type by specify
the operator with ORDER BY ... USING clause.

> Creating the index:
> CREATE INDEX idx_device_port_port_proper on device_port (cast_to_port(port) port_ops desc);

The operator class is not necessary in here as it is the default for
the "port" type.  DESC also wouldn't make any difference.

<font face=3D"Default Sans Serif,Verdana,Arial,Helvetica,sans-serif" size=
=3D"2">Hi Emre,Thanks for your tips! I think I'll do as you suggest=
 and keep it simple with a single ordering operator.Although, I do =
find your output very strange. You wrote ORDER BY port, which is a text typ=
e. Why does Postgres order using the ordering operators of the "Port" data =
type rather than the "text" type, even though you haven't performed a cast?=
Steven-----Emre Hasegeli <emre@h=
asegeli.com> wrote: -----<div st=
yle=3D"padding-right:0px;padding-left:5px;border-left:solid black 2px;">To:=
 Steven Xu <stevenx@yorku.ca>From: Emre Hasegeli <emre@hasegel=
i.com>Date: 03/05/2016 09:30AMCc: "pgsql-general@postgresql.org"=
 <pgsql-general@postgresql.org>Subject: Re: [GENERAL] Custom colu=
mn ordering<font face=3D"Courier New,Courier,monospace" size=
=3D"2">>   - Why is PostgreSQL not using the functional index I cre=
ated and why is it> not being ordered correctly?Your example=
 works for me:> hasegeli=3D# CREATE TABLE device=5Fport (port te=
xt);> CREATE TABLE>> hasegeli=3D# CREATE INDEX idx=5Fd=
evice=5Fport=5Fport=5Fproper ON device=5Fport (cast=5Fto=5Fport(port) port=
=5Fops DESC);> CREATE INDEX>> hasegeli=3D# INSERT INTO=
 device=5Fport VALUES ('a'), ('b'), ('c');> INSERT 0 3>&g=
t; hasegeli=3D# SELECT port FROM device=5Fport ORDER BY port;> port<=
br>> ------> c> b> a> (3 rows)>&g=
t; hasegeli=3D# SET enable=5Fseqscan =3D 0;> SET>> has=
egeli=3D# EXPLAIN ANALYZE SELECT port FROM device=5Fport ORDER BY cast=5Fto=
=5Fport(port);>               &nb=
sp;                     &=
nbsp;                    =
              QUERY PLAN> -------=
---------------------------------------------------------------------------=
----------------------------------------------------------------------&=
gt;  Index Scan Backward using idx=5Fdevice=5Fport=5Fport=5Fproper on =
device=5Fport  (cost=3D0.15..408.55 rows=3D1360 width=3D32) (actual ti=
me=3D0.042..0.053 rows=3D3 loops=3D1)>  Planning time: 0.079 ms=
>  Execution time: 0.079 ms> (3 rows)>  =
 - Is creating a separate data type and using a functional index on the=
> casts to this data type the right approach to a custom ordering?<b=
r>You don't need to create a type for this.  You can just create a=
non-default operator class and use it with your text type by specifythe=
 operator with ORDER BY ... USING clause.> Creating the index:<b=
r>> CREATE INDEX idx=5Fdevice=5Fport=5Fport=5Fproper on device=5Fport (c=
ast=5Fto=5Fport(port) port=5Fops desc);The operator class is not ne=
cessary in here as it is the default forthe "port" type.  DESC als=
o wouldn't make any difference.</fo=
nt>

> Although, I do find your output very strange. You wrote ORDER BY port, which
> is a text type. Why does Postgres order using the ordering operators of the
> "Port" data type rather than the "text" type, even though you haven't
> performed a cast?

That is an editing mistake.  I mean

> hasegeli=# SELECT port FROM device_port ORDER BY cast_to_port(port);

<font face=3D"Default Sans Serif,Verdana,Arial,Helvetica,sans-serif" size=
=3D"2">I see. Thanks again! I removed the "port" data type from my implemen=
tation and just used the ORDER BY... USING clause and the explain output/re=
sults is providing what I expect.Steven<div style=3D"paddin=
g-left:5px;"><div style=3D"padding-right:0px;padding-left:5px;border-left:s=
olid black 2px;"><font face=3D"Courier New,Courier,monospace" size=3D"=
2">That is an editing mistake.  I mean> hasegeli=3D# SELECT=
 port FROM device=5Fport ORDER BY cast=5Fto=5Fport(port);<=
/div>