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 ?

• mlinth: 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: postgis.net/docs/ST_Transform.html
• Istopopoki: oh yes ! from 10s to 1ms, I guess you were right. If you answer the question I will be happy to accept it.

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: