/ postgresql

Postgresql 9.5 GiST index on int array

Last week I build an GiST index for a table with 200+ rows (two columns: id INT, sp_ids INT[]) , it spent me almost 10 mins.

There are only two elements in every int array, but those value are larger than normal integers, they are 7~8 bits integers.

After chat via postgresql irc, someone pointed that I should use gist__intbig_ops, not default(gist__int_ops).

gist__int_ops is optimized for small ints, and the other is suit for all small and big ints. (Document is implicit)

Maybe you can use GIN index instead of GiST, but if you need an excluded index with && ops, you have to use gist__intbig__ops

CREATE INDEX gist_idx ON t1 USING GIST (sp_ids gist__intbig__ops);