X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 1E07ED1BA8F for ; Wed, 26 May 2004 10:14:50 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 53317-07 for ; Wed, 26 May 2004 10:14:31 -0300 (ADT) Received: from bramble.mmrd.com (unknown [65.217.53.66]) by svr1.postgresql.org (Postfix) with ESMTP id 78ABBD1B23F for ; Wed, 26 May 2004 10:14:26 -0300 (ADT) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by bramble.mmrd.com (8.12.8/8.12.8) with ESMTP id i4QBbr9U013418; Wed, 26 May 2004 07:37:54 -0400 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [10.225.10.110]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id i4QDDxN13289; Wed, 26 May 2004 09:14:00 -0400 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2657.72) id LP85GDKP; Wed, 26 May 2004 09:13:58 -0400 Subject: Re: PostgreSQL caching From: Robert Treat To: Vitaly Belman Cc: Jochem van Dieten , Marty Scholes , pgsql-performance@postgresql.org In-Reply-To: <1371146711781.20040525225305@012.net.il> References: <20792805375.20040521203337@012.net.il> <40B36848.1090403@oli.tudelft.nl> <1371146711781.20040525225305@012.net.il> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 26 May 2004 09:13:58 -0400 Message-Id: <1085577239.29461.1927.camel@camel> Mime-Version: 1.0 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/210 X-Sequence-Number: 7041 On Tue, 2004-05-25 at 15:53, Vitaly Belman wrote: > >> > >> 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 > A question and two experiments... what version of postgresql is this? Try reindexing i_bookgenres_genre_id and capture the explain analyze for that. If it doesn't help try doing set enable_indexscan = false and capture the explain analyze for that. Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL