ST_DWithin: Why does query not use the Spatial Index?

Question

I want my query to output all street segments, that are within a distance of 20 meters of those segments which have the attribute clazz of 11 (motorways).

I have this query from a user on this site:

SELECT DISTINCT s.id, s.clazz
FROM berlin_2po_4pgr s
INNER JOIN berlin_2po_4pgr m 
ON ST_DWithin(s.geom_way::geography, m.geom_way::geography, 20)
WHERE m.clazz = '11' AND s.clazz != '11';

I have a index:

CREATE INDEX gist_index_berlin_2po_4pgr ON berlin_2po_4pgr USING gist(geom_way);

The query takes a very long time (more than two minutes). Probably the reason is that the index isn't used:

"HashAggregate  (cost=8076781.04..8076781.13 rows=9 width=8)"
"  ->  Nested Loop  (cost=0.00..8076781.00 rows=9 width=8)"
"        Join Filter: (((s.geom_way)::geography && _st_expand((m.geom_way)::geography, 20::double precision)) AND ((m.geom_way)::geography && _st_expand((s.geom_way)::geography, 20::double precision)) AND _st_dwithin((s.geom_way)::geography, (m.geom_way)::g (...)"
"        ->  Seq Scan on berlin_2po_4pgr s  (cost=0.00..2267.43 rows=49871 width=101)"
"              Filter: (clazz <> 11)"
"        ->  Materialize  (cost=0.00..2270.24 rows=563 width=93)"
"              ->  Seq Scan on berlin_2po_4pgr m  (cost=0.00..2267.43 rows=563 width=93)"
"                    Filter: (clazz = 11)"

Why? The SRID is 4326, so unit is degrees. But the cast to geography changes the units to meters, right?

The output is correct, so unit can't be the problem.

Answer 1

You index is on geometry not geography and you are casting your geometry to geography.

If you want it to use an index, you need to add a geography index like so:

CREATE INDEX gist_geog_berlin_2po_4pgr ON berlin_2po_4pgr USING gist( (geom_way::geography) );