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 44D6F32A0E6 for ; Tue, 28 Sep 2004 04:17:48 +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 40062-08 for ; Tue, 28 Sep 2004 03:17:46 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 747CB32A07F for ; Tue, 28 Sep 2004 04:17:45 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i8S3HetZ009618; Mon, 27 Sep 2004 23:17:40 -0400 (EDT) To: "Iain" Cc: "Jim C. Nasby" , pgsql-performance@postgresql.org Subject: Re: Caching of Queries In-reply-to: <00b201c4a4ff$bd73b8b0$7201a8c0@mst1x5r347kymb> References: <20040923152925.18234.qmail@web41114.mail.yahoo.com> <20040927191836.GS1297@decibel.org> <00b201c4a4ff$bd73b8b0$7201a8c0@mst1x5r347kymb> Comments: In-reply-to "Iain" message dated "Tue, 28 Sep 2004 11:06:17 +0900" Date: Mon, 27 Sep 2004 23:17:40 -0400 Message-ID: <9617.1096341460@sss.pgh.pa.us> From: Tom Lane 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/305 X-Sequence-Number: 8433 "Iain" writes: > I can only tell you (roughly) how it works wth Oracle, Which unfortunately has little to do with how it works with Postgres. This "latches" stuff is irrelevant to us. In practice, any repetitive planning in PG is going to be consulting catalog rows that it draws from the backend's local catalog caches. After the first read of a given catalog row, the backend won't need to re-read it unless the associated table has a schema update. (There are some other cases, like a VACUUM FULL of the catalog the rows came from, but in practice catalog cache entries don't change often in most scenarios.) We need place only one lock per table referenced in order to interlock against schema updates; not one per catalog row used. The upshot of all this is that any sort of shared plan cache is going to create substantially more contention than exists now --- and that's not even counting the costs of managing the cache, ie deciding when to throw away entries. A backend-local plan cache would avoid the contention issues, but would of course not allow amortizing planning costs across multiple backends. I'm personally dubious that sharing planning costs is a big deal. Simple queries generally don't take that long to plan. Complicated queries do, but I think the reusability odds go down with increasing query complexity. regards, tom lane