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
