Re: [HACKERS] Async I/O

Michal Mosiewicz wrote a few weeks ago:
>
> Also, somebody asked about clustered indexes. I was looking for
> informations on this technique at Sybase (which is a great source of
> information on various DB hints). If you read above document between
> lines, the conclusion comes that clustered index is something that
> allows the data from table to be mixed with index. I suppose that index
> pages are clustered with data pages so if you find aproporiate record in
> index, the data that this index entry points to is on the same page or
> close.

Sybase clustered indexes are pretty standard stuff.

Our B-tree indexes have the index leaf pages storing index rows containing a
key and a 'tid' that points to a data row in a separate heap.

A clustered index is the same in the upper levels as our B-tree, but the leaf
pages contain the actual data rows. Thus the data is maintained in sorted
order for the clustering key. Also, in Sybase, all table pages are chained
together. This has the side effect of possibly speeding up sequential scans.

Very nice except that key updates, or even index splits cause rows to move so
all the secondary indexes must be updated. And, maintaining the page chain
links not only adds overhead, but also proves to be very error prone, leading
to crosslinked tables and other horrors.

Still, for a huge class of applications clustered indexes are a big win. It
would be well worth adding this to pgsql. I would not do the page chaining
though.

-dg

David Gould dg(at)illustra(dot)com 510.628.3783 or 510.305.9468
Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612
Error 605 [tm] is a trademark of Sybase Inc. -- dg