pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: J. Andrew Rogers <jrogers@neopolitan.com>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: pgsql-performance@postgresql.org
Cc: mischa.sandberg@telus.net
Subject: Re: Equivalent praxis to CLUSTERED INDEX?
Date: 26 Aug 2004 12:04:48 -0700
Message-ID: <1093547088.349.134.camel@vulture.corp.neopolitan.com> (raw)
In-Reply-To: <200408261818.i7QIIrr09640@candle.pha.pa.us>
References: <200408261818.i7QIIrr09640@candle.pha.pa.us>

On Thu, 2004-08-26 at 11:18, Bruce Momjian wrote:
> How do vendors actually implement auto-clustering?  I assume they move
> rows around during quiet periods or have lots of empty space in each
> value bucket.


As far as I know, Oracle does it by having a B-Tree organized heap (a
feature introduced around v8 IIRC), basically making the primary key
index and the heap the same physical structure.  Any non-index columns
are stored in the index along with the index columns.  Implementing it
is slightly weird because searching the index and selecting the rows
from the heap are not separate operations.

The major caveat to having tables of this type is that you can only have
a primary key index.  No other indexes are possible because the "heap"
constantly undergoes local reorganizations if you have a lot of write
traffic, the same kind of reorganization you would normally expect in a
BTree index.

The performance improvements come from two optimizations.  First, you
have to touch significantly fewer blocks to get all the rows, even
compared to a CLUSTERed heap.  Second, the footprint is smaller and
plays nicely with the buffer cache.

When I've used these types of heaps in Oracle 8 on heavily used tables
with tens of millions of rows, we frequently got a 10x or better
performance improvement on queries against those tables.  It is only
really useful for tables with vast quantities of relatively small rows,
but it can be a lifesaver in those cases.


J. Andrew Rogers





view thread (41+ messages)  latest in thread

Message-ID: <1093547088.349.134.camel@vulture.corp.neopolitan.com>
Permalink:  ../1093547088.349.134.camel@vulture.corp.neopolitan.com/
Also on:    postgresql.org/message-id/1093547088.349.134.camel@vulture.corp.neopolitan.com

 · 

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: jrogers@neopolitan.com, pgman@candle.pha.pa.us, mischa.sandberg@telus.net
  Subject: Re: Equivalent praxis to CLUSTERED INDEX?
  In-Reply-To: <1093547088.349.134.camel@vulture.corp.neopolitan.com>

* 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