I have a query to find the maximum spacing between charging stations on any given route, using PostGIS and pgRouting. An example query is below:
select (max(delr) * st_length(line::geography) * 0.000621371) as max_spacing from (select sq2.ratio - coalesce((lag(sq2.ratio) over ( order by sq2.ratio)), 0) as delr, line from (select ST_LineLocatePoint(line, sqa.points) as ratio, line from sp_od_ferry(98282, 98002) as line, (select st_setsrid(st_makepoint(longitude, latitude), 4326) as points from (select longitude, latitude from evses_now where analysis_id = 565 and (connector_code = 1 or connector_code = 3) union select longitude, latitude from zipcode_record where zip = '98282' or zip = '98002') as sq) as sqa where st_dwithin(line::geography, sqa.points::geography, 16093.4) order by ratio asc) as sq2) as sq3 group by sq3.line;
Briefly, the logic is to find the points (charging stations) near the shortest path (given by user-defined function sp_od_ferry()) between origin and destination and find the length of the longest segment between points.
I have to run the above query for several OD pairs, and several of these calculations can be launched in parallel by users. I used AWS RDS performance insights and it found the above query to be the slowest one and causing database slowdown (and 100% CPU usage on the DB instance).
On EXPLAIN ANALYZE, it shows the nested inner loop to be the costliest step. https://explain.dalibo.com/plan/jTf
I understand one way to reduce the database load would be to provision a bigger RDS instance. I currently use (db.t3.small) which has the following specs:
I used pgTune to make the changes to the default AWS RDS Postgres 12.5 settings. The new config is below:
max_connections = 100 shared_buffers = 512MB effective_cache_size = 1536MB maintenance_work_mem = 128MB checkpoint_completion_target = 0.9 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 1.1 effective_io_concurrency = 200 work_mem = 2621kB min_wal_size = 2GB max_wal_size = 8GB
Any suggestions regarding the query or ideas about how I can manage the database load while keeping the costs low are appreciated.