PostGIS 3.5.2 ST_Contains() query on gist is slow

[Sebastiaan Couwenberg]

On 9/10/25 4:29 PM, Regina Obe wrote:
> 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();

Old (bookworm):

osm-nl=# SELECT version();
                                                            version
-----------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 15.14 (Debian 15.14-0+deb12u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14+deb12u1) 12.2.0, 64-bit
(1 row)


New (trixie):

osm-nl=# SELECT version();
                                                       version
-------------------------------------------------------------------------------------------------------------------
  PostgreSQL 17.6 (Debian 17.6-0+deb13u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)


> 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.

Why would I want to drop the idx_nodes_geom? Or did you mean the btree_gist (ix_id_nodes_geom)?

Kind Regards,

Bas

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