Adaptation options can be slow to load

[eatyourgreens]

Adaptation options can be slow to load, when the number of features is large. Eg. Transport > Roads > Metro loads ~29k features and there's a noticeable delay.

Tested versions: 0.4.13 and 0.4.12
Tested URL: https://jamaica.infrastructureresilience.org/adaptation?lat=18.00478&lon=-76.88483&zoom=10.98&netTree=11&assets=true&hazards=false&buildings=false&buildingsStyle=type&regions=false&regionsStyle=boundaries&terrestrialStyle=landuse&droughtStyle=adaptation&hazardsStyle=&marineStyle=habitat&assetsStyle=adaptation&drought=false&terrestrial=false&marine=false

Compare with bridges, which load 412 features:
https://jamaica.infrastructureresilience.org/adaptation?lat=18.00478&lon=-76.88483&zoom=10.98&netTree=14&assets=true&hazards=false&buildings=false&buildingsStyle=type&regions=false&regionsStyle=boundaries&terrestrialStyle=landuse&droughtStyle=adaptation&hazardsStyle=&marineStyle=habitat&assetsStyle=adaptation&drought=false&terrestrial=false&marine=false

[eatyourgreens]

I've been testing this to see if #159 slowed down the Adaptation tab, but I also see the same slow performance prior to that PR (v0.4.12.)

[eatyourgreens]

The request for a single page (20 items) of adaptation options is taking 36s, for this particular query.

This is the slow request in the code. It's only slow when the total number of items is large, so maybe this is a slow table scan in Postgres?

irv-jamaica/frontend/src/app/asset-list/use-sorted-features.ts

Lines 60 to 68 in 66e5bde

	const response = await apiClient.features.featuresReadSortedFeatures({
	...layerSpec,
	fieldGroup,
	field,
	dimensions: JSON.stringify(fieldDimensions),
	parameters: JSON.stringify(fieldParams),
	page,
	size: pageSize,
	});

The same query takes 395ms in the backend running locally, so I'm inclined to think the deployed database is missing an index somewhere.

[tomalrussell]

Just had a quick look from here, can confirm the above. There are two main API calls for data here:

• POST /api/attributes/adaptation
  • data loader triggered multiple times from the map, posts a list of feature IDs, returns values to map to feature colour
  • timings look relatively slow from 100ms up to ~15s waiting on server response
  • visible change is as the map features turn from grey to shades of blue
  • https://jamaica.infrastructureresilience.org/api/attributes/adaptation?layer=road_edges_metro&field=avoided_ead_mean&dimensions=%7B%22hazard%22%3A%22flooding%22%2C%22rcp%22%3A%224.5%22%2C%22adaptation_name%22%3A%22Elevate%20the%20roads%22%2C%22adaptation_protection_level%22%3A2.5%7D&parameters=%7B%7D
• GET /api/features/sorted-by/adaptation
  • paginated data fetch triggered once to populate sidebar table (and again each time the table page is changed)
  • timings look very slow, around 40s waiting for the server
  • visible change is in the sidebar, from "Loading..." to data table
  • https://jamaica.infrastructureresilience.org/api/features/sorted-by/adaptation?field=avoided_ead_mean&dimensions=%7B%22hazard%22%3A%22flooding%22%2C%22rcp%22%3A%224.5%22%2C%22adaptation_name%22%3A%22Elevate%20the%20roads%22%2C%22adaptation_protection_level%22%3A2.5%7D&parameters=%7B%7D&sector=transport&subsector=road&asset_type=METRO&page=1&size=20

[eatyourgreens]

POST /api/attributes/adaptation

data loader triggered multiple times from the map, posts a list of feature IDs, returns values to map to feature colour
timings look relatively slow from 100ms up to ~15s waiting on server response
visible change is as the map features turn from grey to shades of blue
https://jamaica.infrastructureresilience.org/api/attributes/adaptation?layer=road_edges_metro&field=avoided_ead_mean&dimensions=%7B%22hazard%22%3A%22flooding%22%2C%22rcp%22%3A%224.5%22%2C%22adaptation_name%22%3A%22Elevate%20the%20roads%22%2C%22adaptation_protection_level%22%3A2.5%7D&parameters=%7B%7D

I've rolled the deployed Docker image back to 0.4.12, to remove changes I made to the data loaders yesterday. I'm still seeing these POST requests taking a long time, so I don't think I broke anything in #159. Loading large numbers of features seems to be intrinsically slow.

This URL should work with 0.4.12 (there was a bug in 0.4.12 that broke the map for the URL that I posted at the top of this issue.)
https://jamaica.infrastructureresilience.org/adaptation?lat=18.00478&lon=-76.88483&zoom=10.98&assets=true&hazards=false&buildings=false&buildingsStyle=type&regions=false&regionsStyle=boundaries&terrestrialStyle=landuse&droughtStyle=adaptation&hazardsStyle=&marineStyle=habitat&assetsStyle=adaptation&drought=false&terrestrial=false&marine=false&netTree=11,11

Running the backend locally, with docker compose, those same POST requests to get the adaptation colours take between 8ms and ~200ms.