X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D0DAE329FD7 for ; Mon, 27 Sep 2004 21:12:10 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 23171-05 for ; Mon, 27 Sep 2004 20:12:03 +0000 (GMT) Received: from hotmail.com (bay9-dav22.bay9.hotmail.com [64.4.46.79]) by svr1.postgresql.org (Postfix) with ESMTP id 85F89329FD3 for ; Mon, 27 Sep 2004 21:12:01 +0100 (BST) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Mon, 27 Sep 2004 13:12:00 -0700 Received: from 67.81.98.198 by bay9-dav22.bay9.hotmail.com with DAV; Mon, 27 Sep 2004 20:11:54 +0000 X-Originating-IP: [67.81.98.198] X-Originating-Email: [awerman@hotmail.com] X-Sender: awerman@hotmail.com From: "Aaron Werman" To: "Jeff" Cc: References: <1096261381.25688.741.camel@localhost.localdomain> <18766.1096262336@sss.pgh.pa.us> <330532b6040927062938c1d596@mail.gmail.com> Subject: Re: Caching of Queries Date: Mon, 27 Sep 2004 16:11:53 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Message-ID: X-OriginalArrivalTime: 27 Sep 2004 20:12:00.0625 (UTC) FILETIME=[3F594610:01C4A4CE] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/10 X-Sequence-Number: 8486 The context of the discussion was a hack to speed queries against static tables, so MVCC is not relevent. As soon as any work unit against a referenced table commits, the cache is invalid, and in fact the table shouldn't be a candidate for this caching for a while. In fact, this cache would reduce some the MVCC 'select count(*) from us_states' type of horrors. (The attraction of a server side cache is obviously that it could *with no server or app changes* dramatically improve performance. A materialized view is a specialized denormalization-ish mechanism to optimize a category of queries and requires the DBA to sweat the details. It is very hard to cache things stochastically without writing a server. Trigger managed extracts won't help you execute 1,000 programs issuing the query "select sec_level from sec where division=23" each second or a big table loaded monthly.) ----- Original Message ----- From: "Jeff" To: "Mitch Pirtle" Cc: "Aaron Werman" ; "Scott Kirkwood" ; "Neil Conway" ; ; "Tom Lane" Sent: Monday, September 27, 2004 2:25 PM Subject: Re: [PERFORM] Caching of Queries > [ discussion of server side result caching ] > > and lets not forget PG's major fork it will throw into things: MVCC > The results of query A may hold true for txn 1, but not txn 2 and so on > . > That would have to be taken into account as well and would greatly > complicate things. > > It is always possible to do a "poor man"'s query cache with triggers.. > which would just leave you with basically a materialized view. > > -- > Jeff Trout > http://www.jefftrout.com/ > http://www.stuarthamm.net/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >