2

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.

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).

  • 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. – joanolo Feb 2 '17 at 23:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.