Re: [PERFORM] sql performance and cache
> > Perhaps you are confusing it with the MySQL query cache? > Is there plan on developing one (query cache)? For the most part, prepared queries and cursors give you a greater advantage due to their versatility -- both of which we do have. In the cases where an actual cache is useful, the client application could do it just as easily or temp tables can be used. I suspect it would be implemented more as a caching proxy than as an actual part of PostgreSQL, should someone really want this feature. signature.asc Description: This is a digitally signed message part
Re: [PERFORM] sql performance and cache
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. -johnn ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] sql performance and cache
PostgreSQL does not have, and has never had a query cache - so nothing you do is going to make that second query faster. Let me clarify that. PostgreSQL will of course cache the disk pages used in getting the data for your query, which is why the second time you run it, it is 3 seconds faster. However, it does not cache the _results_ of the query. Each time you run it, it will be fully re-evaluated. The btree error you give is bad and I'm sure the more experienced list members will want you to dig into it for them. Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] sql performance and cache
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. 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
