Postgis spatial index not used

Question

I have the following table :

          Table "osm_france.france_2po_4pgr"
Column     |           Type            | Modifiers 
---------------+---------------------------+-----------
id            | integer                   | not null
osm_id        | bigint                    | 
osm_name      | character varying         | 
osm_meta      | character varying         | 
osm_source_id | bigint                    | 
osm_target_id | bigint                    | 
clazz         | integer                   | 
flags         | integer                   | 
source        | integer                   | 
target        | integer                   | 
km            | double precision          | 
kmh           | integer                   | 
cost          | double precision          | 
reverse_cost  | double precision          | 
x1            | double precision          | 
y1            | double precision          | 
x2            | double precision          | 
y2            | double precision          | 
geom_way      | geometry(LineString,4326) | 
Indexes:
   "pkey_france_2po_4pgr" PRIMARY KEY, btree (id)
   "france_2po_4pgr_gix" gist (geom_way)
   "idx_france_2po_4pgr_source" btree (source)
   "idx_france_2po_4pgr_target" btree (target)

As you can see, a spatial index is available for the geom_way column. I have performed a VACUUM ANALYZE on the table for safety.

Now I run this very simple query :

select * from osm_france.france_2po_4pgr where
ST_DWITHIN(St_TRANSFORM(geom_way,3035),ST_transform(ST_SetSRID(ST_MakePoint(2.3, 48.8),4326),3035),100);

So I'm looking for all the geometries within 100 meters from a given point (I transform the SRIDs from 4326 to 3035 to specify a range in meters).

The explain analyze yields :

Seq Scan on france_2po_4pgr  (cost=0.00..1630779.00 rows=69644 width=293) (actual time=35.044..9407.356 rows=3 loops=1)
Filter: ((st_transform(geom_way, 3035) && '0103000020DB0B0000010000000500000046D8C5565EA84C41A950F47CDFFF454146D8C5565EA84C41A950F47C4300464146D8C556C2A84C41A950F47C4300464146D8C556C2A84C41A950F47CDFFF454146D8C5565EA84C41A950F47CDFFF4541'::geometry) AND ('0101000020DB0B000046D8C55690A84C41A950F47C11004641'::geometry && st_expand(st_transform(geom_way, 3035), 100::double precision)) AND _st_dwithin(st_transform(geom_way, 3035), '0101000020DB0B000046D8C55690A84C41A950F47C11004641'::geometry, 100::double precision))
Rows Removed by Filter: 5225288
Planning time: 0.517 ms
Execution time: 9407.390 ms

Why is this query not using the available spatial index ?

Answer 1

I suspect it's because you're using ST_TRANSFORM. Perhaps you can try building an index on st_transform(geom_way,3035) - see here: