I am trying to calculate the minimum distance between 16 millions of 3D points and 3D lines.
The query is working for more or less 1 million data, but after that, it took so long and never ends. All the tables have spatial index.
The query is:
SELECT
public.points.orden,
st_length(st_shortestline(public.points.point_geometry, public.line01.geom)) as line01_length,
st_length(st_shortestline(public.points.point_geometry, public.line02.geom)) as line02_length,
st_length(st_shortestline(public.points.point_geometry, public.line03.geom)) as line03_length,
st_length(st_shortestline(public.points.point_geometry, public.line04.geom)) as line04_length
ST_3DLENGTH(ST_3DSHORTESTLINE(public.points.point_geometry, public.line01.geom)) as line01_3dlength,
ST_3DLENGTH(ST_3DSHORTESTLINE(public.points.point_geometry, public.line02.geom)) as line02_3dlength,
ST_3DLENGTH(ST_3DSHORTESTLINE(public.points.point_geometry, public.line03.geom)) as line03_3dlength,
ST_3DLENGTH(ST_3DSHORTESTLINE(public.points.point_geometry, public.line04.geom)) as line04_3dlength
into public.lengthdist
FROM public.points, public.line01, public.line02, public.line03, public.line04
LIMIT (SELECT COUNT(*) FROM public.points);
After, I select the min distance:
SELECT public.lengthdist.orden,
min(line01_length),
min(line02_length),
min(line03_length),
min(line04_length),
min(line01_3dlength),
min(line02_3dlength),
min(line03_3dlength),
min(line04_3dlength)
FROM public.lengthdist
GROUP BY public.lengthdist.orden
And also y set the endpoint for each min line with ST_EndPoint
, and I have the same problem.
I tried with CREATE TABLE - INSERT INTO
statement as well, same results. Also with an UPDATE
. Also, I tried with just ST_SHORTESTLINE
without ST_LENGTH
.
How can I do to reduce the never-ending processing time?
I am working in Windows 10 x64, 16GB RAM.
DB Version: 11
OS Type: windows
DB Type: desktop
Total Memory (RAM):16 GB
CPUs num: 4
Connections num: 20 Data Storage: ssd
max_connections = 20
shared_buffers = 512MB
effective_cache_size = 4GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.5
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
work_mem = 22573kB
min_wal_size = 100MB
max_wal_size = 2GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2
P.D.: I've VACUUM ANALYZE
all tables too.
CREATE TABLE AS (...)
should perform a lot better; theSELECT ... INTO
is not really optimized for large insertions. However, I am not sure what your overall goal might be; if it is to find the one closest point (or its distance) to each of your lines, this is the wrong way. – geozelot Mar 10 '20 at 12:51