Date: Mon, 13 Oct 2003 11:19:59 -0500
From: johnnnnnn <john@phaedrusdeinus.org>
To: pgsql-performance@postgresql.org,
	Chris Faulkner <chrisf@oramap.com>
Subject: Re: sql performance and cache
Message-ID: <20031013161959.GA29900@performics.com>
References: <DGENKIKMJILAAKJGFHKFEEKECIAA.chrisf@oramap.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <DGENKIKMJILAAKJGFHKFEEKECIAA.chrisf@oramap.com>
User-Agent: Mutt/1.5.1i

On Sat, Oct 11, 2003 at 10:43:04AM +0100, Chris Faulkner wrote:
> 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.

The way to do the type of caching you're talking about, if i
understand you correctly, would be to create a temporary
table. Specifically, create a temporary table with the results of the
second query. Then run a select * on that table (with no where
clause), and follow it with a select max(replace(...)) on the same
table (no where clause).

That guarantees two things:

1- The joins/filters are not parsed and evaluated twice, with the
corresponding disk reads.
2- The data is exactly consistent between the two queries.

Correct me if i misunderstood your problem.

-johnnnnnnnnnn