Re: Custom column ordering

[Steven Xu <stevenx@xxxxxxxx>]

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 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?

Steven

-----Emre Hasegeli <emre@xxxxxxxxxxxx> wrote: -----

To: Steven Xu <stevenx@xxxxxxxx>
From: Emre Hasegeli <emre@xxxxxxxxxxxx>
Date: 03/05/2016 09:30AM
Cc: "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx>
Subject: Re: Custom column ordering

>   - 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.053 rows=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.