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
executed CREATE in   0.098872 sec
executed INSERT in  36.958824 sec
executed CREATE in   7.320412 sec
executed CLUSTE in  22.579578 sec
executed VACUUM in   0.809287 sec
executed DELETE in   8.567730 sec
executed VACUUM in   5.194736 sec
executed INSERT in   0.882852 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 839347 AND 840347
retrieved  993 tuples in   0.239210 sec
executing select: SELECT count(*) FROM testtab
retrieved 999898 tuples in   0.417749 sec
executed DELETE in   5.398222 sec
executed VACUUM in   0.949003 sec
executed INSERT in   0.771617 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 393988 AND 394988
retrieved 1015 tuples in   0.001261 sec
executing select: SELECT count(*) FROM testtab
retrieved 1000038 tuples in   0.373199 sec
executed DELETE in   5.613083 sec
executed VACUUM in   1.289501 sec
executed INSERT in   1.434903 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 782316 AND 783316
retrieved 1011 tuples in   0.001723 sec
executing select: SELECT count(*) FROM testtab
retrieved 999826 tuples in   0.353616 sec
executed DELETE in   3.729799 sec
executed VACUUM in   1.406940 sec
executed INSERT in   1.169133 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 797641 AND 798641
retrieved  991 tuples in   0.001118 sec
executing select: SELECT count(*) FROM testtab
retrieved 999731 tuples in   0.340721 sec
executed DELETE in   1.448537 sec
executed VACUUM in   0.918642 sec
executed INSERT in   0.471951 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 910735 AND 911735
retrieved 1029 tuples in   0.001174 sec
executing select: SELECT count(*) FROM testtab
retrieved 999773 tuples in   0.334880 sec
executed DELETE in   0.972467 sec
executed VACUUM in   0.925557 sec
executed INSERT in   0.645214 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 197353 AND 198353
retrieved  975 tuples in   0.001076 sec
executing select: SELECT count(*) FROM testtab
retrieved 999751 tuples in   0.334343 sec
executed DELETE in   0.966780 sec
executed VACUUM in   0.923394 sec
executed INSERT in   0.468693 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 334887 AND 335887
retrieved  983 tuples in   0.001381 sec
executing select: SELECT count(*) FROM testtab
retrieved 999801 tuples in   0.335184 sec
executed DELETE in   0.792456 sec
executed VACUUM in   0.934451 sec
executed INSERT in   0.435028 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 767461 AND 768461
retrieved  998 tuples in   0.001279 sec
executing select: SELECT count(*) FROM testtab
retrieved 999854 tuples in   0.338010 sec
executed DELETE in   0.553884 sec
executed VACUUM in   0.933886 sec
executed INSERT in   0.435980 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 277496 AND 278496
retrieved 1017 tuples in   0.001257 sec
executing select: SELECT count(*) FROM testtab
retrieved 999923 tuples in   0.333156 sec
executed DELETE in   0.518371 sec
executed VACUUM in   0.932207 sec
executed INSERT in  18.031924 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 553415 AND 554415
retrieved 1012 tuples in   0.001280 sec
executing select: SELECT count(*) FROM testtab
retrieved 1000135 tuples in   0.343191 sec
executed CREATE in   0.014632 sec
executed INSERT in  40.509772 sec
executed CREATE in   4.485714 sec
executed VACUUM in   0.396134 sec
executed DELETE in  15.366893 sec
executed VACUUM in   4.004228 sec
executed INSERT in   1.794740 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 839347 AND 840347
retrieved 1008 tuples in   0.001182 sec
executing select: SELECT count(*) FROM testtab
retrieved 1000088 tuples in   0.314738 sec
executed DELETE in  19.191114 sec
executed VACUUM in   0.862771 sec
executed INSERT in   0.595838 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 393988 AND 394988
retrieved 1010 tuples in   0.001081 sec
executing select: SELECT count(*) FROM testtab
retrieved 1000243 tuples in   0.312002 sec
executed DELETE in   4.152384 sec
executed VACUUM in   0.876611 sec
executed INSERT in   0.748715 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 782316 AND 783316
retrieved 1006 tuples in   0.001168 sec
executing select: SELECT count(*) FROM testtab
retrieved 1000469 tuples in   0.313215 sec
executed DELETE in   1.860138 sec
executed VACUUM in   0.887234 sec
executed INSERT in   0.381926 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 797641 AND 798641
retrieved 1001 tuples in   0.001170 sec
executing select: SELECT count(*) FROM testtab
retrieved 1000399 tuples in   0.313861 sec
executed DELETE in   1.203454 sec
executed VACUUM in   0.894531 sec
executed INSERT in   0.414772 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 910735 AND 911735
retrieved 1005 tuples in   0.001192 sec
executing select: SELECT count(*) FROM testtab
retrieved 1000324 tuples in   0.313666 sec
executed DELETE in   1.103641 sec
executed VACUUM in   0.911423 sec
executed INSERT in   0.579772 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 197353 AND 198353
retrieved 1012 tuples in   0.001281 sec
executing select: SELECT count(*) FROM testtab
retrieved 1000150 tuples in   0.314685 sec
executed DELETE in   0.757131 sec
executed VACUUM in   0.904153 sec
executed INSERT in   0.412712 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 334887 AND 335887
retrieved 1003 tuples in   0.001255 sec
executing select: SELECT count(*) FROM testtab
retrieved 1000248 tuples in   0.342517 sec
executed DELETE in   0.572931 sec
executed VACUUM in   0.910369 sec
executed INSERT in   0.701230 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 767461 AND 768461
retrieved 1022 tuples in   0.001361 sec
executing select: SELECT count(*) FROM testtab
retrieved 1000353 tuples in   0.313835 sec
executed DELETE in   0.529490 sec
executed VACUUM in   0.913697 sec
executed INSERT in   0.407656 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 277496 AND 278496
retrieved 1031 tuples in   0.001368 sec
executing select: SELECT count(*) FROM testtab
retrieved 1000435 tuples in   0.314925 sec
executed DELETE in   4.871267 sec
executed VACUUM in   9.618490 sec
executed INSERT in   2.496079 sec
executing select: SELECT count(*) FROM testtab WHERE k BETWEEN 553415 AND 554415
retrieved  987 tuples in   0.001641 sec
executing select: SELECT count(*) FROM testtab
retrieved 1000648 tuples in   0.326064 sec
max_connections = 100            # (change requires restart)
shared_buffers = 700MB            # min 128kB or max_connections*16kB
maintenance_work_mem = 160MB        # min 1MB
max_fsm_pages = 204800            # min max_fsm_relations*16, 6 bytes each
full_page_writes = on            # recover from partial page writes
wal_buffers = 640kB            # min 32kB
checkpoint_segments = 30        # in logfile segments, min 1, 16MB each
checkpoint_timeout =30min        # range 30s-1h
checkpoint_warning = 600s        # 0 is off
autovacuum = off            # enable autovacuum subprocess?
datestyle = 'iso, dmy'
lc_messages = 'en_GB.UTF-8'            # locale for system error message
lc_monetary = 'en_GB.UTF-8'            # locale for monetary formatting
lc_numeric = 'en_GB.UTF-8'            # locale for number formatting
lc_time = 'en_GB.UTF-8'                # locale for time formatting