PostGIS 3.5.2 ST_Contains() query on gist is slow

[Regina Obe]

> 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('0103000020E61000000100000005000000D5A35F11B27A184013
> A1116C5C4E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C
> 402A2F432D179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27
> A184013A1116C5C4E4A40'::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

Bas,

Thanks for the examples.  I was able to use your script to load a database and tested so far on with some pre-release variant of Trixie.

POSTGIS="3.6.0dev 3.6.0beta1-32-gd70143462" [EXTENSION] PGSQL="160" GEOS="3.14.0dev-CAPI-1.20.0" 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.6.0) LIBXML="2.9.14" LIBJSON="0.18" LIBPROTOBUF="1.5.1" WAGYU="0.5.0 (Internal)" PostgreSQL 16.9 (Debian 16.9-1.pgdg130+~20250701.0658.gd742318) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit


Can you output the PostgreSQL version you are using on your old setup?  

SELECT version();


I get the same bad results with the above.  
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'); => 0



So does look to be a selectivity issue as your old isn't even using the spatial index.  I can't tell if it's PostgreSQL or PostGIS at fault here.  
I'm going to test next on 3.4.2 on same instance of  this to see if it's any better. This is on some pre-release version of Trixie I had lying around using PG 16 shipped from apt.postgresql.org.

I assume that if you dropped the spatial index entirely on your new setup you should get the same timing on both systems.