Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL caching

From: Marty Scholes <marty(at)outputservices(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: PostgreSQL caching
Date: 2004-05-21 20:10:56
Message-ID: 40AE6250.6070904@outputservices.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


Responses

pgsql-performance by date

Next:From: Vivek KheraDate: 2004-05-21 20:36:18
Subject: Re: Avoiding vacuum full on an UPDATE-heavy table
Previous:From: Matthew T. O'ConnorDate: 2004-05-21 19:22:40
Subject: Re: Avoiding vacuum full on an UPDATE-heavy table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group