From: Jochem van Dieten <jochemd@oli.tudelft.nl>
To: Vitaly Belman <vitalib@012.net.il>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL caching
Date: Tue, 25 May 2004 17:37:44 +0200
Message-ID: <40B36848.1090403@oli.tudelft.nl> (raw)
References: <20792805375.20040521203337@012.net.il>
Vitaly Belman wrote:
>
> If you'll be so kind though, I'd be glad if you could spot anything to
> speed up in this query. Here's the query and its plan that happens
> without any caching:
>
> -------------------------------------------------------------------------------------------------------------
> QUERY
> -----
> SELECT bv_books. * ,
> vote_avg,
> vote_count
> FROM bv_bookgenres,
> bv_books
> WHERE bv_books.book_id = bv_bookgenres.book_id AND
> bv_bookgenres.genre_id = 5830
> ORDER BY vote_avg DESC LIMIT 10 OFFSET 0;
>
> 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
Presuming that vote_avg is a field in the table bv_bookgenres,
try a composite index on genre_id and vote_avg and then see if
you can use the limit clause to reduce the number of loop
iterations from 1993 to 10.
CREATE INDEX test_idx ON bv_bookgenres (genre_id, vote_avg);
The following query tries to force that execution lan and,
presuming there is a foreign key relation between
bv_books.book_id AND bv_bookgenres.book_id, I expect it will give
the same results, but be carefull with NULL's:
SELECT bv_books. * ,
vote_avg,
vote_count
FROM (
SELECT bg.*
FROM bv_bookgenres bg
WHERE bg.genre_id = 5830
ORDER BY
bg.vote_avg DESC
LIMIT 10
) bv_bookgenres,
bv_books
WHERE bv_books.book_id = bv_bookgenres.book_id
ORDER BY
vote_avg DESC
LIMIT 10;
Jochem
--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
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: jochemd@oli.tudelft.nl, vitalib@012.net.il
Subject: Re: PostgreSQL caching
In-Reply-To: <40B36848.1090403@oli.tudelft.nl>
* 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