Extremely slow intarray index creation and inserts. - Mailing list pgsql-performance

Short summary:
    * extremely slow intarray indexes with gist__int_ops
    * gist__intbig_ops seems much faster even with short arrays
    * gin seems much much faster for inserts and creation(and queries)


I was debugging a system with a table that slowed to where individual
inserts were taking well over a second on a practically idle system.
Dropping an a gist index on an intarray made the problem go away.

Timing the creation of gist indexes on this 8.3.6 system makes me
think there's something broken with intarray's gist indexes.

This table summarizes some of the times, shown more completely
in a script below.
=================================================================
create gist index on 10000  =   5   seconds
create gist index on 20000  =  32   seconds
create gist index on 30000  =  39   seconds
create gist index on 40000  = 102   seconds
create gist index on 70000  = I waited 10 minutes before giving up.

create gin  index on 40000  =   0.7 seconds
create gist index on 40000  =   5   seconds using gist__intbig_ops

create gin  index on 70000  =   1.0 seconds
create gist index on 70000  =   9   seconds using gist__intbig_ops
==================================================================

This surprised me for a number of reasons.   The longest
array in the table is 9 elements long, and most are 5 or 6
so I'd have thought the default ops would have been better
than the big_ops.  Secondly, I thought gin inserts were expected
to be slower than gist, but I'm finding them much faster.

Nothing seems particular strange about the data.  A dump
of an excerpt of the table can be found at
http://0ape.com/tmp/int_array.dmp
(Yes, the production table had other columns; but this
column alone is enough to demonstrate the problem.)

   Any thoughts what I'm doing wrong?
   Ron

psql output showing the timing follows.

===============================================================================
vm=# create table tmp_intarray_test as select tag_id_array as my_int_array from taggings;
SELECT
vm=# create table tmp_intarray_test_10000 as select * from tmp_intarray_test limit 10000;
SELECT
vm=# create table tmp_intarray_test_20000 as select * from tmp_intarray_test limit 20000;
SELECT
vm=# create table tmp_intarray_test_30000 as select * from tmp_intarray_test limit 30000;
SELECT
vm=# create table tmp_intarray_test_40000 as select * from tmp_intarray_test limit 40000;
SELECT
vm=# \timing
Timing is on.
vm=#
vm=# create index "gist_10000 using GIST(my_int_array)" on tmp_intarray_test_10000 using GIST (my_int_array);
CREATE INDEX
Time: 5760.050 ms
vm=# create index "gist_20000 using GIST(my_int_array)" on tmp_intarray_test_20000 using GIST (my_int_array);
CREATE INDEX
Time: 32500.911 ms
vm=# create index "gist_30000 using GIST(my_int_array)" on tmp_intarray_test_30000 using GIST (my_int_array);
CREATE INDEX
Time: 39284.031 ms
vm=# create index "gist_40000 using GIST(my_int_array)" on tmp_intarray_test_40000 using GIST (my_int_array);
CREATE INDEX
Time: 102572.780 ms
vm=#
vm=#
vm=#
vm=#

vm=#
vm=#
vm=#  create index "gin_40000"     on tmp_intarray_test_40000 using GIN (my_int_array gin__int_ops);
CREATE INDEX
Time: 696.668 ms
vm=#  create index "gist_big_4000" on tmp_intarray_test_40000 using GIST (my_int_array gist__intbig_ops);
CREATE INDEX
Time: 5227.353 ms
vm=#
vm=#
vm=#
vm=# \d tmp_intarray_test
   Table "public.tmp_intarray_test"
    Column    |   Type    | Modifiers
--------------+-----------+-----------
 my_int_array | integer[] |

vm=# select max(array_dims(my_int_array)) from tmp_intarray_test_30000;
  max
-------
 [1:9]
(1 row)

Time: 119.607 ms
vm=#
vm=#
vm=# select version();
                                      version
-----------------------------------------------------------------------------------
 PostgreSQL 8.3.6 on i686-pc-linux-gnu, compiled by GCC gcc (Debian 4.3.3-1) 4.3.3
(1 row)

Time: 12.169 ms

vm=#  create index "gistbig70000" on tmp_intarray_test using GIST (my_int_array gist__intbig_ops);
CREATE INDEX
Time: 9156.886 ms
vm=#  create index "gin70000" on tmp_intarray_test using GIN (my_int_array gin__int_ops);
CREATE INDEX
Time: 1060.752 ms
vm=#  create index "gist7000" on tmp_intarray_test using GIST (my_int_array gist__int_ops);
      [.... it just sits here for 10 minutes or more ....]