PostGIS 3.5.2 ST_Contains() query on gist is slow

[Sebastiaan Couwenberg]

On 9/9/25 8:32 PM, Paul Ramsey wrote:
> It’s a shame you cannot provide the selectivity on the other machine, because this way we are just guessing. The fact that selectivity is coming back at zero does maybe indicate a change in selectivity calculations, and zero is smaller than a lot of numbers, so perhaps that’s driving it, but that’s a big guess.

Results from a bookworm chroot:

osm-nl=# SELECT _postgis_selectivity ('nodes', 'geom', ST_GeomFromEWKT('SRID=4326;POLYGON((6.11982 52.6121955,6.11982 53.2038323,7.0927397 53.2038323,7.0927397 52.6121955,6.11982 52.6121955))'), '2');
  _postgis_selectivity
----------------------
   0.07498395609060923
(1 row)


osm-nl=# SELECT postgis_full_version();
                                                                   postgis_full_version
--------------------------------------------------------------------------------------------------------------------------------------------------------
  POSTGIS="3.3.2 4975da8" PGSQL="150" GEOS="3.11.1-CAPI-1.17.1" PROJ="9.1.1" LIBXML="2.9.14" LIBJSON="0.16" LIBPROTOBUF="1.4.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=1.14..42.18 rows=1 width=8) (actual time=23.265..23.267 rows=0 loops=1)
    Buffers: shared read=10
    ->  Index Scan using idx_way_nodes_node_id on way_nodes wn  (cost=0.57..8.59 rows=1 width=16) (actual time=7.631..7.633 rows=1 loops=1)
          Index Cond: (node_id = '8646506472'::bigint)
          Buffers: shared read=5
    ->  Index Scan using pk_nodes on nodes n  (cost=0.57..33.59 rows=1 width=8) (actual time=15.627..15.627 rows=0 loops=1)
          Index Cond: (id = '8646506472'::bigint)
          Filter: st_contains('0103000020E61000000100000005000000D5A35F11B27A184013A1116C5C4E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C402A2F432D179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27A184013A1116C5C4E4A40'::geometry, geom)
          Rows Removed by Filter: 1
          Buffers: shared read=5
  Planning:
    Buffers: shared hit=123 read=3
  Planning Time: 1.477 ms
  Execution Time: 23.344 ms
(14 rows)


Kind Regards,

Bas

-- 
  GPG Key ID: 4096R/6750F10AE88D4AF1
Fingerprint: 8182 DE41 7056 408D 6146  50D1 6750 F10A E88D 4AF1