Postgres: Sort Unique Index by a separate column

Consider a many-to-many relationship of books in a bookshelf:

CREATE TABLE bookshelf_books(
  bookshelf_id text NOT NULL,
  book_id text NOT NULL,
  ordering text NOT NULL
);

CREATE UNIQUE INDEX bookshelf_book ON bookshelf_books(bookshelf_id, book_id);

When the index gets created, I assume it will order items by bookshelf_id, book_id.

But what if I write a query that sorts by ordering?

SELECT * FROM bookshelf_books WHERE bookshelf_id = 'foo' AND book_id = 'bar' ORDER by ordering LIMIT 10;

PG will have to run one more sort. Is there a way to avoid the sort on ordering?

I could get around it by creating one more index with ordering:

CREATE INDEX bookshelf_book_order ON bookshelf_books(bookshelf_id, book_id, order);

But this feels wasteful. Is it possible me to tell PG that while constructing the unique index, it should sort items with ordering?

Solution

Since the index over bookshelf_id and book_id is unique - your example query will return either zero or one row. Sorting requires at least 2 rows.
So, if you want to get all books for a given bookshelf or all bookshelfs where the given book is located - you will need 2 additional indexes:

one non-unique index over (book_id, bookshelf_ordering) which will define how the bookshelfs for the given book will be ordered
and another non-unique index over (bookshelf_id, books_ordering) which will define how the books in the given bookshelf will be ordered

Then you can query like this

SELECT * FROM bookshelf_books 
WHERE bookshelf_id = 'foo' 
ORDER by books_ordering 
LIMIT 10