Check possibility to rewrite the homepage view to have lazy load with parallel database queries

[pankajkoti]

Description

Performance Analysis of Home Page Query Execution

I have analyzed the queries executed on the home page and attaching herewith the top 5 slow queries observed with enabling pg_stat_statements in the postgres container and having about 40k DAG locally with Breeze. My observations indicate that we have the necessary indexes for those queries, and adding any additional indexes does not improve performance.

hp_slow_query_1.txt
hp_slow_query_2.txt
hp_slow_query_3.txt
hp_slow_query_4.txt
hp_slow_query_5.txt

Sequential Query Execution on Home Page

Problem: On the /home view, queries are executed one after the other serially. The home page is not returned and rendered until all queries finish processing. The five queries listed take about 85-90% of the total time. Even with indexes, they still take considerable time due to the large number of records.

Use case/motivation

The current implementation of the home page results in significant delays in rendering due to the sequential execution of multiple queries. This affects the user experience as users have to wait for all queries to finish processing before the page is displayed. By implementing parallel query execution and lazy loading, we can achieve the following:

1. Faster Page Load Times: The home page can begin rendering as soon as the first query results are available, rather than waiting for all queries to complete.
2. Improved User Experience: Users will experience a more responsive and dynamic interface, as parts of the page will load incrementally.

Related issues

No response

Are you willing to submit a PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[pankajkoti]

cc: @bbovenzi for your attention and thoughts on this one.

[tirkarthi]

Related

#38776
#36504

[bbovenzi]

We already need to rewrite the whole page soon. We can redo the queries, caching, and loading order then.