Message-ID: <40AE6250.6070904@outputservices.com>
Date: Fri, 21 May 2004 14:10:56 -0600
From: Marty Scholes <marty@outputservices.com>
User-Agent: Mozilla/5.0 (X11; U; SunOS sun4u; en-US; rv:0.9.9) Gecko/20020517
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL caching
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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