X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D654FDA50B for ; Fri, 7 Oct 2005 10:07:18 -0300 (ADT) 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 01920-07 for ; Fri, 7 Oct 2005 13:07:10 +0000 (GMT) Received: from mx1.surnet.cl (mx2.surnet.cl [216.155.73.181]) by svr1.postgresql.org (Postfix) with ESMTP id F07D7DA56C for ; Fri, 7 Oct 2005 10:07:11 -0300 (ADT) Received: from unknown (HELO cluster.surnet.cl) ([216.155.73.165]) by mx1.surnet.cl with ESMTP; 07 Oct 2005 09:06:46 -0400 Received: from alvh.no-ip.org (200.85.218.198) by cluster.surnet.cl (7.0.043) (authenticated as alvherre@surnet.cl) id 433A601F002215BB; Fri, 7 Oct 2005 09:07:15 -0400 Received: by alvh.no-ip.org (Postfix, from userid 1000) id 1D5E0C2DC18; Fri, 7 Oct 2005 09:07:15 -0400 (CLT) Date: Fri, 7 Oct 2005 09:07:15 -0400 From: Alvaro Herrera To: Cestmir Hybl Cc: "Steinar H. Gunderson" , pgsql-performance@postgresql.org Subject: Re: count(*) using index scan in "query often, update rarely" environment Message-ID: <20051007130714.GB5175@surnet.cl> Mail-Followup-To: Cestmir Hybl , "Steinar H. Gunderson" , pgsql-performance@postgresql.org References: <935801c5cb20$dcf93a00$131fc39e@stratos> <20051007104816.GA8371@uio.no> <9fbe01c5cb30$4407d580$131fc39e@stratos> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <9fbe01c5cb30$4407d580$131fc39e@stratos> User-Agent: Mutt/1.5.10i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.495 required=5 tests=[AWL=-0.305, DNS_FROM_RFC_ABUSE=0.374, DNS_FROM_RFC_POST=1.376, FORGED_RCVD_HELO=0.05] X-Spam-Level: * X-Archive-Number: 200510/140 X-Sequence-Number: 14908 On Fri, Oct 07, 2005 at 01:14:20PM +0200, Cestmir Hybl wrote: > collision: it's possible to either block updating transaction until > index scan ends or discard index scan imediately and finish query using > MVCC compliant scan You can't change from one scan method to a different one on the fly. There's no way to know which tuples have alreaady been returned. Our index access methods are designed to be very concurrent, and it works extremely well. One index scan being able to block an update would destroy that advantage. > dead rows: this sounds like more serious counter-argument, I don't know > much about dead records management and whether it would be > possible/worth to make indexes matching live records when there's no > transaction in progress on that table It's not possible, because a finishing transaction would have to clean up every index it has used, and also any index it hasn't used but has been modified by another transaction which couldn't clean up by itself but didn't do the work because the first one was looking at the index. It's easy to see that it's possible to create an unbounded number of transactions, each forcing the other to do some index cleanup. This is not acceptable. Plus, it would be very hard to implement, and a very wide door to bugs. -- Alvaro Herrera http://www.advogato.org/person/alvherre "Et put se mouve" (Galileo Galilei)