2

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 ?

  • 2
    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 – mlinth Jan 4 '16 at 12:13
  • oh yes ! from 10s to 1ms, I guess you were right. If you answer the question I will be happy to accept it. – Istopopoki Jan 4 '16 at 13:23
5

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.