Обсуждение: [MASSMAIL]Allowing DESC for a PRIMARY KEY column

Hi!

I have the same problem as [1]. I have table something like:

CREATE TABLE values (
  id int NOT NULL,
  revision int NOT NULL,
  data jsonb NOT NULL,
  PRIMARY KEY (id, revision)
)

And I would like to be able to specify PRIMARY KEY (id, revision DESC)
because the most common query I am making is:

SELECT data FROM values WHERE id=123 ORDER BY revision DESC LIMIT 1

My understanding, based on [2], is that the primary key index cannot
help here, unless it is defined with DESC on revision. But this does
not seem to be possible. Would you entertain a patch adding this
feature? It seems pretty straightforward?


Mitar

[1] https://stackoverflow.com/questions/45597101/primary-key-with-asc-or-desc-ordering
[2] https://www.postgresql.org/docs/16/indexes-ordering.html

-- 
https://mitar.tnode.com/
https://twitter.com/mitar_m
https://noc.social/@mitar

Mitar <mmitar@gmail.com> writes:
> And I would like to be able to specify PRIMARY KEY (id, revision DESC)
> because the most common query I am making is:
> SELECT data FROM values WHERE id=123 ORDER BY revision DESC LIMIT 1

Did you experiment with whether that actually needs a special index?
I get

regression=# create table t(id int, revision int, primary key(id, revision));
CREATE TABLE
regression=# explain select * from t WHERE id=123 ORDER BY revision DESC LIMIT 1;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Limit  (cost=0.15..3.45 rows=1 width=8)
   ->  Index Only Scan Backward using t_pkey on t  (cost=0.15..36.35 rows=11 width=8)
         Index Cond: (id = 123)
(3 rows)

> My understanding, based on [2], is that the primary key index cannot
> help here, unless it is defined with DESC on revision. But this does
> not seem to be possible. Would you entertain a patch adding this
> feature? It seems pretty straightforward?

You would need a lot stronger case than "I didn't bother checking
whether I really need this".

            regards, tom lane

Hi!

On Fri, Mar 29, 2024 at 9:41 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> You would need a lot stronger case than "I didn't bother checking
> whether I really need this".

Thanks! I have tested it this way (based on your example):

create table t (id int not null, revision int not null);
create unique index on t (id, revision desc);
explain select * from t where id=123 order by revision desc limit 1;
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Limit  (cost=0.15..3.45 rows=1 width=8)
   ->  Index Only Scan using t_id_revision_idx on t  (cost=0.15..36.35
rows=11 width=8)
         Index Cond: (id = 123)
(3 rows)

It is very similar, with only the direction difference. Based on [1] I
was under the impression that "Index Only Scan Backward" is much
slower than "Index Only Scan", but based on your answer it seems I
misunderstood and backwards scanning is comparable with forward
scanning? Especially this section:

"Consider a two-column index on (x, y): this can satisfy ORDER BY x, y
if we scan forward, or ORDER BY x DESC, y DESC if we scan backward.
But it might be that the application frequently needs to use ORDER BY
x ASC, y DESC. There is no way to get that ordering from a plain
index, but it is possible if the index is defined as (x ASC, y DESC)
or (x DESC, y ASC)."

I am curious, what is then an example where the quote from [1]
applies? Really just if I would be doing ORDER BY id, revision DESC on
the whole table? Because one future query I am working on is where I
select all rows but for only the latest (highest) revision. Curious if
that will have an effect there.


Mitar

[1] https://www.postgresql.org/docs/16/indexes-ordering.html

--
https://mitar.tnode.com/
https://twitter.com/mitar_m
https://noc.social/@mitar