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

Re: PostgreSQL caching

From: Marty Scholes <marty(at)outputservices(dot)com>
To: Vitaly Belman <vitalib(at)012(dot)net(dot)il>
Cc: Jochem van Dieten <jochemd(at)oli(dot)tudelft(dot)nl>,pgsql-performance(at)postgresql(dot)org,Marty Scholes <marty(at)outputservices(dot)com>
Subject: Re: PostgreSQL caching
Date: 2004-05-25 22:24:18
Message-ID: 40B3C792.3020106@outputservices.com (view raw or flat)
Thread:
Lists: pgsql-performance
Vitaly,

This looks like there might be some room for performance improvement...

 > MS> I didn't see the table structure, but I assume
 > MS> that the vote_avg and
 > MS> vote_count fields are in bv_bookgenres.
 >
 > I didn't understand you. vote_avg is stored in bv_books.

Ok.  That helps.  The confusion (on my end) came from the SELECT clause 
of the query you provided:

 > SELECT     bv_books. * ,
 >            vote_avg,
 >            vote_count

All fields from bv_books were selected (bv_books.*) along with vote_agv 
and vote_count.  My assumption was that vote_avg and vote_count were 
therefore not in bv_books.

At any rate, a query with an IN clause should help quite a bit:

SELECT     bv_books. *
FROM       bv_books
WHERE      bv_books.book_id IN (
               SELECT book_id
               FROM bv_genres
               WHERE bv_bookgenres.genre_id = 5830
               )
ORDER BY   vote_avg DESC LIMIT 10 OFFSET 0;

Give it a whirl.

Marty


In response to

Responses

pgsql-performance by date

Next:From: Robert TreatDate: 2004-05-26 13:13:58
Subject: Re: PostgreSQL caching
Previous:From: Robert TreatDate: 2004-05-25 21:07:51
Subject: Re: Interpreting vmstat

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