PostGIS 3.5.2 ST_Contains() query on gist is slow
Regina Obe
lr at pcorp.us
Mon Sep 8 21:04:37 PDT 2025
> 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)
>
Bas,
Are you upgrading from 3.5.2 to 3.5.3 or from pre 3.5.1 to 3.5.2? sounds from your below comment that is what you are doing, but you are showing 3.5.2 output so not clear if that is your new setup or your old setup that was working.
I am assuming the above is your new setup? What is the full version output of your old setup?
If the above is your new setup, I was thinking it might be an issue with GEOS.
Did you upgrade GEOS as well at the same time. Might be a GEOS issue around the RelateNG work that was done in 3.13.0 and 3.13.1
https://github.com/libgeos/geos/blob/3.13.1/NEWS.md
I'm looking at in particular -
Improve RelateNG performance for A/L cases in prepared predicates (GH-1201, Martin Davis)
3.13.0
Port of RelateNG locationtech/jts#1052 (Martin Davis, Paul Ramsey)
Paul do those impact ST_Contains? I assume they do.
>
> 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 @
> '0103000020E61000000100000005000000D5A35F11B27A184013A1116C5C4
> E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C402A2F432D
> 179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27A184013A11
> 16C5C4E4A40'::geometry)
> Filter: ((id = '8646506472'::bigint) AND
> st_contains('0103000020E61000000100000005000000D5A35F11B27A184013
> A1116C5C4E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C
> 402A2F432D179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27
> A184013A1116C5C4E4A40'::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.
>
To take GEOS out of the equation, what is performance if you just do below compared to your old performance
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_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;
>
> 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
More information about the postgis-users
mailing list