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 510AA329ED6 for ; Mon, 27 Sep 2004 13:59:36 +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 58215-05 for ; Mon, 27 Sep 2004 12:59:28 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id CA8BC329ED5 for ; Mon, 27 Sep 2004 13:59:31 +0100 (BST) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1CBv6G-0004Sc-00; Mon, 27 Sep 2004 08:59:28 -0400 To: Neil Conway Cc: Aaron Werman , Scott Kirkwood , pgsql-performance@postgresql.org Subject: Re: Caching of Queries References: <1096261381.25688.741.camel@localhost.localdomain> In-Reply-To: <1096261381.25688.741.camel@localhost.localdomain> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 27 Sep 2004 08:59:27 -0400 Message-ID: <87fz5398b4.fsf@stark.xeocode.com> Lines: 29 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii 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: 200409/279 X-Sequence-Number: 8407 Neil Conway writes: > I think the conclusion of past discussions about this feature is that > it's a bad idea. Last I checked, MySQL has to clear the *entire* query > cache when a single DML statement modifying the table in question is > issued. Not to mention that the feature is broken for non-deterministic > queries (like now(), ORDER BY random(), or nextval('some_seq'), and so > on). That makes the feature close to useless for a lot of situations, > albeit not every situation. Well there's no reason to assume that just because other implementations are weak that postgres would have to slavishly copy them. I've often wondered whether it would make sense to cache the intermediate results in queries. Any time there's a Materialize node, the database is storing all those data somewhere; it could note the plan and parameters that generated the data and reuse them if it sees the same plan and parameters -- including keeping track of whether the source tables have changed or whether there were any non-immutable functions of course. This could be quite helpful as people often do a series of queries on the same basic data. Things like calculating the total number of records matching the user's query then fetching only the records that fit on the current page. Or fetching records for a report then having to calculate subtotals and totals for that same report. Or even generating multiple reports breaking down the same data along different axes. -- greg