From: Marty Scholes <marty@outputservices.com>
To: Vitaly Belman <vitalib@012.net.il>
Cc: Jochem van Dieten <jochemd@oli.tudelft.nl>
Cc: pgsql-performance@postgresql.org, Marty Scholes <marty@outputservices.com>
Subject: Re: PostgreSQL caching
Date: Tue, 25 May 2004 16:24:18 -0600
Message-ID: <40B3C792.3020106@outputservices.com> (raw)
References: <20792805375.20040521203337@012.net.il>
<40B36848.1090403@oli.tudelft.nl>
<1371146711781.20040525225305@012.net.il>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: marty@outputservices.com, vitalib@012.net.il, jochemd@oli.tudelft.nl
Subject: Re: PostgreSQL caching
In-Reply-To: <40B3C792.3020106@outputservices.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox