Re: Equivalent praxis to CLUSTERED INDEX?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bruce Momjian wrote:

| Gaetano Mendola wrote:
|
|>Tom Lane wrote:
|>
|> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
|> >
|> >>Agreed. What I am wondering is with our system where every update gets
|> >>a new row, how would this help us? I know we try to keep an update on
|> >>the same row as the original, but is there any significant performance
|> >>benefit to doing that which would offset the compaction advantage?
|> >
|> >
|> > Because Oracle uses overwrite-in-place (undoing from an UNDO log on
|> > transaction abort), while we always write a whole new row, it would take
|> > much larger PCTFREE wastage to get a useful benefit in PG than it does
|> > in Oracle. That wastage translates directly into increased I/O costs,
|> > so I'm a bit dubious that we should assume there is a win to be had here
|> > just because Oracle offers the feature.
|>
|>Mmmm. Consider this scenario:
|>
|>ctid datas
|>(0,1) yyy-xxxxxxxxxxxxxxxxxxx
|>(0,2) -------- EMPTY --------
|>(0,3) -------- EMPTY --------
|>(0,4) -------- EMPTY --------
|>(0,5) -------- EMPTY --------
|>(0,6) yyy-xxxxxxxxxxxxxxxxxxx
|>(0,7) -------- EMPTY --------
|>.... -------- EMPTY --------
|>(0,11) yyy-xxxxxxxxxxxxxxxxxxx
|>
|>
|>the row (0,2) --> (0,5) are space available for the (0,1) updates.
|>This will help a table clustered ( for example ) to mantain his
|>own correct cluster order.
|
|
| Right. My point was that non-full fill is valuable for us only when
| doing clustering, while for Oracle it is a win even in non-cluster cases
| because of the way they update in place.

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.

Regards
Gaetano Mendola

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBL+kA7UpzwH2SGd4RAp6fAJ9rSs5xmTXsy4acUGcnCRTbEUCwrwCgo/o6
0JPtziuf1E/EGLaqjbPMV44=
=pIgX
-----END PGP SIGNATURE-----