Does spgist extension in PostgreSQL 9.2.3 support float or int array?

Question

SP-GiST is an abbreviation for space-partitioned GiST in PostgreSQL 9.2.3. It can be used to find the similar tuple by creating the sp-gist index on the column.

e.g:

create index pt_spgist_idx on geo using spgist(point);

'Point' column on table 'Geo' is Point type of Geometric Types.

and we can use sql sentence to find the nearest point of (34.34898,-92.82934) with spgist index 'pt_spgist_idx':

select point from geo where point ~= '(34.34898,-92.82934)'

We can see that sp-gist index supports Geometric Point type.

The question is:

Does spgist extension in PostgreSQL 9.2.3 support float or int array? For I want to use sp-gist index in int array whose size is 500.

Answer 1

No, by default SP-GiST only supports these operators over point ranges box and text

Name Indexed Data Type Indexable Operators
kd_point_ops point << <@ <^ >> >^ ~=
quad_point_ops point << <@ <^ >> >^ ~=
range_ops any range type && &< &> -|- << <@ = >> @>
box_ops box << &< && &> >> ~= @> <@ &<| <<| |>> |&>
text_ops text < <= = > >= ~<=~ ~<~ ~>=~ ~>~

That's not to say that it can not work over float or int[] but it may be awkward unless they have any special internal designation you can exploit for partitioning. That is to say, if your int[] and array are likely to produce balanced trees, or are equally distributed there is no point whatsoever (afaik).

• joanolo: If arrays were guaranteed to have always the same number of dimensions, SP-Gist partitioning makes sense in a mathematical point of view. Your array would represent a point in an n-dimensional space. As an array, in principle, can have variable number of dimensions... this might be the reason why this isn't implemented.