Re: Maintaining cluster order on insert

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> * delete a random 2% of the table
> * vacuum to recover space
> * insert a random 2% of the table
> * select (about) 1000 consecutively-numbered rows
> * select all the rows (this is just a cross check that the
> number of rows isn't changing too much)
>
> What you would hope to see as the benefit of the patch is that the time
> for the range SELECT degrades more slowly as more of the table is
> replaced. Ignoring the first SELECT as being a startup transient, it
> looks like HEAD degrades from about 3 msec to 6 msec over 10 iterations
> (20% replacement of the table), whereas with the patch it's about 3 msec
> to about 4 and a half. However, the INSERT steps went from around 20
> sec each to about twice that.

I would suggest:

a) continuing the test until you have 100% replacement.

b) defining the tables with a fill-factor large enough to hold at least one
extra tuple

c) considering this patch alongside GII where it is especially important.

It's pretty serious what you're suggesting since it means that we'll basically
never have a real cluster feature. I would sure hope we're missing something
and there's a way to make this work usefully.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com