PostGIS 3.5.2 ST_Contains() query on gist is slow

[Sebastiaan Couwenberg]

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