Using unique btree indexes for pathkeys with one extra column

Hello,

In the category "doing more tricks with our existing btrees", which
includes all that difficult stuff like skip scans and incremental
sort, here's an easier planner-only one: if you have a unique index
on (a) possibly "including" (b) and you have a pathkey (a, b), you can
use an index [only] scan. That is, if the index is unique, and you
want exactly one extra column in index order, then you don't need any
extra sorting to get (a, b) in order. (If the index is not unique, or
there is more than one extra trailing column in the pathkey, you need
the incremental sort patch[1] to use this index). This was brought to
my attention by a guru from a different RDBMS complaining about stupid
stuff that PostgreSQL does and I was triggered to write this message
as a kind of TODO note...

[1] https://commitfest.postgresql.org/24/1124/

--
Thomas Munro
https://enterprisedb.com