Re: Maintaining cluster order on insert

Tom Lane wrote:
> 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.

Clustering in general isn't very important on a table that fits in cache.

Also note that the overhead of descending the b-tree twice on INSERTs
hurts the most in a CPU bound test like that. The overhead of the
two-phase method I proposed should be lower.

Test 1:
> executed CREATE in 0.066563 sec
> executed INSERT in 40.465653 sec
> executed CREATE in 9.152698 sec
> executed CLUSTE in 20.036375 sec
> executed VACUUM in 1.440232 sec
Test 2:
> executed CREATE in 0.086862 sec
> executed INSERT in 50.746362 sec
> executed CREATE in 12.115655 sec
> executed CLUSTE in 33.656341 sec
> executed VACUUM in 4.306563 sec

Why is there such a big difference in all these initialization steps as
well? Is it just random noise?

I played a bit with that test program, results from my laptop attached.
I used a version patched with the latest patch I submitted, because
that's what I had readily available. I used the same patched binaries in
both test runs, I just didn't CLUSTER the table in the other run. The
main difference to your results is that the DELETE, VACUUM and INSERT
operations are much faster both with and without the patch. Most INSERTs
for example took < 1 s, and in your results they took > 15 s. Any idea why?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com