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 4BA6732A08F for ; Tue, 28 Sep 2004 15:37:04 +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 58603-06 for ; Tue, 28 Sep 2004 14:36:58 +0000 (GMT) Received: from hotmail.com (bay18-dav2.bay18.hotmail.com [65.54.187.182]) by svr1.postgresql.org (Postfix) with ESMTP id 4173F329FFE for ; Tue, 28 Sep 2004 15:37:01 +0100 (BST) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Tue, 28 Sep 2004 07:37:01 -0700 Received: from 67.81.98.198 by bay18-dav2.bay18.hotmail.com with DAV; Tue, 28 Sep 2004 14:36:02 +0000 X-Originating-IP: [67.81.98.198] X-Originating-Email: [awerman2@hotmail.com] X-Sender: awerman2@hotmail.com From: "Aaron Werman" To: "Tom Lane" Cc: References: <20040923152925.18234.qmail@web41114.mail.yahoo.com> <20040927191836.GS1297@decibel.org> <00b201c4a4ff$bd73b8b0$7201a8c0@mst1x5r347kymb> <9617.1096341460@sss.pgh.pa.us> <14083.1096379881@sss.pgh.pa.us> Subject: Re: Caching of Queries Date: Tue, 28 Sep 2004 10:36:03 -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: 28 Sep 2004 14:37:01.0307 (UTC) FILETIME=[9DA27CB0:01C4A568] 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/311 X-Sequence-Number: 8439 ----- Original Message ----- From: "Tom Lane" To: "Aaron Werman" Cc: "Iain" ; "Jim C. Nasby" ; Sent: Tuesday, September 28, 2004 9:58 AM Subject: Re: [PERFORM] Caching of Queries > "Aaron Werman" writes: > > I imagine a design where a shared plan cache would consist of the plans, > > indexed by a statement hash and again by dependant objects. A statement to > > be planned would be hashed and matched to the cache. DDL would need to > > synchronously destroy all dependant plans. If each plan maintains a validity > ^^^^^^^^^^^^^ > > flag, changing the cache wouldn't have to block so I don't see where there > ^^^^^^^^^^^^^^^^^^^^^^ > > would be contention. > > You have contention to access a shared data structure *at all* -- for > instance readers must lock out writers. Or didn't you notice the self- > contradictions in what you just said? > > Our current scalability problems dictate reducing such contention, not > adding whole new sources of it. You're right - that seems unclear. What I meant is that there can be a global hash table that is never locked, and the hashes point to chains of plans that are only locally locked for maintenance, such as gc and chaining hash collisions. If maintenance was relatively rare and only local, my assumption is that it wouldn't have global impact. The nice thing about plan caching is that it can be sloppy, unlike block cache, because it is only an optimization tweak. So, for example, if the plan has atomic refererence times or counts there is no need to block, since overwriting is not so bad. If the multiprocessing planner chains the same plan twice, the second one would ultimately age out.... /Aaron > > regards, tom lane >