Re: [SQL] sql performance and cache

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:
http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php

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