From: johnnnnnn <john@phaedrusdeinus.org>
To: pgsql-performance@postgresql.org, Chris Faulkner <chrisf@oramap.com>
Subject: Re: sql performance and cache
Date: Mon, 13 Oct 2003 11:19:59 -0500
Message-ID: <20031013161959.GA29900@performics.com> (raw)
In-Reply-To: <DGENKIKMJILAAKJGFHKFEEKECIAA.chrisf@oramap.com>
References: <DGENKIKMJILAAKJGFHKFEEKECIAA.chrisf@oramap.com>
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
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-sql@postgresql.org
Cc: john@phaedrusdeinus.org, chrisf@oramap.com
Subject: Re: sql performance and cache
In-Reply-To: <20031013161959.GA29900@performics.com>
* 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