Postgres: Sort Unique Index by a separate column-CodePudding

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?

CodePudding user response：

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