Optimization of simple join query PostgreSQL-CodePudding

I have two tables: book and bought_book

book: id, price, author_id

sold_book: id, date, book_id, buyer_id

book 1:M sold_book

I want to find max price for sold book (for author). This is my query now:

SELECT max(b.price) 
FROM book b 
JOIN sold_book sb ON b.id = sb.book_id 
where b.author_id = 1;

But the problem is that I have millions of books and millions of sold books as well. And I want to make the most efficient query. I use PostgreSQL. Can my query be more efficient?

CodePudding user response：

You can create an index for the where clause.

For example,

create index book_author_id_index on book (author_id);

For more information, please refer to, https://www.postgresql.org/docs/14/performance-tips.html

CodePudding user response：

You don't need to join to every sold_book record, just one of them:

SELECT max(b.price) 
FROM   book b 
where  exists (select from sold_book sb where sb.book_id = b.id)
and    b.author_id = 1;

Indexes on book.author_id, and sold_book.book_id would help.