Avoiding Full Scans with ST_DWithin and Geography

ST_Distance in a WHERE clause reads every row. ST_DWithin uses the index. Learn the rewrite, the two indexes it needs, and how to spot the difference in a plan.

← Back to Bounding Box & Spatial Index Queries

This page covers the single highest-value rewrite in a spatial API: turning a distance comparison that reads the whole table into an index-assisted predicate that reads a few hundred rows.

Context & When to Use

WHERE ST_Distance(geom::geography, $point) < 5000 looks like a filter and behaves like a table scan. PostgreSQL has no way to use a spatial index on it: ST_Distance is an ordinary function, so the planner must call it for every row before it can evaluate the comparison. On a two million row table that is two million geodesic distance computations to return perhaps forty rows.

ST_DWithin(geom::geography, $point, 5000) expresses the same intent in a form the planner understands. It is rewritten internally into an index-supported overlap test against an expanded bounding box, which the GiST index serves, followed by an exact distance recheck on the small candidate set. Same answer, three orders of magnitude less work.

The rewrite is mechanical and safe, and it belongs anywhere a radius appears: proximity search, geofence membership, “alert me when a vehicle comes within 200 m”. The one prerequisite people miss is the index — a geography predicate needs an index on the geography expression, and the existing geometry index will not serve it, as covered in Measuring Distance and Area in Metres with Geography.

Runnable Implementation

-- The index the predicate needs. Without it the rewrite happens and
-- still ends in a sequential scan, which is the confusing failure.
CREATE INDEX IF NOT EXISTS features_geog_gix
    ON features USING GIST ((geom::geography));

-- ✕ Reads every row: ST_Distance is opaque to the planner
SELECT id, name
FROM   features
WHERE  ST_Distance(geom::geography,
                   ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography) < $3;

-- ✓ Index-assisted: expanded-box overlap, then an exact recheck
SELECT id, name,
       ROUND(ST_Distance(geom::geography, p.pt)::numeric, 1) AS distance_m
FROM   features f,
       LATERAL (SELECT ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography AS pt) p
WHERE  ST_DWithin(f.geom::geography, p.pt, $3)
ORDER  BY f.geom::geography <-> p.pt
LIMIT  $4;

Note that ST_Distance still appears — in the SELECT list, where it runs once per returned row and costs nothing. The rule is the same one that governs transform placement: expensive functions belong on the output side, index-friendly predicates on the filter side.

Two stages versus two million evaluationsTwo execution paths for the same question. The ST_Distance path evaluates the function on all 2.1 million rows and then filters, taking 2100 milliseconds. The ST_DWithin path first uses the GiST index to find rows whose bounding box overlaps a box expanded by the radius, yielding 340 candidates, then applies the exact geodesic distance to those candidates only, keeping 38 and taking 9 milliseconds. The recheck stage is highlighted as the reason the answer is identical.Same question, same answer, two execution shapes✕ ST_Distance(...) < revaluate geodesic distance — 2 100 000 rowskeep 382 100 ms✓ ST_DWithin(..., r)GiST: boxes overlapping box ⊕ r — 340 candidatesexact recheck on 340 → keep 389 msThe recheck is why the two answers are identical: the index stage is deliberately generous, and theexact predicate then removes the false positives it let through.2.1 M point table, 5 km radius, warm cache. The ratio holds across sizes; the absolute numbers do not.

Key Parameters & Options

ConstructIndex-assistedUnitNotes
ST_DWithin(geog, geog, m)yesmetresThe default choice for proximity
ST_DWithin(geom, geom, deg)yesdegreesFast and answers the wrong question
ST_Distance(...) < rno—Always a scan; rewrite it
ST_Distance in SELECTn/ametresCorrect place for it
<-> orderingyes, with GiSTmetres on geographyPair with LIMIT for nearest-N
Radius from a columnno—Pre-compute a buffered column instead

The per-feature radius case deserves a note, because it looks harmless and is not. ST_DWithin(a.geom, b.geom, a.catchment_m) cannot be index-assisted, since the expansion differs per row. The fix is to materialise ST_Buffer(geom::geography, catchment_m)::geometry into its own indexed column and test overlap against that.

Reading the plan

The rewrite either happened or it did not, and the plan says which in one line.

