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 38F093A5FC3 for ; Fri, 28 Jan 2005 20:46:22 +0000 (GMT) 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 01470-09 for ; Fri, 28 Jan 2005 20:46:16 +0000 (GMT) Received: from sccrmhc11.comcast.net (sccrmhc11.comcast.net [204.127.202.55]) by svr1.postgresql.org (Postfix) with ESMTP id 6DEA83A5DE7 for ; Fri, 28 Jan 2005 20:46:15 +0000 (GMT) Received: from sysexperts.com (c-24-6-183-218.client.comcast.net[24.6.183.218]) by comcast.net (sccrmhc11) with ESMTP id <2005012820461201100ch5i7e>; Fri, 28 Jan 2005 20:46:12 +0000 Received: from localhost (localhost [127.0.0.1]) (uid 1000) by filer with local; Fri, 28 Jan 2005 12:46:10 -0800 id 0004103A.41FAA492.00000CB3 Date: Fri, 28 Jan 2005 12:46:10 -0800 From: Kevin Brown To: pgsql-performance@postgresql.org Subject: Re: PostgreSQL clustering VS MySQL clustering Message-ID: <20050128204610.GB31166@filer> Mail-Followup-To: Kevin Brown , pgsql-performance@postgresql.org References: <41F144FE.30902@outputservices.com> <41F14758.4030602@outputservices.com> <20050122.121300.41631131.t-ishii@sra.co.jp> <25335.1106428223@sss.pgh.pa.us> <1106511352.31592.112.camel@localhost.localdomain> <6346.1106512803@sss.pgh.pa.us> <20050125031104.GW67721@decibel.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline In-Reply-To: Organization: Frobozzco International User-Agent: Mutt/1.5.6+20040907i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200501/597 X-Sequence-Number: 10287 PFC wrote: > So, here is something annoying with the current approach : Updating rows > in a table bloats ALL indices, not just those whose indexed values have > been actually updated. So if you have a table with many indexed fields and > you often update some obscure timestamp field, all the indices will bloat, > which will of course be corrected by VACUUM, but vacuum will have extra > work to do. The MVCC approach probably doesn't leave you with many choices here. The index entries point directly to the rows in the table, and since an update creates a new row (it's the equivalent of doing an insert then a delete), all indexes have to be updated to reflect the location of the new row. Unless my understanding of how this works is completely off... -- Kevin Brown kevin@sysexperts.com