[postgis-users] Help optimizing query

[Luca Bertoncello]

Hi all!

I have a table "elevation", defined as:

gis=# \d+ elevation
                                       Table "public.elevation"
  Column   |       Type       | Collation | Nullable | Default | Storage
| Stats target | Description
-----------+------------------+-----------+----------+---------+---------+--------------+-------------
 latlng    | geography        |           |          |         | main
|              |
 lat       | double precision |           |          |         | plain
|              |
 lng       | double precision |           |          |         | plain
|              |
 elevation | real             |           |          |         | plain
|              |

Hier I saved data about elevation of the terrain...
It works and I can use the data, but this is very slow if I'd like to
get the elevation graph of a path...

Currently I use this function:

CREATE OR REPLACE FUNCTION getElevationsOnLine(startPoint geometry,
endPoint geometry, pointsInterval int)
RETURNS TABLE (
  lat DOUBLE PRECISION,
  lng DOUBLE PRECISION,
  elevation REAL,
  dist DOUBLE PRECISION
)
LANGUAGE plpgsql
AS $$
DECLARE
  point geometry;
BEGIN
  FOR point IN
    SELECT (ST_DumpPoints(ST_Segmentize(ST_MakeLine(startPoint,
endPoint)::geography, pointsInterval)::geometry)).geom
  LOOP
    RETURN QUERY
      SELECT elevation.lat, elevation.lng, elevation.elevation,
elevation.latlng <-> point AS dist
      FROM elevation
      ORDER BY dist LIMIT 1;
  END LOOP;
END;
$$;

The problem is the SELECT elevation.... query.
Using explain analyze I see:

                                                                   QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=165202.56..165202.68 rows=1 width=28) (actual
time=2939.661..2946.685 rows=1 loops=1)
   ->  Gather Merge  (cost=165202.56..297446.23 rows=1133438 width=28)
(actual time=2939.658..2946.680 rows=1 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=164202.54..165619.33 rows=566719 width=28)
(actual time=2828.064..2828.066 rows=1 loops=3)
               Sort Key: ((latlng <->
'0101000020E6100000E17A14AE47412C40BC74931804A64940'::geography))
               Sort Method: top-N heapsort  Memory: 25kB
               Worker 0:  Sort Method: top-N heapsort  Memory: 25kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 25kB
               ->  Parallel Seq Scan on elevation  (cost=0.00..161368.94
rows=566719 width=28) (actual time=3.910..2415.377 rows=453375 loops=3)
 Planning Time: 0.199 ms
 Execution Time: 2946.763 ms

the given coordinate is just to check...

I already tried to define an index over latlng, but it does not help.

Can someone help me to speed up the query?

Thanks a lot
Luca Bertoncello
(lucabert at lucabert.de)