For integer arrays GIN is 10x slower than GiST on update. It's about 10x faster than GiST on select. Updating of 100 rows in 100,000 takes < 0.5s for GIN index, which is quite reasonable speed.
Linux 2.6.19.1, Slackware, x86, Intel(R) Pentium(R) 4 CPU 2.40GHz, 2Gb RAM.
PostgreSQL 8.2.4
Test table tt(id integer, a int[]) with GIN index created
test=# \d tt Table "public.tt" Column | Type | Modifiers --------+-----------+----------- id | integer | a | integer[] | Indexes: "gin_idx" gin (a)
For creating GiST index
create index gist_idx on tt using gist(a gist__intbig_ops);
Example of populating a table - 10000 rows, with integer arrays cardinality 500 and maximal array length of 100.
sh ./test-id.sh -l 100 -n 10000 -v 500 | psql test -c "copy tt from stdin with delimiter as '|'"
Example of update SQL - update first 300 rows
update tt set a=array( select(1000*random())::integer+generate_series( 0,200) ) where id < 300;
Update array column by random array of length 200, vary #rows ( All timings in ms !)
-l 100 -n 10000 -v 500
Create index:
-l 100 -n 100000 -v 500
Create index:
-l 100 -n 10000 -v 5000
Create index:
-l 100 -n 100000 -v 5000
Create index (real):
Скрипт test-id.sh for generating test load.
#!/bin/sh # default values # array length MAXLEN=4 # number of rows MAXNUM=10 # cardinality MAXVAL=100 while getopts l:n:v: opt do case "$opt" in l) MAXLEN="$OPTARG";; n) MAXNUM="$OPTARG";; v) MAXVAL="$OPTARG";; esac done for ((i=0;i<$MAXNUM;i++)) do len=$(( $RANDOM % $MAXLEN))+1 for ((j=0;j<$len;j++)) do if [ $j == '0' ]; then val=$(( $RANDOM % $MAXVAL)) else val=$val,$(( $RANDOM % $MAXVAL)) fi done echo $i\|{$val} done