If you'll be so kind though, I'd be glad if you could spot anything to speed up in this query. Here's the query and its plan that happens without any caching:
-------------------------------------------------------------------------------------------------------------
QUERY
-----
SELECT bv_books. * ,
vote_avg, vote_count FROM bv_bookgenres, bv_books WHERE bv_books.book_id = bv_bookgenres.book_id AND bv_bookgenres.genre_id = 5830
ORDER BY vote_avg DESC LIMIT 10 OFFSET 0; QUERY PLAN
----------
Limit (cost=2337.41..2337.43 rows=10 width=76) (actual time=7875.000..7875.000 rows=10 loops=1)
-> Sort (cost=2337.41..2337.94 rows=214 width=76) (actual time=7875.000..7875.000 rows=10 loops=1)
Sort Key: bv_books.vote_avg
-> Nested Loop (cost=0.00..2329.13 rows=214 width=76) (actual time=16.000..7844.000 rows=1993 loops=1)
-> Index Scan using i_bookgenres_genre_id on bv_bookgenres (cost=0.00..1681.54 rows=214 width=4) (actual time=16.000..3585.000 rows=1993 loops=1)
Index Cond: (genre_id = 5830)
-> Index Scan using bv_books_pkey on bv_books (cost=0.00..3.01 rows=1 width=76) (actual time=2.137..2.137 rows=1 loops=1993)
Index Cond: (bv_books.book_id = "outer".book_id)
Total runtime: 7875.000 ms
Presuming that vote_avg is a field in the table bv_bookgenres, try a composite index on genre_id and vote_avg and then see if you can use the limit clause to reduce the number of loop iterations from 1993 to 10.
CREATE INDEX test_idx ON bv_bookgenres (genre_id, vote_avg);
The following query tries to force that execution lan and, presuming there is a foreign key relation between bv_books.book_id AND bv_bookgenres.book_id, I expect it will give the same results, but be carefull with NULL's:
SELECT bv_books. * ,
vote_avg,
vote_count
FROM (
SELECT bg.*
FROM bv_bookgenres bg
WHERE bg.genre_id = 5830
ORDER BY
bg.vote_avg DESC
LIMIT 10
) bv_bookgenres,
bv_books
WHERE bv_books.book_id = bv_bookgenres.book_id
ORDER BY
vote_avg DESC
LIMIT 10;Jochem
-- I don't get it immigrants don't work and steal our jobs - Loesje
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
