Message-ID: <40B36848.1090403@oli.tudelft.nl>
Date: Tue, 25 May 2004 17:37:44 +0200
From: Jochem van Dieten <jochemd@oli.tudelft.nl>
Organization: OnLine Internet
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.6b) Gecko/20031205 Thunderbird/0.4
MIME-Version: 1.0
To: Vitaly Belman <vitalib@012.net.il>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL caching
References: <20792805375.20040521203337@012.net.il>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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 = &quot;outer&quot;.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