在PostgreSQL中是否有与SQLite R*Tree类似功能

对于PostGIS，您可以设置一个高达4D的基于R-Tree的索引作为任何其他GIST索引.Demo at db<>fiddle:

设置包含示例的表格:

create extension postgis;

create table business(
  id int generated by default as identity primary key,
  business_name text,
  x numeric, y numeric, z numeric, t numeric,
  geom geometry(PointZM,4326) generated always as 
      (st_setsrid(st_makepoint(x,y,z,t),4326)) stored);

select setseed(.4);
insert into business (business_name,x,y,z,t)
select 'business_'||n, x, y, z, t
from generate_series(1,1e5,1)a(n),
  lateral(select   n AS n1,
                   180-random()*360 x,
                   90 -random()*180 y, 
                   50 -random()*100 z,
                   50 -random()*100 t)b;

设置索引.默认运算符类将仅使用每个几何图形的两个维度来构建r-树，因此您需要指定gist_geometry_ops_nd个运算符类来处理n维几何图形的所有维度:

create index geom_idx on business using gist(geom gist_geometry_ops_nd);

一种测试操作，显示索引用途: Select 最接近参考点的7个点:

prepare postgis_based as
select id,business_name,x,y,z,t
      ,(select geom from business where id=42)<<->>geom AS distance 
from business
order by distance
limit 7;

explain analyze execute postgis_based;
execute postgis_based;

QUERY PLAN
Limit  (cost=8.59..9.53 rows=7 width=73) (actual time=0.428..1.624 rows=7 loops=1)
  InitPlan 1 (returns $0)
    ->  Index Scan using business_pkey on business business_1  (cost=0.29..8.31 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=1)
          Index Cond: (id = 42)
  ->  Index Scan using geom_idx on business  (cost=0.28..13468.38 rows=100005 width=73) (actual time=0.427..1.622 rows=7 loops=1)
        Order By: (geom <<->> $0)
Planning Time: 0.351 ms
Execution Time: 1.689 ms

如果在使用默认索引设置时将x和y置零，而只填充z和t，则可以为observe slow sequential scans kicking in，这表示只使用了前两个维度来构建树，因此在该场景中没有帮助.一旦你切换到gist_geometry_ops_nd，你就开始得到quick index scans.