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

Help and support
1

While experimenting with @woodpeck’s Postpass I was running this routine within a Leaflet HTML multiple times when it suddenly increased in execution speed, something around 10x faster.

SELECT osm_id, osm_type, tags, geom
    FROM postpass_pointpolygon p
    WHERE 
			p.osm_type='N'
      AND tags->>'railway'='station'
			AND tags ? 'ref:crs'
      AND EXISTS (
        SELECT 1 FROM postpass_polygon r
        WHERE r.osm_type='R'
        AND r.osm_id IN (58447,58437,58446)
        AND ST_Contains(r.geom, p.geom)
      )

As soon as I change one line from:
AND tags ? 'ref:crs'
to:
AND tags ? 'network'
it suddenly slowed back down & never recovered.

An AI client explained it was something to do with ‘caching on the server’ but it was unclear as to the reasons why.

What’s the logic behind it, and is there a technique to get the amended routine (and any others I write) to cache & run fast?

2

AFAIK only a limited subset of tags is indexed.

Maybe @woodpeck can give a bit more insight.

3

'ref:crs' seems a bit of a niche tag to cache (but glad it does).

4

Whilst I’ve not used it myself, I note that there’s an /explain endpoint:

That should allow you to see the query plan, which could give you an idea as to why the performance changed. One possible reason that occurs to me is that it could simply be because filtering for ref:crs is more selective than for network. Taginfo currently shows that the former has 2953 instances, whereas the latter has 3,706,753 instances.

1 Like
5

There’s only 3k ref:crs world-wide but 4m network which probably makes the network query slower. I would re-write your query as

WITH area AS (
  SELECT geom 
  FROM postpass_polygon
  WHERE osm_type='R' 
  AND osm_id IN (58447,58437,58446))
  
SELECT osm_id, osm_type, tags, p.geom
FROM postpass_point p, area
WHERE tags @> '{"railway": "station"}'::JSONB
AND tags ? 'network'
AND ST_Contains(area.geom, p.geom)

but that seems to give only a negligible performance benefit (you’re using the “pointpolygon” in your outer query but then only ask for nodes - cheaper to use just “point”).

I was able to speed up the query by adding a hard-coded bounding box for the UK though:

AND p.geom && ST_MakeEnvelope(-9.36,49.67,2.1,61.07,4326)

The && operator is much faster than any ST_Contains, and apparently this hint helps PostGIS to pre-filter points with the bounding box before entering the actual ST_Contains calculation.

2 Likes