Re: Allowing DESC for a PRIMARY KEY column

Mitar <mmitar(at)gmail(dot)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