[dashboard] Investigate and fix N+1 query issue

[bmispelon]

I noticed today that Sentry was reporting a possible N+1 query issue on the dashboard homepage.
Here's the query that Sentry is claiming is being repeated:

SELECT dashboard_datum.id, dashboard_datum.content_type_id, dashboard_datum.object_id,
  dashboard_datum.timestamp, dashboard_datum.measurement
FROM dashboard_datum
WHERE (
  dashboard_datum.content_type_id = %s AND dashboard_datum.object_id = %s
)
ORDER BY dashboard_datum.timestamp DESC
LIMIT 1

I'd recomment trying to see ifthe Django Debug Toolbar (installed by default I think) is reporting the duplicated query also. And if so, we should try to fix it. Usually it's a matter of adding a select_related() or prefetch_related() somewhere in the view, but it might also be more complicated than that.

[ontowhee]

DjangoDebugToolbar is reporting this query as repeating 19 times, which corresponds to 1 time for each metric that is displayed on the dashboard.

There is a for loop iterating over the metrics to get the latest datum for each metric.
https://github.com/django/djangoproject.com/blob/main/dashboard/views.py#L19-L30

I'll open a draft PR that seems to reduce the number of sql queries down from 35 to 9 for the dashboard page.

[Mamatha1718]

Hi @bmispelon
As I observed, data is a reverse relationship (defined using GenericRelation in the Metric model), so I have updated the code to use prefetch_related("data") to optimize the query. Please review the changes in the PR.
Will you please let me know any changes need.
Thank you.

[ontowhee]

Would it help to combine the information into a materialized view? Perhaps the view can be updated once an hour so that it is close to real time?