postgresql – ST_DWithin不使用带非文字参数的索引

我在Amazon RDS上使用PostreSQL 9.3 w / PostGIS 2.1.8.我有一个名为project_location的表,它定义了“geo-fences”(每个都基本上是一个坐标和半径).使用名为“location”的几何列和名为“radius”的双列存储地理围栏.我在location列上有一个空间索引.

CREATE TABLE project_location
(
  ...
  location geography(Point,4326),radius double precision NOT NULL,...
)
CREATE INDEX gix_project_location_location 
ON project_location USING gist (location);

该表目前有大约50,000条记录.如果我查询表以查找geo-fence包含一个点的所有project_locations,就像

SELECT COUNT(*) 
FROM project_location 
WHERE ST_DWithin(location,ST_SetSRID(ST_MakePoint(-84.1000,34.0000),4326)::geography,radius);

我发现没有使用空间索引. EXPLAIN的结果显示如下：

"Aggregate  (cost=11651.97..11651.98 rows=1 width=0)"
"  ->  Seq Scan on project_location  (cost=0.00..11651.97 rows=1 width=0)"
"        Filter: ((location && _st_expand('0101000020E610000066666666660655C00000000000004140'::geography,radius)) AND ('0101000020E610000066666666660655C00000000000004140'::geography && _st_expand(location,radius)) AND _st_dwithin(location,'0101000020E610000066666666660655C00000000000004140'::geography,radius,true))"

但是,如果半径是一个常数值,如下所示

SELECT COUNT(*) 
FROM project_location 
WHERE ST_DWithin(location,1000);

空间索引用于EXPLAIN显示

"Aggregate  (cost=8.55..8.56 rows=1 width=0)"
"  ->  Index Scan using gix_project_location_location on project_location  (cost=0.28..8.55 rows=1 width=0)"
"        Index Cond: (location && '0101000020E610000066666666660655C00000000000004140'::geography)"
"        Filter: (('0101000020E610000066666666660655C00000000000004140'::geography && _st_expand(location,1000::double precision)) AND _st_dwithin(location,1000::double precision,true))"

在阅读了ST_DWithin如何使用索引后,我理解为什么会这样.基本上,基于半径的边界框用于“预过滤”候选点以在对这些点进行相对昂贵的距离计算之前确定可能的匹配.

我的问题是有没有办法进行这种类型的搜索,以便可以使用空间索引？基本上是一种用一堆可变半径地理围栏来查询表的方法吗？

create index on project_location using gist (ST_Expand(location,radius));

select count(*) from project_location where ST_Intersects(ST_Expand(location,radius),<your_point>) and ST_Distance(location,<your_point>) < radius;