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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster