Re: PostGIS 3.5.2 ST_Contains() query on gist is slow

Sebastiaan Couwenberg via postgis-users <[email protected]> Tue, 9 Sep 2025 21:19:05 +0200

Newsgroups	gmane.comp.gis.postgis
Message-ID	<[email protected]>

On 9/9/25 9:07 PM, Regina Obe wrote:
> Can you get us a small dump of the data showing the issue or the recipe you used to load the data?

The script to create the database is attached.

It assumes that the osmosis Debian package is installed and the non-standard /var/tmp/osmosis directory exists.

> I'd like to try this out on a couple of versions of PG/ PostGIS I have loaded.

I should be able to test the bookworm setup tomorrow after the data finished loading in the chroot.

Kind Regards,

Bas

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

create-db.sh (application/x-shellscript, 952 B)

#!/bin/bash

set -x

rm -rf /srv/osmosis/tmp/* ; \
dropdb -w osm-nl ; \
createdb -w osm-nl && \
createlang -w plpgsql osm-nl ; \
psql -w osm-nl < /usr/share/postgresql/*/contrib/postgis-*/postgis.sql && \
psql -w osm-nl < /usr/share/postgresql/*/contrib/postgis-*/spatial_ref_sys.sql && \
echo "CREATE EXTENSION hstore" | psql -w osm-nl && \
cd /usr/share/doc/osmosis/examples/ && \
for f in pgsnapshot_schema_0.6{,_action,_bbox,_linestring}.sql; do psql -w -d osm-nl -f $f; done && \
echo "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO osm" | psql -w osm-nl && \
JAVACMD_OPTIONS=-Djava.io.tmpdir=/var/tmp/osmosis osmosis \
	-v 9 \
	--truncate-pgsql host=localhost database=osm-nl user=bas password="$(grep ":bas:" ~/.pgpass | awk -F: '{print $5}' | tr -d '\n')" \
	--read-pbf file=~/data/osm/netherlands.osm.pbf \
	--write-pgsql host=localhost database=osm-nl user=bas password="$(grep ":bas:" ~/.pgpass | awk -F: '{print $5}' | tr -d '\n')"