Skip site navigation (1) Skip section navigation (2)

Re: Table clustering idea

From: "J(dot) Andrew Rogers" <jrogers(at)neopolitan(dot)com>
To: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
Cc: Postgres Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Table clustering idea
Date: 2006-06-27 17:13:03
Message-ID: 10FF0EBD-F9FF-4A32-AD73-D93D1A7D8475@neopolitan.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Jun 27, 2006, at 9:39 AM, Jim C. Nasby wrote:
> I think one of the issues might have been: how will you handle other
> indexes on the table when you can no longer point them at an item  
> (since
> items will need to move to maintain an IOT).


There are clean ways to handle this.  The table is organized on the  
primary key, a typical requirement for IOTs.  Any indexes you add to  
IOT reference the primary key of the heap tuple.  Since the heap and  
PK index are the same thing, external indexes use the PK as the tuple  
identifier.

The only caveat is that this creates performance asymmetries.  IOTs  
have significantly faster access through their primary keys but  
slower external index access since two B-Trees have to be traversed.   
An IOT is typically only used for tables that are only accessed  
through their primary key.  Not supporting external indexes on IOTs  
is a functional implementation (and probably recommended in  
practice), though most real implementations allow external indexes if  
not always in their first version.


J. Andrew Rogers
jrogers(at)neopolitan(dot)com


In response to

pgsql-hackers by date

Next:From: Magnus HaganderDate: 2006-06-27 17:49:46
Subject: Re: SO_SNDBUF size is small on win32?
Previous:From: Tom LaneDate: 2006-06-27 17:01:10
Subject: Re: SO_SNDBUF size is small on win32?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group