ST_DWithin does not use index with non-literal argument

Question

I am using PostreSQL 9.3 w/ PostGIS 2.1.8 on Amazon RDS. I have a table named project_location, which defines "geo-fences" (each one is essentially a coordinate and radius). The geo-fence is stored using a geometry column named "location" and a double column named 'radius'. I have a spatial index on the location column.

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);

The table currently has ~50,000 records in it. If I query the table to find all project_locations where the geo-fence contains a point, something like

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

I find that the spatial index is not used. The results of EXPLAIN show the following:

"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))"

However, if the radius is a constant value, like in the following

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

The spatial index is used as show by 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, '0101000020E610000066666666660655C00000000000004140'::geography, 1000::double precision, true))"

Having read how ST_DWithin uses indexes I understand why this is the case. Essentially a bounding box based on the radius is used to "pre-filter" the candidate points to determine the possible matches before doing the relatively expensive distance calculation on those points.

My question is there any way to do this type of search so that a spatial index can be used? Basically a way to query a table with a bunch of variable radius geo-fences?

Darafei Praliaskouski Darafei Praliaskouski · Accepted Answer · 2018-04-20T14:00:13

PostGIS allows to speed up your query by using a functional index. I'm not sure how to do it in geography datatype as there is no ST_Expand there, but the query will be quite simple if you store your data in some Mercator projection (say, SRID=3857).

Idea:

generate a box expanded by radius units around your point;
build an index on those boxes;
query the point of user against these boxes;
recheck by exact radius.

On your project_location table:

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

Now you can use ST_Expand(location, radius) as if it was a your indexed geometry column.

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

Now you're skipping ST_DWithin as you want the recheck to never attempt to use index, and use your index on function of your geometry.

For geography you may try to stub ST_Expand with ST_Envelope(ST_Buffer(geom, radius)).

ST_DWithin does not use index with non-literal argument

3 Answers