Thanks.. I'll keep those issues in mind. On Sat, Mar 24, 2012 at 6:18 PM, Tatsuo Ishii <is...@postgresql.org> wrote:
> >> 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 >
