Mitar <mmi...@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
