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 A9EE0329EA8 for ; Mon, 27 Sep 2004 14:00:22 +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 56344-07 for ; Mon, 27 Sep 2004 13:00:12 +0000 (GMT) Received: from mproxy.gmail.com (rproxy.gmail.com [64.233.170.198]) by svr1.postgresql.org (Postfix) with ESMTP id 6157D329DB1 for ; Mon, 27 Sep 2004 14:00:14 +0100 (BST) Received: by mproxy.gmail.com with SMTP id 77so3633678rnk for ; Mon, 27 Sep 2004 06:00:14 -0700 (PDT) Received: by 10.38.171.19 with SMTP id t19mr258615rne; Mon, 27 Sep 2004 06:00:14 -0700 (PDT) Received: by 10.38.73.17 with HTTP; Mon, 27 Sep 2004 06:00:14 -0700 (PDT) Message-ID: Date: Mon, 27 Sep 2004 10:00:14 -0300 From: Scott Kirkwood Reply-To: Scott Kirkwood To: pgsql-performance@postgresql.org Subject: Re: Caching of Queries In-Reply-To: <1096261381.25688.741.camel@localhost.localdomain> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <1096261381.25688.741.camel@localhost.localdomain> 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/280 X-Sequence-Number: 8408 On Mon, 27 Sep 2004 15:03:01 +1000, Neil Conway wrote: > 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. I think it's important to demark three levels of possible caching: 1) Caching of the parsed query tree 2) Caching of the query execute plan 3) Caching of the query results I think caching the query results (3) is pretty dangerous and difficult to do correctly. Caching of the the execute plan (2) is not dangerous but may actually execute more slowly by caching a bad plan (i.e. a plan not suited to the current data) Caching of the query tree (1) to me has very little downsides (except extra coding). But may not have a lot of win either, depending how much time/resources are required to parse the SQL and lookup the objects in the system tables (something I've never gotten a satisfactory answer about). Also, some of the query cache would have to be cleared when DDL statements are performed. -Scott