From: | johnnnnnn <john(at)phaedrusdeinus(dot)org> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org, Chris Faulkner <chrisf(at)oramap(dot)com> |
Subject: | Re: sql performance and cache |
Date: | 2003-10-13 16:19:59 |
Message-ID: | 20031013161959.GA29900@performics.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | James Rogers | 2003-10-13 18:43:43 | Re: One or more processor ? |
Previous Message | Vivek Khera | 2003-10-13 15:52:44 | Re: Performance, vacuum and reclaiming space, fsm |
From | Date | Subject | |
---|---|---|---|
Next Message | Ian Barwick | 2003-10-13 17:15:21 | Re: [postgres] Antwort von Microsoft auf Mail hier! |
Previous Message | Marc G. Fournier | 2003-10-13 14:04:33 | removing precision from timestamp (microseconds) .. |