Re: query cache

>> Well, you'd have to start by demonstrating the benefit of it. The
>> advantage of query caches in proxies and clients is well-known, because you
>> can offload some of the work of the database onto other servers, this
>> increasing capacity. Adding a query cache to the database server would
>> require the "query identity recognition" of the cache to be far cheaper (as
>> in 10X cheaper) than planning and running the query, which seems unlikely
>> at best.
>>
>> I figured I'd create the md5 digest of the sourceText of a query, and then
> look that up in a hash. I don't think that will be very expensive. I'll
> have another hash to keep track of which queries are dependent on which
> relations, so that when a relation is changed somehow (and committed), the
> query is then invalidated and removed from the query hash.

From the experience of implementing query cache in pgool-II there are
some suggestions:

- A query result cache should not be created if the transaction
including the SELECT is not committed.

- Since a transaction could have many SELECTs, you need to keep those
query results somewhere in a temporary storage. You could either
discard or register them to the query cache storage depending on the
transaction's fate, either aborted or committed.

- If a SELECT has non-immutable functions, then the query result
should not be cached.

- If a SELECT uses temporary tables, then the query result should not
be cached.

- If a SELECT uses unlogged tables, then the query result should not
be cached because their data could vanish after crash recovery. Of
course this is only applied if you plan to use cache storage which
does not survive after crash.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp