SQL/Postpass - One runs signicantly slower than others

Help and support
,
1

This sql/postpass routine completes successfully in ~2 seconds

{{data:sql,server=https://postpass.geofabrik.de/api/0.2/}}
SELECT osm_id, osm_type, tags, geom
FROM postpass_line
WHERE
    ST_DWithin(
        geom::geography, ST_SetSRID(ST_MakePoint(-2.35969,51.38138),4326)::geography,
        500
    )
     AND tags->>'highway' ~ '^(footway|steps|track|cycleway|bridleway|pedestrian|service|residential|unclassified)$'
 AND tags ? 'prow_ref'

Prow_ref query

If I replace prow_ref with other values ie surface, or remove AND tags ? 'prow_ref' completely it takes about 4 minutes to run. The returned data from that is about 2x to 4x the amount of the original query.

I have a few other sql/postpass routines, run within overpass turbo or a leaflet html, which download much more data, in seconds.

Why is causing this one so much delay?

2

Likely indexing, if I had to guess

3

see related Postpass - Why does execution speed vary a lot depending on tags?

4

processing cost is not directly correlated with output size

5

You haven’t asked for this advice and it is not the main reason for slowness. However, I still recommend to use regular expressions if they are really necessary only. Cases like this can be written as tags->>'highway' IN ('footway', 'steps', 'track', 'cycleway', 'bridleway', 'pedestrian', 'service', 'residential', 'unclassified') if you don’t intend to catch a highway=bridleway_simulation as well.

But back to your original question:

Database queries are slow if the query engine has to read all entries in the table (“full table scan”). Queries become reasonable performant if they can use an index. The database at postpass.geofabrik.de has a spatial index on the geom (type Geometry) column and an index on the keys of the tags column (type JSONB). If you cast geom to geography, your query cannot use the spatial index.

This means, the query optimizer uses the index on tags if the queries key (prow_ref) is no frequent OSM key. However, surface is very common.

If your spatial condition involves casting to geography you have to add a second condition without casting as a rough filter. In your case, you can add a ST_Expand(ST_SetSRID(ST_MakePoint(-2.35969,51.38138),4326), 0.005) && geom (distance to be adapted to your latitude). The distance should be larger than 500 meter. It only serves as a rough filter to skip most data.

6

tags ? 'prow_ref is the only part of that query that can use an index. Regular expressions can never use an index, and more generally tags->>'key' == 'value' can’t use the an index. Treat the ->> operator as being independent of what you do with the result of that operation.

The PostgreSQL docs go over what the GIN index covers but generally you want to use contains and overlap operators - equivalent to && if you were doing arrays.

7

To expand, on postpass.geofabrik.de there is a GIN index on tags, and PostgreSQL can only use this index for some JSONB operations, (documented here).

You can replace tags->>'key'='value' with tags @> '{"key":"value"}'::jsonb and it should[1] use the tags index.

  1. haha postgresql might not use it, who knows, pray to your gods ↩︎