I have a table with time-series data and with a primary key – actually several large tables but this will do as an example:

postgres=# create table foo (x varchar, y varchar, z text, updated timestamptz, primary key(x, y, updated));
CREATE TABLE
postgres=# d foo
                         Table "public.foo"
 Column  |           Type           | Collation | Nullable | Default 
---------+--------------------------+-----------+----------+---------
 x       | character varying        |           | not null | 
 y       | character varying        |           | not null | 
 z       | text                     |           |          | 
 updated | timestamp with time zone |           | not null | 
Indexes:

This will automatically create an index on x, updated. However, many of my queries are looking for “the most recent foo for various values of x” not the oldest so the index is the wrong way round for efficient querying.

For example, If I do:

explain analyze select distinct on (x,y,updated) * from foo order by x,y,updated;

Then it uses the index nicely:

----------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=0.43..145739.21 rows=158334 width=38) (actual time=0.031..889.771 rows=1583344 loops=1)
   ->  Index Scan using foo_pkey on foo  (cost=0.43..133864.13 rows=1583344 width=38) (actual time=0.030..534.995 rows=1583344 loops=1)
 Planning Time: 0.074 ms
 Execution Time: 958.419 ms
(4 rows)

but of course shows me the oldest elements.

If I do:

userdb=> explain analyze select distinct on (x,y,updated) * from foo order by x, y, updated desc;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=278945.67..294779.11 rows=158334 width=38) (actual time=3823.677..4808.844 rows=1583344 loops=1)
   ->  Sort  (cost=278945.67..282904.03 rows=1583344 width=38) (actual time=3823.674..4482.405 rows=1583344 loops=1)
         Sort Key: x, y, updated DESC
         Sort Method: external merge  Disk: 79536kB
         ->  Seq Scan on foo  (cost=0.00..29314.44 rows=1583344 width=38) (actual time=0.013..142.781 rows=1583344 loops=1)
 Planning Time: 0.075 ms
 Execution Time: 4888.878 ms
(7 rows)

then it gives me the most recent data which is what I want but takes five times as long.

What I’d like to get is this:

                         Table "public.foo"
 Column  |           Type           | Collation | Nullable | Default 
---------+--------------------------+-----------+----------+---------
 x       | character varying        |           | not null | 
 y       | character varying        |           | not null | 
 z       | integer                  |           |          | 
 updated | timestamp with time zone |           | not null | 
Indexes:
    "foo_pkey" PRIMARY KEY, btree (x, y, updated DESC)

This just produces a syntax error:

create table foo (x varchar, y varchar, z text, updated timestamptz, primary key(x, y, updated desc));

I can do this:

postgres=# create index on foo (x, y, updated desc);
CREATE INDEX
postgres=#

which fixes the query but then I get two indices which is obviously less performant than one. I can drop the primary key but that feels pretty ugly. Is there a better way to get what I want? Is there a good reason why a PK generates an index with particular ordering and why that ordering being the other way round would be be problematic?

The only references I can see elsewhere are a thread (https://www.postgresql.org/message-id/[email protected]) which suggests that a unique index and no PK is the best option but that’s 15 years old. Is there anything better now?

i.e. Is this really the best I can do?

=> alter table foo drop constraint foo_pkey;
ALTER TABLE
=> create unique index on foo(x,y,updated desc);
CREATE INDEX
=> explain analyze select distinct on (x,y,updated) * from foo order by x, y, updated desc;
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=0.43..126019.21 rows=158334 width=38) (actual time=0.025..867.984 rows=1583344 loops=1)
   ->  Index Scan using foo_x_y_updated_idx on foo  (cost=0.43..114144.13 rows=1583344 width=38) (actual time=0.024..521.720 rows=1583344 loops=1)
 Planning Time: 0.171 ms
 Execution Time: 935.838 ms
(4 rows)