Re: PostgreSQL caching

On Tue, 2004-05-25 at 15:53, Vitaly Belman wrote:
> >>
> >> 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
>

A question and two experiments... what version of postgresql is this?

Try reindexing i_bookgenres_genre_id and capture the explain analyze for
that. If it doesn't help try doing set enable_indexscan = false and
capture the explain analyze for that.

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL