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 88EF9528AD for ; Tue, 24 May 2005 14:28:31 -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 76872-09 for ; Tue, 24 May 2005 17:28:30 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.197.194]) by svr1.postgresql.org (Postfix) with SMTP id A7A6752896 for ; Tue, 24 May 2005 14:28:29 -0300 (ADT) Received: (qmail 10008 invoked by uid 500); 24 May 2005 17:27:13 -0000 Date: Tue, 24 May 2005 12:27:13 -0500 From: Bruno Wolff III To: mark durrant Cc: pgsql-performance@postgresql.org Subject: Re: Select performance vs. mssql Message-ID: <20050524172713.GC8129@wolff.to> Mail-Followup-To: Bruno Wolff III , mark durrant , pgsql-performance@postgresql.org References: <20050524153637.64525.qmail@web53101.mail.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20050524153637.64525.qmail@web53101.mail.yahoo.com> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.009 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/362 X-Sequence-Number: 12526 On Tue, May 24, 2005 at 08:36:36 -0700, mark durrant wrote: > > --MSSQL's ability to hit the index only and not having > to go to the table itself results in a _big_ > performance/efficiency gain. If someone who's in > development wants to pass this along, it would be a > nice addition to PostgreSQL sometime in the future. > I'd suspect that as well as making one query faster, > it would make everything else faster/more scalable as > the server load is so much less. This gets brought up a lot. The problem is that the index doesn't include information about whether the current transaction can see the referenced row. Putting this information in the index will add significant overhead to every update and the opinion of the developers is that this would be a net loss overall.