On Saturday 11 October 2003 10:43, Chris Faulkner wrote:
> 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 short answer is that PG doesn't cache query results. The only way it could 
do so safely is to lock all tables you access to make sure that no other 
process changes them. That would effectively turn PG into a single-user DB in 
short notice.

> 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.

I assume these two queries are linked? If you rely on the max size being 
unchanged and have more than one process using the database, you should make 
sure you lock the rows in question.

> 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.

PG will cache the underlying data, but not the results. The values you are 
changing are used to hold table/index rows etc. This means the second query 
shouldn't need to access the disk if the rows it requires are cached.

There is a discussion of the postgresql.conf file and how to tune it at:

Given the explain attached, 16 secs seems slow. Could you post an EXPLAIN 
ANALYSE of either/both queries to the performance list. I'd drop the sql list 
when we're just talking about performance.
  Richard Huxton
  Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?


Reply via email to