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/5afb04860910140012g79755d53t9eab8de2fc2bb1b3@mail.gmail.com) 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)
You cannot create a primary key with an index that sorts descending on a column.
But that may not be necessary for your use case: PostgreSQL can scan the index in descending order just as efficiently as in ascending order, so you should rewrite the query as follows:
SELECT DISTINCT ON (x, y, updated) *
FROM foo
ORDER BY x DESC, y DESC, updated DESC;
That will scan the primary key index backwards and does not require a sort.
If it is important for you that the result is sorted in ascending order for x and y, you either have to bite the bullet and create a second index, or you add another sort after the DISTINCT, which should be a bit cheaper than sorting before the DISTINCT:
SELECT *
FROM (SELECT DISTINCT ON (x, y, updated) *
FROM foo
ORDER BY x DESC, y DESC, updated DESC
) AS q
ORDER BY x, y;