Incoming links: SQL spatial index
The highly underdocumented built-in module, that supports SQL spatial index and a lot more.
Quite horrendous as it only seems to work on geometric types and not existing columns. But why.
And it uses custom operatores, where standard operators would have been just fine for points...
Minimal runnable example with points:The index creation unfortunately took 100s, so it will not scale to 1B points very well whic his a shame.
set -x
time psql -c 'drop table if exists t'
time psql -c 'create table t(p point)'
time psql -c "insert into t select (point ('(' || generate_series || ',' || generate_series || ')')) from generate_series(1, 10000000)"
time psql -c 'create index on t using gist(p)'
time psql -c "select count(*) from t where p <@ box '(1000000,1000000),(9000000,2000000)'"