Showing revision 5

rbtree test

Rbtree testings

Since 8.4 gin index utilizes maintenance_work_mem to accumulate new index entries, which then flushed to disk to pending list and eventually inserted to index by autovacuum process.

Test environment: My desktop machine with Linux 2.6.24.3-smp, i686 Intel(R) Core(TM)2 Duo CPU E6750 @ 2.66GHz GenuineIntel, 8 GB RAM,

Pg settings (only relevant):
shared_buffers = 512MB 
work_mem = 32MB
maintenance_work_mem = 256MB
checkpoint_segments = 16
effective_cache_size = 1GB

1. Original test from http://archives.postgresql.org/pgsql-performance/2009-03/msg00340.php

drop table a;
create table a (i1 int, i2 int, i3 int, i4 int, i5 int, i6 int);
-- insert and create index
insert into a select n, n, n, n, n, n from generate_series(1, 100000) as n;
create index arr_gin on a using gin ( (array[i1, i2, i3, i4, i5, i6]) );
truncate a;
drop index arr_gin ; 
-- insert after index
create index arr_gin on a using gin ( (array[i1, i2, i3, i4, i5, i6]) );
insert into a select n, n, n, n, n, n from generate_series(1, 100000) as n;

For fu=off (fastupdate is disabled) use the same sql, but add 'with (fastupdate=off)'.

          HEAD             HEAD+rbtree-0.10
     fu=on   fu=off     fu=on   fu=off
ins  290.211 314.417    240.020 287.560
idx  647.324 624.781    718.303 680.528
sum  937.535 939.198    958.323 968.088

idx  1.815   2.241      1.383   2.178
ins  817.886 959.904    782.268 985.334
sum  819.701 962.145    783.651 987.512  

rbtree is used only with bulk insert into index (ins,idx,sum sequence) and fastupdate enabled, so we can use timings with fastupdate disabled to verify test results.

Summary:

  Performance difference between HEAD and HEAD+rbtree-0.10 is within accuracy
  of test.

2. Random array test - 100 000 integer arrays, array length is random between 5 and 1000, cardinality is 500 000. Data: http://www.sai.msu.su/~megera/postgres/files/arr-5-1000-500000-100000.sql.gz

   zcat arr-5-1000-500000-100000.sql.gz| psql test
   create index a_idx on tt using gin(a);
          HEAD             HEAD+rbtree-0.10
idx  97833.943          93022.371
     94076.822          94435.548
     92993.065          91723.859

Summary:

  There is no visible performance difference between HEAD (btree+hack) and
  HEAD+rbtree-0.10.

3. Real data from Wikipedia links Data: http://www.sai.msu.su/~megera/postgres/files/links2.sql.gz

   zcat links2.sql.gz | psql test
test=# \d links2
     Table "public.links2"
 Column |   Type    | Modifiers 
--------+-----------+-----------
 id     | integer   | 
 idout  | integer[] | 
 idin   | integer[] | 
Indexes:
    "id_idx_link2" btree (id)
    "idin_rbtree_idx" gin (idin)
    "idout_rbtree_idx" gin (idout)
          HEAD             HEAD+rbtree-0.10
ins    97833.943          165348.671
btree  180160.087         31304.554 
idout   32624.773          767966.869
gin2                      720310.768
   

Links: