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 CD1FC563AA for ; Tue, 15 Mar 2005 09:37:44 +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 67386-08 for ; Tue, 15 Mar 2005 09:37:42 +0000 (GMT) Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net [194.217.242.92]) by svr1.postgresql.org (Postfix) with ESMTP id 33DCE54EE6 for ; Tue, 15 Mar 2005 09:37:41 +0000 (GMT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-34.mail.demon.net with esmtp (Exim 4.42) id 1DB8Uf-000OD3-G2; Tue, 15 Mar 2005 09:37:42 +0000 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id E6CD115A44; Tue, 15 Mar 2005 09:37:40 +0000 (GMT) Message-ID: <4236ACE5.1040004@archonet.com> Date: Tue, 15 Mar 2005 09:37:41 +0000 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tambet Matiisen Cc: pgsql-performance@postgresql.org Subject: Re: One tuple per transaction References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.062 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/295 X-Sequence-Number: 11000 Tambet Matiisen wrote: >> >>Not exactly. The dead tuple in the index will be scanned the >>first time (and its pointed heap tuple as well), then we will >>mark it dead, then next time we came here, we will know that >>the index tuple actually points to a uesless tuple, so we >>will not scan its pointed heap tuple. >> > > > But the dead index tuple will still be read from disk next time? Maybe > really the performance loss will be neglible, but if most of tuples in > your table/index are dead, then it might be significant. When a block is read from disk, any dead tuples in that block will be read in. Vacuum recovers these. > Consider the often suggested solution for speeding up "select count(*) > from table" query: make another table rowcounts and for each of the > original tables add insert and delete triggers to update row count in > rowcounts table. Actually this is standard denormalization technique, > which I use often. For example to ensure that order.total = > sum(order_line.total). This does of course completely destroy concurrency. Since you need to lock the summary table, other clients have to wait until you are done. > Now, if typical inserts into your most active table occur in batches of > 3 rows, in one transaction, then row count for this table is updated 3 > times during transaction. 3 updates generate 3 tuples, while 2 of them > are dead from the very start. You effectively commit 2 useless tuples. > After millions of inserts you end up with rowcounts table having 2/3 of > dead tuples and queries start to slow down. > > Current solution is to vacuum often. My proposal was to create new tuple > only with first update. The next updates in the same transaction would > update the existing tuple, not create a new. How do you roll back to a savepoint with this model? -- Richard Huxton Archonet Ltd