An exact answer is not possible without testing on your actual data, as this is highly dependent on the data size, structure, corresponding PostgreSQL internals, and the exact queries you intent to use the index for.
Generally speaking, it is perfectly fine to assume a
GIST index is what you want.
Some basic considerations:
GIST has full operator support, including (k)NN searches
SP-GIST doesn't support (k)NN as of yet, and supports fewer operators (which is probably not a real issue, though)
GIST isn't overly sensitive to the spatial distribution (homogeneous/consistently spaced vs heterogeneous/blobs of geometries) and the topology (many overlaps vs isolated distribution) of your
SP_GIST is most effective for overlapping geometries, and boost searches for spatially heterogeneous distributions, due to its Spatial Partitioning
GIST creation time is rising slightly non-linear with the amount of data it has to
ingest, but has a an overall stable increase (ballpark figure: 20 minutes for 100 million rows (points; global distribution))
SP_GIST is likely faster for smaller amount of data, but tends to have a significant performance drop after a few hundred million geometries compared to
GIST indexes have a non-trivial storage impact (ballpark figure: 5GB for 100 million geometries), but only
BRIN indexes really make a difference here
SP-GIST has a few percentages less space requirement
Since it seems you are having heterogeneously distributed
POINTs, you could definitely try the
SP_GIST index and see if you get more performance out of it; this is still dependent on other factors that are linked to PG internals, relation and result statistics, though.
But it will likely be slower if all you filter for is all points within a large bbox, as this is better covered with
- a presentation of an old university colleague; his collection of slides, on different topics, are extremely insightful!
- a direct link from said presentation with more details to
SP_GIST; all other index types are covered, too