Re: Equivalent praxis to CLUSTERED INDEX? - Mailing list pgsql-performance

From Gaetano Mendola
Subject Re: Equivalent praxis to CLUSTERED INDEX?
Date
Msg-id 412FD9B7.4080207@bigfoot.com
Whole thread Raw
In response to Re: Equivalent praxis to CLUSTERED INDEX?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
Tom Lane wrote:

 > Bruce Momjian <pgman@candle.pha.pa.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.



Regards
Gaetano Mendola













pgsql-performance by date:

Previous
From: Artimenko Igor
Date:
Subject: Re: Why those queries do not utilize indexes?
Next
From: Gaetano Mendola
Date:
Subject: Re: Equivalent praxis to CLUSTERED INDEX?