• Marc G. Fournier's avatar
    What looks like some *major* improvements to btree indexing... · 5d9f146c
    Marc G. Fournier authored
    Patches from: aoki@CS.Berkeley.EDU (Paul M. Aoki)
    
    i gave jolly my btree bulkload code a long, long time ago but never
    gave him a bunch of my bugfixes.  here's a diff against the 6.0
    baseline.
    
    for some reason, this code has slowed down somewhat relative to the
    insertion-build code on very small tables.  don't know why -- it used
    to be within about 10%.  anyway, here are some (highly unscientific!)
    timings on a dec 3000/300 for synthetic tables with 10k, 100k and
    1000k tuples (basically, 1mb, 10mb and 100mb heaps).  'c' means
    clustered (pre-sorted) inputs and 'u' means unclustered (randomly
    ordered) inputs.  the 10k table basically fits in the buffer pool, but
    the 100k and 1000k tables don't.  as you can see, insertion build is
    fine if you've sorted your heaps on your index key or if your heap
    fits in core, but is absolutely horrible on unordered data (yes,
    that's 7.5 hours to index 100mb of data...) because of the zillions of
    random i/os.
    
    if it doesn't work for you for whatever reason, you can always turn it
    back off by flipping the FastBuild flag in nbtree.c.  i don't have
    time to maintain it.
    
    good luck!
    
    baseline code:
    
    time psql -c 'create index c10 on k10 using btree (c int4_ops)' bttest
    real   8.6
    time psql -c 'create index u10 on k10 using btree (b int4_ops)' bttest
    real   9.1
    time psql -c 'create index c100 on k100 using btree (c int4_ops)' bttest
    real   59.2
    time psql -c 'create index u100 on k100 using btree (b int4_ops)' bttest
    real   652.4
    time psql -c 'create index c1000 on k1000 using btree (c int4_ops)' bttest
    real   636.1
    time psql -c 'create index u1000 on k1000 using btree (b int4_ops)' bttest
    real   26772.9
    
    bulkloading code:
    
    time psql -c 'create index c10 on k10 using btree (c int4_ops)' bttest
    real   11.3
    time psql -c 'create index u10 on k10 using btree (b int4_ops)' bttest
    real   10.4
    time psql -c 'create index c100 on k100 using btree (c int4_ops)' bttest
    real   59.5
    time psql -c 'create index u100 on k100 using btree (b int4_ops)' bttest
    real   63.5
    time psql -c 'create index c1000 on k1000 using btree (c int4_ops)' bttest
    real   636.9
    time psql -c 'create index u1000 on k1000 using btree (b int4_ops)' bttest
    real   701.0
    5d9f146c
nbtsort.c 36.5 KB