Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id F23309FB3F9 for ; Mon, 23 Oct 2006 10:51:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 86931-03-2 for ; Mon, 23 Oct 2006 13:50:31 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from lists.commandprompt.com (host-130.commandprompt.net [207.173.203.130]) by postgresql.org (Postfix) with ESMTP id BF8169FB424 for ; Mon, 23 Oct 2006 10:50:30 -0300 (ADT) Received: from alvh.no-ip.org (200-126-100-46.bk7-dsl.surnet.cl [200.126.100.46]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id k9NDoD7V016057; Mon, 23 Oct 2006 06:50:20 -0700 Received: by alvh.no-ip.org (Postfix, from userid 1000) id 5E77DC2DC0D; Mon, 23 Oct 2006 10:50:11 -0300 (CLST) Date: Mon, 23 Oct 2006 10:50:11 -0300 From: Alvaro Herrera To: PostgreSQL Performance List Cc: =?iso-8859-1?Q?P=E9ter_Kov=E1cs?= Subject: Re: Index on two columns not used Message-ID: <20061023135011.GG7961@alvh.no-ip.org> Mail-Followup-To: PostgreSQL Performance List , =?iso-8859-1?Q?P=E9ter_Kov=E1cs?= References: <45360E25.9010403@freesurf.fr> <45361295.9050504@enterprisedb.com> <4539F4E1.7000003@chemaxon.hu> <453CC2DD.4070608@logix-tt.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <453CC2DD.4070608@logix-tt.com> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: ClamAV version 0.88.4, clamav-milter version 0.88.4 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Mon, 23 Oct 2006 06:50:21 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.135 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200610/475 X-Sequence-Number: 21604 Markus Schaber wrote: > Additionally, in most UPDATE cases, the new row version will fit into > the same page as the old version. In this case, the index does not have > to be changed, which is an additional speed improvement. Actually, when the UPDATE puts a new row version in the same heap page, the index must be updated anyway. All the rest of what you said is correct. There is another reason not to put visibility info in the index, which is that it would be extremely complex to update all indexes to contain the right visibility (and maybe impossible without risking deadlocks). Updating only the heap is very simple. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.