Re: Equivalent praxis to CLUSTERED INDEX?

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:

> > Don't you think this will permit also to avoid extra disk seek and cache
> > invalidation? If you are updating the row (0,1) I think is less expensive
> > put the new version in (0,2) instead of thousand line far from that point.

Well if the other buffer "a thousand lines far from that point" is already in
ram, then no, there's no penalty at the time for storing it there.

However it destroys the clustering, which was the original point.

> It would, but does that outweigh the decreased I/O by having things more
> densely packed? I would think not.

Well the dense packing is worth something. But so is the clustering. There's
definitely a trade-off.

I always found my largest tables are almost always insert-only tables anyways.
So in Oracle I would have pctused 100 pctfree 0 on them and get the
performance gain.

The tables that would benefit from this would be tables always accessed by
indexes in index scans of more than one record. The better the clustering the
fewer pages the index scan would have to read in. If the data took 10% more
space but the index scan only needs 1/4 as many buffers it could be a big net
win.

--
greg