Re: query cache - Mailing list pgsql-hackers

Thanks..  I'll keep those issues in mind.<br /><br /><div class="gmail_quote">On Sat, Mar 24, 2012 at 6:18 PM, Tatsuo
Ishii<span dir="ltr"><<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>></span> wrote:<br
/><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div
class="im">>>Well, you'd have to start by demonstrating the benefit of it.  The<br /> >> advantage of query
cachesin proxies and clients is well-known, because you<br /> >> can offload some of the work of the database
ontoother servers, this<br /> >> increasing capacity.  Adding a query cache to the database server would<br />
>>require the "query identity recognition" of the cache to be far cheaper (as<br /> >> in 10X cheaper) than
planningand running the query, which seems unlikely<br /> >> at best.<br /> >><br /> >> I figured I'd
createthe md5 digest of the sourceText of a query, and then<br /> > look that up in a hash.  I don't think that will
bevery expensive.  I'll<br /> > have another hash to keep track of which queries are dependent on which<br /> >
relations,so that when a relation is changed somehow (and committed), the<br /> > query is then invalidated and
removedfrom the query hash.<br /><br /></div>From the experience of implementing query cache in pgool-II there are<br
/>some suggestions:<br /><br /> - A query result cache should not be created if the transaction<br />  including the
SELECTis not committed.<br /><br /> - Since a transaction could have many SELECTs, you need to keep those<br />  query
resultssomewhere in a temporary storage. You could either<br />  discard or register them to the query cache storage
dependingon the<br />  transaction's fate, either aborted or committed.<br /><br /> - If a SELECT has non-immutable
functions,then the query result<br />  should not be cached.<br /><br /> - If a SELECT uses temporary tables, then the
queryresult should not<br />  be cached.<br /><br /> - If a SELECT uses unlogged tables, then the query result should
not<br/>  be cached because their data could vanish after crash recovery. Of<br />  course this is only applied if you
planto use cache storage which<br />  does not survive after crash.<br /> --<br /> Tatsuo Ishii<br /> SRA OSS, Inc.
Japan<br/> English: <a href="http://www.sraoss.co.jp/index_en.php"
target="_blank">http://www.sraoss.co.jp/index_en.php</a><br/> Japanese: <a href="http://www.sraoss.co.jp"
target="_blank">http://www.sraoss.co.jp</a><br/></blockquote></div><br />