What the plan looks like in each caseTwo EXPLAIN excerpts side by side. The failing plan shows a sequential scan on features with the distance test appearing as a Filter and 2.1 million rows removed by that filter. The healthy plan shows an index scan using the geography GiST index, with the predicate appearing as an Index Cond and only 302 rows removed by recheck. Two annotations point out that the words Filter and Index Cond are the whole diagnosis, and that a large rows-removed count under Filter always means a scan.One word tells you which plan you gotSeq Scan on featuresFilter: (st_distance(...) < 5000)Rows Removed by Filter: 2099962actual time=2101.4..2101.4"Filter" + a huge removal count = a scanIndex Scan using features_geog_gixIndex Cond: (geom::geography && ...)Rows Removed by Recheck: 302actual time=8.9..9.1"Index Cond" + a small recheck = correctA third case exists and is the most confusing:ST_DWithinwith no geography index, whichalso produces a Seq Scan — the rewrite is willing but there is nothing to rewrite onto.If the query looks right and the plan looks wrong, check for the index on the cast expression first.

Where the rewrite stops helping

Index assistance is not unconditional. It works by narrowing the candidate set, so it delivers less and less as the radius grows relative to the data’s extent. Past a certain point the expanded box covers most of the table, every row becomes a candidate, and the plan reverts to a scan with an index lookup bolted on the front.

That crossover is worth knowing, because the fix is different on each side of it. Below it, the answer is always “make sure the index exists and the predicate is index-friendly”. Above it, no index will help and the answer is a coarser data structure: a pre-aggregated summary table, a materialized view, or simply refusing the request with a documented maximum radius.

Index benefit against search radiusQuery time plotted against search radius from 100 metres to 500 kilometres, for a dataset spanning about 400 kilometres. Up to 20 kilometres the indexed query stays under 20 milliseconds while the unindexed comparison sits flat at 2100 milliseconds. From 50 kilometres the indexed line climbs as the candidate set grows, reaching 400 milliseconds at 150 kilometres and converging with the unindexed line at around 300 kilometres, where the expanded box covers nearly the whole dataset. A marker shows the practical maximum radius an API should accept.The index stops paying once the radius approaches the data's extent2 500 ms1 200 ms0ST_Distance — flat, always a full scanST_DWithin — grows with the candidate setpractical maximumcap the API's radius here100 m50 km150 km500 kmA documented maximum radius is a feature, not a limitation — it keeps every accepted request on the fast side.

Gotchas & Failure Modes

  • The geography index missing. ST_DWithin on geography with only a geometry index still scans. The predicate is correct and the plan is not; the index on (geom::geography) is the fix.
  • Casting only one operand. Mixing a geometry and a geography argument either raises function st_dwithin(geometry, geography, numeric) does not exist or silently resolves to the geometry overload with a degree radius. Cast both.
  • A radius that is actually a column. Index assistance requires the radius to be constant for the scan. Materialise a buffered geometry if each feature has its own catchment.
  • ST_DWithin with a huge radius. At continental scale the expanded box covers most of the table and the index stops helping. Above roughly a tenth of the data’s extent, a different access path — or a coarser pre-aggregated table — is needed.
  • Sorting by ST_Distance after filtering with ST_DWithin. Correct, but it re-computes distance for every candidate. Use the <-> operator, which the same index serves for ordering.
  • ANALYZE never run after a bulk load. Without statistics the planner may estimate the candidate set as the whole table and choose a scan anyway. Run ANALYZE after any large import, as in Managing Async Transactions for Bulk Geometry Writes.

One last practical note: the rewrite is worth applying even where the current table is small enough that nobody notices. A distance filter that scans two hundred thousand rows in forty milliseconds looks perfectly healthy in review, and it becomes a two-second query the quarter the table reaches four million. Because the rewrite costs nothing and reads no worse, there is no reason to defer it until the metric turns red.

One last practical note: the rewrite is worth applying even where the current table is small enough that nobody notices. A distance filter that scans two hundred thousand rows in forty milliseconds looks perfectly healthy in review, and it becomes a two-second query the quarter the table reaches four million. Because the rewrite costs nothing and reads no worse, there is no reason to defer it until the metric turns red.

Verification Snippet

-- Prove the rewrite happened
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM features
WHERE ST_DWithin(geom::geography,
                 ST_SetSRID(ST_MakePoint(-0.1276, 51.5072), 4326)::geography, 5000);
-- Index Scan using features_geog_gix on features
--   Index Cond: ((geom)::geography && _st_expand(...))
--   Rows Removed by Recheck: 302

-- And that both forms agree on the answer
SELECT count(*) FROM features
WHERE ST_DWithin(geom::geography, $1::geography, 5000);
SELECT count(*) FROM features
WHERE ST_Distance(geom::geography, $1::geography) < 5000;
-- identical counts, wildly different timings
curl -s "localhost:8000/v1/nearby?lon=-0.1276&lat=51.5072&radius_m=5000" \
  | jq '.results | length, (.[0].distance_m)'
# 38
# 184.6

← Back to Bounding Box & Spatial Index Queries