Since the upgrade to Debian trixie with PostgreSQL 17.6 and PostGIS 3.5.2 the
queries to fetch all ways connected to a boundary with nodes within a bounding
box are terribly slow.
osm-nl=# SELECT postgis_full_version();
postgis_full_version
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
POSTGIS="3.5.2 dea6d0a" PGSQL="170" GEOS="3.13.1-CAPI-1.19.2" PROJ="9.6.0 NETWORK_ENABLED=OFF
URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/tmp/proj DATABASE_PATH=/usr/share/proj/proj.db" (compiled against PROJ 9.5.1)
LIBXML="2.9.14" LIBJSON="0.18" LIBPROTOBUF="1.5.1" WAGYU="0.5.0 (Internal)"
(1 row)
osm-nl=# EXPLAIN (ANALYZE, BUFFERS) SELECT wn.way_id FROM way_nodes AS wn,
nodes AS n WHERE wn.node_id = 8646506472 AND wn.node_id = n.id AND
ST_Contains(ST_GeomFromEWKT('SRID=4326;POLYGON((6.1198199
52.612195500000006,6.1198199 53.2038323,7.0927397 53.2038323,7.0927397
52.612195500000006,6.1198199 52.612195500000006))'), n.geom);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.99..29.54 rows=1 width=8) (actual time=5648.531..5648.532
rows=0 loops=1)
Buffers: shared hit=6744524 read=845409 written=5
-> Index Scan using idx_way_nodes_node_id on way_nodes wn (cost=0.57..8.59
rows=1 width=16) (actual time=0.021..0.022 rows=1 loops=1)
Index Cond: (node_id = '8646506472'::bigint)
Buffers: shared read=5
-> Index Scan using idx_nodes_geom on nodes n (cost=0.42..20.94 rows=1
width=8) (actual time=5648.507..5648.507 rows=0 loops=1)
Index Cond: (geom @
'0103000020E61000000100000005000000D5A35F11B27A184013A1116C5C4E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C402A2F432D179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27A184013A1116C5C4E4A40'::geometry)
Filter: ((id = '8646506472'::bigint) AND
st_contains('0103000020E61000000100000005000000D5A35F11B27A184013A1116C5C4E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C402A2F432D179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27A184013A1116C5C4E4A40'::geometry,
geom))
Rows Removed by Filter: 9886042
Buffers: shared hit=6744524 read=845404 written=5
Planning:
Buffers: shared hit=11 read=15
Planning Time: 0.294 ms
Execution Time: 5648.551 ms
(14 rows)
This is a database with OSM data from Geofabrik created with Osmosis using its
pgsnapshot schema.
osm-nl=# \d+ way_nodes;
Table "public.way_nodes"
Column | Type | Collation | Nullable | Default | Storage | Compression
| Stats target | Description
-------------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
way_id | bigint | | not null | | plain |
| |
node_id | bigint | | not null | | plain |
| |
sequence_id | integer | | not null | | plain |
| |
Indexes:
"pk_way_nodes" PRIMARY KEY, btree (way_id, sequence_id)
"idx_way_nodes_node_id" btree (node_id)
Access method: heap
osm-nl=# \d+ nodes;
Table "public.nodes"
Column | Type | Collation | Nullable | Default |
Storage | Compression | Stats target | Description
--------------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | bigint | | not null | |
plain | | |
version | integer | | not null | |
plain | | |
user_id | integer | | not null | |
plain | | |
tstamp | timestamp without time zone | | not null | |
plain | | |
changeset_id | bigint | | not null | |
plain | | |
tags | hstore | | | |
extended | | |
geom | geometry(Point,4326) | | | |
main | | |
Indexes:
"pk_nodes" PRIMARY KEY, btree (id)
"idx_nodes_geom" gist (geom)
Access method: heap
Increasing the work_mem from 40MB to 400MB did not help as expected as this
worked fine on bookworm and earlier.
I suspect there may be a regression in PostGIS 3.5.2, but I'm at a loss on how
to confirm and fix that.
The changes in the 3.5.3 NEWS don't seem to be related.
Has anyone encountered this issue as well and/or get suggests solutions?
Kind Regards,
Bas
--
GPG Key ID: 4096R/6750F10AE88D4AF1
Fingerprint: 8182 DE41 7056 408D 6146 50D1 6750 F10A E88D 4AF1
