Re: CLUSTER and clustered indices

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: CLUSTER and clustered indices
Date: 2005-11-21 23:17:51
Message-ID: 20051121231751.GQ19279@pervasive.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

+1, and I know Sybase had this in 11.0.3, which IIRC is over 10 years
old now.

BTW,
http://archives.postgresql.org/pgsql-performance/2004-08/msg00492.php is
one discussion about this from the past. I seem to recall that there was
an objection to true Index Organized Tables because it would be too
dificult to make that work with MVCC. If that's the case then what I
laid out in that email might get some of the benefit without the
difficulty. But hopefully it's easy to just store heap values in the
leaf nodes of an index.

FWIW, I know that Sybase required that an IOT be clustered on a unique
index. I think Oracle has the same requirement as well.

On Fri, Nov 18, 2005 at 08:30:14AM +0000, Simon Riggs wrote:
> On Thu, 2005-11-17 at 21:57 -0300, Alvaro Herrera wrote:
>
> > Personally I'd prefer to see index-ordered heaps, where the heap is
> > itself an index, so the ordering it automatically kept.
>
> Agreed. (I think thats case-closed on the previous proposal.)
>
> As an aside, Index Organized Tables (IOTs) isn't just an Oracle term.
> They first used the term, but the concept had already been implemented
> in both Tandem (value-ordered) and Teradata (hash-ordered) before this,
> as well as numerous OLAP systems. The concept doesn't look to be
> patented.
>
> If anybody is looking for a justification for IOTs, the reduction in
> table volume for large tables is very high. IOTs are the equivalent of
> removing all of the leaf blocks of the clustered index.
>
> Best Regards, Simon Riggs
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Bob Ippolito 2005-11-21 23:19:35 Re: PostgreSQL 8.1.0 catalog corruption
Previous Message Tom Lane 2005-11-21 23:12:50 Re: PostgreSQL 8.1.0 catalog corruption