Not knowing a whole lot about the internals of Pg, one thing jumped out
at me, that each trip to get data from bv_books took 2.137 ms, which
came to over 4.2 seconds right there.
The problem "seems" to be the 1993 times that the nested loop spins, as
almost all of the time is spent there.
Personally, I am amazed that it takes 3.585 seconds to index scan
i_bookgenres_genre_id. Is that a composite index? Analyzing the
taables may help, as the optimizer appears to mispredict the number of
rows returned.
I would be curious to see how it performs with an "IN" clause, which I
would suspect would go quite a bit fasrer. Try the following:
SELECT bv_books. * ,
vote_avg,
vote_count
FROM bv_bookgenres,
bv_books
WHERE bv_books.book_id IN (
SELECT book_id
FROM bv_genres
WHERE bv_bookgenres.genre_id = 5830
)
AND bv_bookgenres.genre_id = 5830
ORDER BY vote_avg DESC LIMIT 10 OFFSET 0;
In this query, all of the book_id values are pulled at once.
Who knows?
If you get statisctics on this, please post.
Marty