From: | Mitar <mmitar(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Allowing DESC for a PRIMARY KEY column |
Date: | 2024-03-29 21:50:42 |
Message-ID: | CAKLmikPnpbMOAhwHmg39Ai-KBnaBd5YYo_ajuQBj9sXQwVweeg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi!
On Fri, Mar 29, 2024 at 9:41 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2024-03-29 22:02:27 | Re: Statistics Import and Export |
Previous Message | Thomas Munro | 2024-03-29 21:39:05 | Re: BitmapHeapScan streaming read user and prelim refactoring |