I have the following table :
Table "osm_france.france_2po_4pgr"Column | Type | Modifiers ---------------+---------------------------+-----------id | integer | not nullosm_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 whereST_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: 5225288Planning time: 0.517 msExecution time: 9407.390 msWhy is this query not using the available spatial index ?
أكثر...
Table "osm_france.france_2po_4pgr"Column | Type | Modifiers ---------------+---------------------------+-----------id | integer | not nullosm_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 whereST_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: 5225288Planning time: 0.517 msExecution time: 9407.390 msWhy is this query not using the available spatial index ?
أكثر...