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 B109B5E46F3 for ; Thu, 26 Aug 2004 17:44:07 -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 13621-02 for ; Thu, 26 Aug 2004 20:44:04 +0000 (GMT) Received: from mx1.neopolitan.us (mx1.neopolitan.us [65.87.16.224]) by svr1.postgresql.org (Postfix) with ESMTP id B24E75E46EE for ; Thu, 26 Aug 2004 17:44:01 -0300 (ADT) Received: from [65.87.16.98] (HELO vulture.corp.neopolitan.com) by mx1.neopolitan.us (CommuniGate Pro SMTP 4.1.8) with ESMTP-TLS id 5617072; Thu, 26 Aug 2004 13:44:03 -0700 Subject: Re: Equivalent praxis to CLUSTERED INDEX? From: "J. Andrew Rogers" To: Magnus Hagander Cc: Bruce Momjian , pgsql-performance@postgresql.org, mischa.sandberg@telus.net In-Reply-To: <6BCB9D8A16AC4241919521715F4D8BCE475B39@algol.sollentuna.se> References: <6BCB9D8A16AC4241919521715F4D8BCE475B39@algol.sollentuna.se> Content-Type: text/plain Organization: Message-Id: <1093553043.349.171.camel@vulture.corp.neopolitan.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 (1.2.2-5) Date: 26 Aug 2004 13:44:03 -0700 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/378 X-Sequence-Number: 8014 On Thu, 2004-08-26 at 12:30, Magnus Hagander wrote: > Almost the same for MSSQL. The clustered index is always forced unique. > If you create a non-unique clustered index, SQLServer will internally > pad it with random (or is it sequential? Can't remember right now) data > to make each key unique. The clustered index contains all the data > fields - both the index key and the other columns from the database. > > It does support non-clustered indexes as well on the same table. Any > "secondary index" will then contain the index key and the primary key > value. This means a lookup in a non-clustered index means a two-step > index lookup: First look in the non-clustered index for the clustered > key. Then look in the clustered index for the rest of the data. Ah, okay. I see how that would work for a secondary index, though it would make for a slow secondary index. Neat workaround. For all I know, current versions of Oracle may support secondary indexes on index-organized tables; all this Postgres usage over the last couple years has made my Oracle knowledge rusty. > IIRC, SQL Server always creates clustered indexes by default for primary > keys. That would surprise me actually. For some types of tables, e.g. ones with multiple well-used indexes or large rows, index-organizing the heap could easily give worse performance than a normal index/heap pair depending on access patterns. It also tends to be more prone to having locking contention under some access patterns. This is one of those options that needs to be used knowledgeably; it is not a general architectural improvement that you would want to apply to every table all the time. J. Andrew Rogers