pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bruce Momjian <pgman@candle.pha.pa.us>
To: J. Andrew Rogers <jrogers@neopolitan.com>
Cc: Magnus Hagander <mha@sollentuna.net>
Cc: pgsql-performance@postgresql.org
Cc: mischa.sandberg@telus.net
Subject: Re: Equivalent praxis to CLUSTERED INDEX?
Date: Thu, 26 Aug 2004 21:45:50 -0400 (EDT)
Message-ID: <200408270145.i7R1jop04185@candle.pha.pa.us> (raw)
In-Reply-To: <1093553043.349.171.camel@vulture.corp.neopolitan.com>


Updated TODO item:

        o Automatically maintain clustering on a table

        This would require some background daemon to maintain clustering
        during periods of low usage. It might also require tables to be only
        paritally filled for easier reorganization.  It also might require
        creating a merged heap/index data file so an index lookup would
        automatically access the heap data too.

---------------------------------------------------------------------------

J. Andrew Rogers wrote:
> 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
> 
> 
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



view thread (41+ messages)  latest in thread

Message-ID: <200408270145.i7R1jop04185@candle.pha.pa.us>
Permalink:  ../200408270145.i7R1jop04185@candle.pha.pa.us/
Also on:    postgresql.org/message-id/200408270145.i7R1jop04185@candle.pha.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: pgman@candle.pha.pa.us, jrogers@neopolitan.com, mha@sollentuna.net, mischa.sandberg@telus.net
  Subject: Re: Equivalent praxis to CLUSTERED INDEX?
  In-Reply-To: <200408270145.i7R1jop04185@candle.pha.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox