>> 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.
Youcould either discard or register them to the query cache storage depending on the transaction's fate, either aborted
orcommitted.
- 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
crashrecovery. 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