<font face="Default Sans Serif,Verdana,Arial,Helvetica,sans-serif" size="2">Hi Emre,<br><br>Thanks for your tips! I think I'll do as you suggest and keep it simple with a single ordering operator.<br><br>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?<br><br>Steven<br><br><font color="#990099">-----Emre Hasegeli <emre(at)hasegeli(dot)com> wrote: -----</font><div style="padding-left:5px;"><div style="padding-right:0px;padding-left:5px;border-left:solid black 2px;">To: Steven Xu <stevenx(at)yorku(dot)ca><br>From: Emre Hasegeli <emre(at)hasegeli(dot)com><br>Date: 03/05/2016 09:30AM<br>Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org><br>Subject: Re: [GENERAL] Custom column ordering<br><br><div><font face="Courier New,Courier,monospace" size="2">> - Why is PostgreSQL not using the functional index I created and why is it<br>> not being ordered correctly?<br><br>Your example works for me:<br><br>> hasegeli=# CREATE TABLE device_port (port text);<br>> CREATE TABLE<br>><br>> hasegeli=# CREATE INDEX idx_device_port_port_proper ON device_port (cast_to_port(port) port_ops DESC);<br>> CREATE INDEX<br>><br>> hasegeli=# INSERT INTO device_port VALUES ('a'), ('b'), ('c');<br>> INSERT 0 3<br>><br>> hasegeli=# SELECT port FROM device_port ORDER BY port;<br>> port<br>> ------<br>> c<br>> b<br>> a<br>> (3 rows)<br>><br>> hasegeli=# SET enable_seqscan = 0;<br>> SET<br>><br>> hasegeli=# EXPLAIN ANALYZE SELECT port FROM device_port ORDER BY cast_to_port(port);<br>> QUERY PLAN<br>> --------------------------------------------------------------------------------------------------------------------------------------------------------<br>> 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.053 rows=3 loops=1)<br>> Planning time: 0.079 ms<br>> Execution time: 0.079 ms<br>> (3 rows)<br><br>> - Is creating a separate data type and using a functional index on the<br>> casts to this data type the right approach to a custom ordering?<br><br>You don't need to create a type for this. You can just create a<br>non-default operator class and use it with your text type by specify<br>the operator with ORDER BY ... USING clause.<br><br>> Creating the index:<br>> CREATE INDEX idx_device_port_port_proper on device_port (cast_to_port(port) port_ops desc);<br><br>The operator class is not necessary in here as it is the default for<br>the "port" type. DESC also wouldn't make any difference.<br></font></div></div></div><div></div></font>