sql - 按坐标查询花费的时间太长 - 优化选项？

Question

我有一个存储事件的表(目前大约有 5M，但还会有更多)。对于此查询，每个事件都有两个我关心的属性 - location(纬度和经度对)和 relevancy。

我的目标是:对于给定的位置范围(SW/NE 纬度/经度对，所以 4 个 float )按 相关性 返回排名前 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"

Answer 1

使用使用 R 树的空间索引(本质上是二维索引，通过将空间划分为多个框来操作)会更好，并且比大于、小于比较的性能要好得多这种查询有两个单独的经纬度值。不过，您需要先创建一个几何类型，然后在查询中对其进行索引和使用，而不是您当前使用的单独的纬度/经度对。

下面将创建一个几何类型，填充它，并为其添加一个索引，确保它是一个点并以纬度/经度表示，称为 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