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: