Fix slowness on Scenario Data summary endpoint

[batpad]

Currently, the summary endpoint for eg https://proenergia-staging.ds.io/api/v1/scenario/1/summary/Pop/ is extremely slow.

This endpoint is meant to give summary statistics for a particular key in the scenario data for a scenario.

The view handling querying and aggregating summary data is here: https://github.com/developmentseed/moz-proenergia-backend/blob/main/proenergia/datasets/views.py#L92

Ensuring we know the root cause

Aggregating the summary values across many entries is likely going to be a bit slow in the database regardless, but we should confirm:

• What are all the SQL queries being generated on the page, and how long is each taking.

There could be some quick wins here - for eg. I'm a bit worried about this line: https://github.com/developmentseed/moz-proenergia-backend/blob/main/proenergia/datasets/views.py#L97 which might be triggering a giant query that we can definitely get rid of / do differently.

So as a first pass: log all SQL queries generated and the time each takes. The only thing that should take time is the actual query generating the summary statistics.

Possible Solutions

I think solutions here would broadly fall into three buckets:

• Optimize the SQL, indexes and querying
• Implement some sort of caching
• Throw more hardware at the problem

We should likely look at these in order.

Optimizing SQL and Indexes

There's some ideas for how we could speed up the queries:

• Expensive queries to check .exists(): The two queries to check if ScenarioData and the key exists() seem like they could be unnecessarily expensive. We should get metrics and if its those queries slowing things down, we can definitely do something a lot more light-weight to check exists() rather than the potentially expensive queries being generated there.
• Improve Indexing: There's possibly small tweaks we can do to the db indexes - one I see is a single column index on scenario. There's possibly other tweaks to the GIN index that we could look into / perhaps ask for Bitner's help.
• Improve aggregation by type logic: It maybe more efficient to get the "type" of a key first and then attempt the aggregation based on the type instead of the try / except here: https://github.com/developmentseed/moz-proenergia-backend/blob/main/proenergia/datasets/views.py#L114 - but again, we'd need to measure this to find out, and this is likely not actually an issue.

We should look for quick wins on the SQL optimizing, but I wouldn't get into a rabbit hole of index optimization - if it turns out that a lot of time is actually being taken by ScenarioData.objects.filter(scenario=scenario) and not the aggregate query for eg., then that's something easy to fix.

If it seems like we just do have too many entries in ScenarioData and the aggregation query is just going to be slow, then I would strongly advocate for a caching strategy.

Caching Strategy

When looking at this, my first instinct was that the easiest thing to do would be to create a separate table with precomputed summary values, and handle updating summary values on updates to ScenarioData.

So:

• A new model like ScenarioDataSummary or so with scenario_id, key, type, count, min, max, etc.
• A management command to update summaries for a scenario
• A celery task or so to trigger updating summaries when scenario data changes

This would likely not be a good idea of scenario data is constantly being updated, but if it is not updated much, this seems like the nicest solution to me.

Increasing database hardware

@willemarcel let's get a good handle on the queries being generated and be sure of what's causing the slowness. We can definitely throw more hardware at the database, but I want to make sure we first due our diligence on optimizing and treat that as a last resort.

---

My hunch is that even if we fix some issues in the queries being generated and the indexes, doing these aggregations over ~1 million values in the database is going to be a bit slow (even if we bring it down to 4-5s, that's still "slow"). I think I would very much advocate for precomputing these summary values into a separate table. @willemarcel do you see a big problem with that approach?

[willemarcel]

The summary will be used for 2 different goals:

• Populating the filter options: the frontend needs to show the options of filtering, so for a numeric filter, we need the min and max values, for a text filter, we need the possible values.
• Summary information: the frontend also needs to show a summary of the results we are showing in the map, example: the total population, the total electrification cost, or the population by region.

In the first case we are not going to apply any filters, so the results for a scenario would be always the same and the cache or precomputing would solve the issue. However, in the second case, the results will be dynamic, as the users can apply multiple filters and precomputing or cache would not help.

About the exists() queries, it's not expensive, as the query stops when it finds the first result. I believe it's better to have it and avoid executing the complete query later.

[batpad]

@willemarcel ha, that makes a lot of sense, thanks for the context.

In that case, I think my suggestion would be:

• Still do a bit of analyzing of queries and EXPLAIN ANALYZE in the db to see if we can tweak indexes a bit, etc. There might be small things we can do to improve performance by a bit. If you can send me a file and instructions on importing data locally, I can try and help here.
• Implement some Django Cache layer to reduce db hits - I think this could be a simple FileSystem or DatabaseCache and some default Django caching to cache responses for the same request for some period of time
• Give ourselves a better database - on staging, I'd just move to RDS and give ourselves a bit of a better instance

I'll go ahead and work on moving our staging setup to RDS, and then also do some work on tuning the postgres config for when we move this to an on-prem server. But let's chat about this - this is likely hard to get super-fast with Postgres, so we'll likely need to figure out what an acceptable level of performance is (cc @hanbyul-here )

cc @geohacker for any Postgres wisdom :-)

[hanbyul-here]

It is difficult to draw an arbitrary line re: latency. being < 1 sec for any query will be good, but if it is not feasible, that is what it is. Rather, I would like to know what we can do for improvement and fine tune the UI/UX after improvement. (I am currently setting up timeout limit to be 6 secs with no specific reasons - but we can think of more ways to extend this if we need to).

It will be great to see improvements especially for endpoints to populate UIs - which I think it will be improved with cache layer since there will be no difference between requests.

[geohacker]

I'd have to dig into the models and queries a bit more to be useful, but some thoughts:

• It looks like the only index is the GIN on the metadata column of ScenarioData https://github.com/developmentseed/moz-proenergia-backend/blob/main/proenergia/datasets/models.py#L212
• For aggregations, I'd imagine an expression index would be very helpful. So something like CREATE INDEX CONCURRENTLY idx_scenario_metadatakey ON scenariodata ((attrs->>'some_key_thats_used_in_aggregation'));. We can make a few of these until we think the index is outgrowing. We'd need to know most used keys.
• There are a couple of type casting happening. If this is across millions of rows, that's going to be quite expensive. Is there a way we can avoid this? The other option is also to add casting in the index, but it's not super useful (((attrs->>'some_key_thats_used_in_aggregation')::numeric));
• Can I see what query the .exists is generating? Ideally, it's using the GIN index, but there are probably a couple of things we can solve there.
• We also probably can just avoid the entries_with_key.exists altogether https://github.com/developmentseed/moz-proenergia-backend/blob/main/proenergia/datasets/views.py#L125, by just checking the count after query is run? This maybe overall cheaper especially because the keys are known from the frontend. Like:

query = entries_with_key.annotate(
                    key=Cast(KT(f"metadata__{key}"), FloatField())
                ).aggregate(Max("key"), Min("key"), Sum("key"), Count("key"))

if query["count"] == 0:

• As a final straw, we can also just store the keys are columns for more efficient queries.

[batpad]

Ok, so I got things loaded into a db locally and am able to log the SQL. The .exists() queries etc are all fine, Django does them intelligently with a LIMIT 1 etc so they don't take time (<10ms) - so the query that takes time is the aggregate query which is understandably slow - on my laptop, this is taking just over 9 seconds.

The query:

SELECT MAX((("datasets_scenariodata"."metadata" ->> 'Pop'))::double precision) AS "key__max",
       MIN((("datasets_scenariodata"."metadata" ->> 'Pop'))::double precision) AS "key__min",
       SUM((("datasets_scenariodata"."metadata" ->> 'Pop'))::double precision) AS "key__sum",
       COUNT((("datasets_scenariodata"."metadata" ->> 'Pop'))::double precision) AS "key__count"
  FROM "datasets_scenariodata"
 WHERE ("datasets_scenariodata"."scenario_id" = 1 AND "datasets_scenariodata"."metadata" ? 'Pop')

This is the EXPLAIN on the query:

QUERY PLAN
--
Aggregate  (cost=26030.86..26030.87 rows=1 width=32) (actual time=9139.301..9139.302 rows=1 loops=1)
->  Bitmap Heap Scan on datasets_scenariodata  (cost=74.02..25740.58 rows=7257 width=1686) (actual time=67.248..2229.645 rows=726530 loops=1)
Recheck Cond: (metadata ? 'Pop'::text)
Filter: (scenario_id = 1)
Rows Removed by Filter: 3588
Heap Blocks: exact=50265 lossy=131716
->  Bitmap Index Scan on datasets_sc_metadat_9a5d28_gin  (cost=0.00..72.21 rows=7668 width=0) (actual time=61.840..61.840 rows=730118 loops=1)
Index Cond: (metadata ? 'Pop'::text)
Planning Time: 0.398 ms
Execution Time: 9139.374 ms

cc @geohacker

[batpad]

I'm thinking the only good solution here is to do some denormalization and move values that we need to do aggregations on to a separate table with values having the correct types and proper indexes.

Am going to test this out and sketch things out. This will mean that somewhere we will need to be able to specify "Summary Keys" or so where the keys that need summaries can be configured, along-with what type they are. I'll do a dirty sketch of this in a branch to have a proof-of-concept and then @willemarcel I think we can discuss the best way you want to do this.

[batpad]

With moving these key / values to a separate table with defined types for numbers and strings and composite indexes, all queries - summaries and summaries with filters are down to <100ms locally.

This will require us to pre-define what fields need to be used in summaries and filters. The implementation I'm proposing roughly:

• Add a summary_numeric_fields and summary_string_fields to DataModel to specify which fields need to be included in this new ScenarioDataMetrics table.
• Create a new ScenarioDataMetrics model with scenario_data_id, key, numeric_value, string_value
• Management command to update metrics table / populate it when data loading happens

I've got this working locally and I think this is an okay approach? With the JSON field, it seemed impossible to index things in a way that prevented the query from needing to load the entire JSON object for each row into memory. Even with adding an index for specific keys - eg. adding an index to the Pop key specifically, the query time came down from about 9 seconds to about 8 seconds, not a significant enough improvement, and the plan showed that it still needed to scan through all the JSON.

I do think we need to move the key / values into a separate table for performance. Am hoping we don't need summarization on ALL the keys present - though even if we do, we should still break it up into a separate table.

@willemarcel let me know if this roughly makes sense? I tested that the summary endpoints and filters all work and are about 200x faster with this approach - I'll push a rough PR and hopefully we can discuss next steps here.

[willemarcel]

I think the main issue here is that the filters structure are more complex than the ScenarioDataMetrics is providing. Often we need to filter based on multiple keys at once, not only in the key we are getting the summary data. For example, I need the summary for the Pop key, but I want to filter it based on Posto, PopStartYear, State, Cost and some other keys. I think it's possible to aggregate the feature_ids values after querying based on multiple values on ScenarioDataMetrics. Could you test if the performance improvement will remain in this use case?

[batpad]

@willemarcel one can use multiple filters comma-separated on the endpoint. This was definitely the convoluted bit to get multiple filters working by directly querying the database and not filtering filter_ids in python - it's this Subquery stuff that does that: https://github.com/developmentseed/moz-proenergia-backend/pull/59/changes#diff-21e3a25d274a1736b146f7abd12fc017df9608ec13db7bc996935d4d9fbf61c4R261

I tested it with multiple filters and it all worked well and was very fast, but would be good to maybe show you and clarify this is what you mean.

Also, all the fields that need to be filtered on need to also be added to summary_string_fields or summary_numeric_fields so they get added to the metrics table - perhaps we should name this differently to clarify that both fields that we need summaries on and that we need summaries filtered on need to be added to these definitions so they are in the metrics table.

[batpad]

is to do some denormalization

And sorry "denormalization" was totally the wrong word to use here haha - I just meant "flattening the JSON field to a separate table".

[willemarcel]

Another detail to have in mind is that if a new version of the Scenario is uploaded, we need to update the ScenarioDataMetrics entries and delete the feature_ids that don't exist in the new version.