I'm using PostGIS + pointcloud to store a huge dataset of LiDAR point (2M patches of 600points) I query this database to obtain the altimetic profile between two points.

At the moment I have a huge list of points in my client sofware and I execute many select statements over the DB to obtain these profiles.

I tried to rewrite the query to perform more than one profile at the time, but the query execution time increases more than lineary with the number of pairs.

    SELECT ST_Centroid(geom) as pt FROM osm_table
    WHERE  gid=12345
p2 as(
    SELECT ST_Centroid(geom) as pt FROM osm_table
    WHERE  gid=54321
buffer AS(
    SELECT ST_Buffer_Meters(ST_MakeLine(p1.pt, p2.pt), {4}) AS line FROM p1,p2
lidar AS(
    patches AS (
        SELECT pa FROM lidar_table
        JOIN buffer ON PC_Intersects(pa, line)
    pa_pts AS (
        SELECT PC_Explode(pa) AS pts FROM patches
    building_pts AS (
        SELECT pts, line FROM pa_pts JOIN buffer
        ON ST_Intersects(line, pts::geometry)
        PC_Get(pts, 'z') AS z, ST_Distance(pts::geometry, p1.pt, true) as distance
        FROM building_pts, p1
SELECT DISTINCT on (lidar.distance)
FROM lidar 
ORDER BY lidar.distance;

This is the query I was able to write, i would like to optimize it to calculate the profile from one to many buildings using the parallel query that are available in my PostgreSQL version (10).

How could I do that?

UPDATE: I rewrote my query to process multiple destination point together, however the query planner is not using any parallel worker. Even by setting force_parallel_mode=on.

SELECT array_agg(array[distance, z]) as profile, b1, b2
    SELECT distance, z, b1, b2
    FROM (
        SELECT distinct on (distance) PC_Get(pts, 'z') as z, ST_Distance(pts::geometry, p1, true) as distance, b1, b2
        FROM (
            WITH buffer AS(
                SELECT ST_Buffer_Meters(ST_MakeLine(ST_Centroid(osm1.geom), ST_Centroid(osm2.geom)), 1) as line, ST_Centroid(osm1.geom) as p1, osm1.gid as b1, osm2.gid as b2
                FROM osm_table osm1, osm_table osm2
                WHERE osm1.gid  = 925093 AND osm2.gid IN (923131, 923133, 923132, 923139, 923130, 923123)
            SELECT PC_Explode(lidar_table.pa) as pts, b1, b2, p1, line FROM lidar_toscana
            JOIN buffer  ON PC_Intersects(pa, buffer.line)
        WHERE ST_Intersects(pts::geometry, line)
    ORDER BY b1, b2, distance
GROUP BY b1, b2

I started tearing apart the query to see what piece is killing the parallel processing. Apparently as soon as I use a CTE or the function PC_Explode, the query planner stops using parallel processing.

Query plan of the 2nd query:

GroupAggregate  (cost=1278448.96..1278454.32 rows=153 width=40)"
Group Key: _.b1, _.b2"
->  Sort  (cost=1278448.96..1278449.34 rows=153 width=48)"
Sort Key: _.b1, _.b2, _.distance"
->  Subquery Scan on _  (cost=1278441.11..1278443.41 rows=153 width=48)"
->  Unique  (cost=1278441.11..1278441.88 rows=153 width=48)"
->  Sort  (cost=1278441.11..1278441.50 rows=153 width=48)"
Sort Key: (_st_distance((st_geomfromewkb(pc_asbinary(__1.pts)))::geography, (__1.p1)::geography, '0'::double precision, true))"
->  Subquery Scan on __1  (cost=15.05..1278435.56 rows=153 width=48)"
Filter: ((st_geomfromewkb(pc_asbinary(__1.pts)) && __1.line) AND _st_intersects(st_geomfromewkb(pc_asbinary(__1.pts)), __1.line))"
->  ProjectSet  (cost=15.05..24895.40 rows=4600000 width=104)"
CTE buffer"
->  Nested Loop  (cost=0.85..14.64 rows=6 width=72)"
->  Index Scan using gid_osm_centro on osm_centro osm1  (cost=0.43..2.65 rows=1 width=164)"
Index Cond: (gid = 925093)"
->  Index Scan using gid_osm_centro on osm_centro osm2  (cost=0.43..10.37 rows=6 width=164)"
Index Cond: (gid = ANY ('{923131,923133,923132,923139,923130,923123}'::integer[]))"
->  Nested Loop  (cost=0.41..1846.27 rows=4600 width=90)"
->  CTE Scan on buffer  (cost=0.00..0.12 rows=6 width=72)"
->  Index Scan using lidar_toscana_index on lidar_toscana  (cost=0.41..306.92 rows=77 width=18)"
Index Cond: (buffer.line && st_geomfromewkb(pc_envelopeasbinary(pa)))"
Filter: _st_intersects(buffer.line, st_geomfromewkb(pc_envelopeasbinary(pa)))"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.