X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP
	id 919A6D1B50D; Sat, 11 Oct 2003 10:12:39 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024)
	with ESMTP id 31948-06; Sat, 11 Oct 2003 07:12:00 -0300 (ADT)
Received: from relay01.kbs.net.au (149.32.220.203.comindico.com.au
	[203.220.32.149]) by svr1.postgresql.org (Postfix) with ESMTP
	id AFC19D1B516; Sat, 11 Oct 2003 07:11:52 -0300 (ADT)
Received: from [203.221.127.74] (helo=familyhealth.com.au)
	by relay01.kbs.net.au with esmtp (Exim 3.36 #1)
	id 1A8Gj6-0001IU-00; Sat, 11 Oct 2003 20:11:56 +1000
Message-ID: <3F87D764.8030306@familyhealth.com.au>
Date: Sat, 11 Oct 2003 18:11:48 +0800
From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.5b) Gecko/20030901 Thunderbird/0.2
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Chris Faulkner <chrisf@oramap.com>
Cc: Pgsql-Performance <pgsql-performance@postgresql.org>,
	Pgsql-Sql <pgsql-sql@postgresql.org>
Subject: Re: sql performance and cache
References: <DGENKIKMJILAAKJGFHKFEEKECIAA.chrisf@oramap.com>
In-Reply-To: <DGENKIKMJILAAKJGFHKFEEKECIAA.chrisf@oramap.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/384
X-Sequence-Number: 4132


> I have two very similar queries which I need to execute. They both have
> exactly the same from / where conditions. When I execute the first, it takes
> about 16 seconds. The second is executed almost immediately after, it takes
> 13 seconds. In short, I'd like to know why the query result isn't being
> cached and any ideas on how to improve the execution.

<snip>

> OK - so I could execute the query once, and get the maximum size of the
> array and the result set in one. I know what I am doing is less than optimal
> but I had expected the query results to be cached. So the second execution
> would be very quick. So why aren't they ? I have increased my cache size -
> shared_buffers is 2000 and I have doubled the default max_fsm... settings
> (although I am not sure what they do). sort_mem is 8192.

PostgreSQL does not have, and has never had a query cache - so nothing 
you do is going to make that second query faster.

Perhaps you are confusing it with the MySQL query cache?

Chris