ST_DWithin does not use index with non-literal argument
<p>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.</p>

<pre><code>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);
</code></pre>

<p>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</p>

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

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

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

<p>However, if the radius is a constant value, like in the following</p>

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

<p>The spatial index is used as show by EXPLAIN</p>

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

<p>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.  </p>

<p>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?</p>