Re: [SQL] [PERFORM] sql performance and cache
On Tue, 14 Oct 2003, Wei Weng wrote: On Sat, 11 Oct 2003, Christopher Kings-Lynne 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. 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 Is there plan on developing one (query cache)? Not really, Postgresql's design makes it a bit of a non-winner. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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
[PERFORM] sql performance and cache
Hello all
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 first query attempts to find the maximum size of an array in the result
set- the field is called level. IT contains anything between 1 and 10
integers. I just need to know what the largest size is. I do this to find
out the maximum size of the level array.
max(replace(split_part(array_dims(level),':',2),']','')::int)
I know this is big and ugly but is there any better way of doing it ?
The second query just returns the result set - it has exactly the same
FROM/Where clause.
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.
The from / where is
FROM oscar_node N, oscar_point P
where N.GEOM_ID_OF_POINT = P.POINT_ID
and N.TILE_REF = P.TILE_REF
and N.TILE_REF in ('TQ27NE','TQ28SE','TQ37NW','TQ38SW')
and P.TILE_REF in ('TQ27NE','TQ28SE','TQ37NW','TQ38SW')
and P.FEAT_CODE = 3500
and P.wkb_geometry GeometryFromText('BOX3D(529540.0 179658.88,530540.0
180307.12)'::box3d,-1)
oscar_node and oscar_point both have about 3m rows. PK on oscar_node is
composite of TILE_REF and NODE_ID. PK on oscar_point is TILE_REF and
POINT_ID. The tables are indexed on feat_code and I have an index on
wkb_geometry. (This is a GIST index). I have increased the statistics size
and done the analyze command.
Here is my explain plan
Nested Loop (cost=0.00..147.11 rows=1 width=148)
Join Filter: (inner.GEOM_ID_OF_POINT = outer.POINT_ID)
- Index Scan using gidx_oscar_point on oscar_point p (cost=0.00..61.34
rows=1 width=57)
Index Cond: (wkb_geometry 'SRID=-1;BOX3D(529540 179658.88
0,530540 180307.12 0)'::geometry)
Filter: (((TILE_REF = 'TQ27NE'::bpchar) OR (TILE_REF =
'TQ28SE'::bpchar) OR (TILE_REF = 'TQ37NW'::bpchar) OR (TILE_REF =
'TQ38SW'::bpchar)) AND (FEAT_CODE = 3500))
- Index Scan using idx_on_tile_ref on oscar_node n (cost=0.00..85.74
rows=2 width=91)
Index Cond: (n.TILE_REF = outer.TILE_REF)
Filter: ((TILE_REF = 'TQ27NE'::bpchar) OR (TILE_REF =
'TQ28SE'::bpchar) OR (TILE_REF = 'TQ37NW'::bpchar) OR (TILE_REF =
'TQ38SW'::bpchar))
I am seeing this message in my logs.
bt_fixroot: not valid old root page
Maybe this is relevant to my performance problems.
I know this has been a long message but I would really appreciate any
performance tips.
Thanks
Chris
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
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. 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
