Re: PostgreSQL caching

Vitaly Belman wrote:
>
> 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