We have tried fillfactor for indices and it seems to work. Need to try fillfactor for table. May for that reason the bulk update queries don't get the advantage of HOT :)
On Wed, Apr 30, 2008 at 9:45 PM, Pavan Deolasee <[EMAIL PROTECTED]> wrote: > On Wed, Apr 30, 2008 at 8:16 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > > "Pavan Deolasee" <[EMAIL PROTECTED]> writes: > > > That's weird. With that fillfactor, you should have a very high > > > percentage of HOT update ratio. It could be a very special case that > > > we might be looking at. > > > > He's testing > > > > It's "She" :-) > > Oh yes. Apologies if I sounded harsh; did not mean that. I was just > completely confused why she is not seeing the HOT updates. > > > >> update table1 set delta1 = 100 where code/1000000 =999; > > > > so all the rows being updated fall into a contiguous range of "code" > > values. If the table was loaded in such a way that those rows were > > also physically contiguous, then the updates would be localized and > > would very soon run out of freespace on those pages. > > > > Yeah, that seems like the pattern. I tested with the similar layout > and a fill factor 80. The initial few bulk updates had comparatively > less HOT updates (somewhere 20-25%), But within 4-5 iterations of > updating the same set of rows, HOT updates were 90-95%. That's because > after few iterations (and because of non-HOT updates) the tuples get > scattered in various blocks, thus improving chances of HOT updates. > > I guess the reason probably is that she is using fill factor for > indexes and not heap, but she hasn't yet confirmed. > > Thanks, > Pavan > > -- > Pavan Deolasee > EnterpriseDB http://www.enterprisedb.com > -- Regards Gauri
