Hello,
As I hope that the problem reported in BUG # 18005 can be reproduced on
your site, I will now attach some script-files to start the SQL´s and my
"postgresql.conf" file.
Prerequisites:
-A server with:
Ubuntu-2204-jammy-amd64-base (if possible)
At least 128 GB RAM,
At least 8 CPUs
At least 2 TB NVME
Of course Postgresql (14.8)
OSM2PGSQL (version 1.7.2 if possible, the attached LUA file is not
compatible with 1.6.x versions)
Fast Internet connection to download the OSM maps (pbf format)
Please find attached some shell and SQL scripts, that should be stored in
the same directory.
A sub-directories "pbf" should be created, the 2 log files are created
during the run in background (nohup)
-rw-r--r-- 1 postgres postgres 26278 Jun 17 20:49 all.sql
-rwxrwxr-x 1 postgres postgres 256 Jun 15 16:48 create-tags.sh
-rw-r--r-- 1 postgres postgres 51 Jun 15 16:47 extend.sql
-rw-rw-r-- 1 postgres postgres 183568 Jun 27 07:33 gen-europe.log
-rwxrwxr-x 1 postgres postgres 46 Jun 15 16:48 gen-europe.sh
-rw-rw-r-- 1 postgres postgres 164758 Jun 26 16:34 gen-rest.log
-rwxrwxr-x 1 postgres postgres 354 Jun 26 14:14 gen-rest.sh
-rwxrwxr-x 1 postgres postgres 163 Jun 15 16:49 get-pbf.sh
-rw-r--r-- 1 postgres postgres 5646 Jun 15 16:49 mycfg.lua
-rwxrwxr-x 1 postgres postgres 252 Jun 16 13:20 noh-gen-all.sh
drwxrwxr-x 2 postgres postgres 4096 Jun 27 07:33 pbf
Grant execution rights and start with
./noh-gen-all.sh
After 2 / 3 hours I could see in the log "gen-rest.log" the described
hanging SELECT!
I had 3 times by 3 tests:
(
-- create tags for noise
-- create raw data
-- when several highways-segments are producing noise, aggregate the
noises using the "ST_Union" of the segments!
-- (better as using "sum" or "max" that do not deliver good factors)
SELECT
m.osm_id losmid, m.highway lhighway, q.highway as qhighway,
q.maxspeed_class,
case............
)
The select needs in non-parallel mode nearly 55 minutes for "asia" and 45
minutes for "north-america, less for the other continents.
To be sure that the SELECT hangs, consider to look at the ps_stat_activity:
the time stamps of the hanging SQL´s (1 or 3 processes) are very strange
(as if the SQL never started at all)
Regards
EssBee