通过坐标查询花费的时间太长-要优化的选项？

小编典典

通过坐标查询花费的时间太长-要优化的选项？

sql

我有一张桌子来存储事件（目前大约有5M，但还会有更多）。每个事件都有我要对此查询关注的两个属性-
location（纬度和经度对）和relevancy。

我的目标是 ：对于给定的位置范围（SW / NE纬度/经度对，因此有4个浮点数）将返回relevancy落入这些范围之内的前100个事件。

我目前正在使用以下查询：

select * 
from event 
where latitude >= :swLatitude 
and latitude <= :neLatitude 
and longitude >= :swLongitude 
and longitude <= :neLongitude 
order by relevancy desc 
limit 100

现在暂时搁置此查询无法处理的日期线回绕问题。

对于较小的位置范围，此方法效果很好，但是每当我尝试使用较大的位置范围时，时滞都非常严重。

我定义了以下索引：

CREATE INDEX latitude_longitude_relevancy_index
  ON event
  USING btree
  (latitude, longitude, relevancy);

该表本身非常简单：

CREATE TABLE event
(
  id uuid NOT NULL,
  relevancy double precision NOT NULL,
  data text,
  latitude double precision NOT NULL,
  longitude double precision NOT NULL
  CONSTRAINT event_pkey PRIMARY KEY (id)
)

我尝试explain analyze并得到以下内容，我认为这甚至不使用该索引：

"Limit  (cost=1045499.02..1045499.27 rows=100 width=1249) (actual time=14842.560..14842.575 rows=100 loops=1)"
"  ->  Sort  (cost=1045499.02..1050710.90 rows=2084754 width=1249) (actual time=14842.557..14842.562 rows=100 loops=1)"
"        Sort Key: relevancy"
"        Sort Method: top-N heapsort  Memory: 351kB"
"        ->  Seq Scan on event  (cost=0.00..965821.22 rows=2084754 width=1249) (actual time=3090.660..12525.695 rows=1983213 loops=1)"
"              Filter: ((latitude >= 0::double precision) AND (latitude <= 180::double precision) AND (longitude >= 0::double precision) AND (longitude <= 180::double precision))"
"              Rows Removed by Filter: 3334584"
"Total runtime: 14866.532 ms"

我在Win7上使用的是PostgreSQL 9.3，而对于此看似简单的任务而言，将其转移到其他任何对象似乎都太过分了。

问题：

有什么方法可以使用不同的索引来帮助当前查询更快？
有什么方法可以重写当前查询以使其更快？
正确执行此操作的最简单方法是什么？安装PostGIS并使用GEOGRAPHY数据类型？那实际上会为我现在所做的工作带来性能上的好处吗？哪种PostGIS功能最适合此查询？

编辑＃1：结果vacuum full analyze：

INFO:  vacuuming "public.event"
INFO:  "event": found 0 removable, 5397347 nonremovable row versions in 872213 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 17.73s/11.84u sec elapsed 154.24 sec.
INFO:  analyzing "public.event"
INFO:  "event": scanned 30000 of 872213 pages, containing 185640 live rows and 0 dead     rows; 30000 rows in sample, 5397344 estimated total rows
Total query runtime: 360092 ms.

抽真空后的结果：

"Limit  (cost=1058294.92..1058295.17 rows=100 width=1216) (actual time=6784.111..6784.121 rows=100 loops=1)"
"  ->  Sort  (cost=1058294.92..1063405.89 rows=2044388 width=1216) (actual time=6784.109..6784.113 rows=100 loops=1)"
"        Sort Key: relevancy"
"        Sort Method: top-N heapsort  Memory: 203kB"
"        ->  Seq Scan on event  (cost=0.00..980159.88 rows=2044388 width=1216) (actual time=0.043..6412.570 rows=1983213 loops=1)"
"              Filter: ((latitude >= 0::double precision) AND (latitude <= 180::double precision) AND (longitude >= 0::double precision) AND (longitude <= 180::double precision))"
"              Rows Removed by Filter: 3414134"
"Total runtime: 6784.170 ms"

阅读 186

2021-04-07

共1个答案

小编典典

使用使用R树的空间索引（本质上是二维索引，通过将空间划分为多个框进行操作），您会好得多，并且在两个单独的纬度上进行的比较要好于大于，小于，此类查询中的lon值。不过，您将需要首先创建一个几何类型，然后将其编入索引并在查询中使用，而不是当前正在使用的单独的纬度/经度对。

下面的代码将创建一个几何类型，将其填充并为其添加索引，以确保它是一个点，并且以经度/纬度表示，称为EPSG：4326

alter table event add column geom geometry(POINT, 4326);
update event set geom=ST_SetSrid(ST_MakePoint(lon, lat), 4326);
create index ix_spatial_event_geom on event using gist(geom);

然后，您可以运行以下查询来获取事件，这些事件将使用空间相交，而空间相交应利用您的空间索引：

Select * from events where ST_Intersects(ST_SetSRID(ST_MakeBox2D(ST_MakePoint(swLon, swLat), 
    ST_MakePoint(neLon, neLat)),4326), geom) 
order by relevancy desc limit 100;

您可以通过使用ST_MakeBOX2D和两组点（位于边界框的对角线上）来为相交创建边界框，因此SW和NE或NW和SE对都可以工作。

在对此进行解释时，您应该发现已包含空间索引。这将比lon和lat列上的两个单独索引好得多，因为您只会命中一个针对空间搜索而优化的索引，而不是两个B树。我意识到这代表了另一种方式，除了间接地，它没有回答您最初的问题。

编辑： Mike
T提出了一个很好的观点，即对于4326中的边界框搜索，使用几何数据类型和&&运算符更合适，更快捷，因为无论如何SRID都将被忽略，例如，

 where ST_MakeBox2D(ST_MakePoint(swLon, swLat), ST_MakePoint(neLon, neLat)) && geom

2021-04-07