2

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.

WITH p1 AS(
    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(
    WITH
    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)
    )
    SELECT
        PC_Get(pts, 'z') AS z, ST_Distance(pts::geometry, p1.pt, true) as distance
        FROM building_pts, p1
    )
SELECT DISTINCT on (lidar.distance)
lidar.distance,
lidar.z
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
FROM(
    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.