Topic list loading is getting slow.

[ilmartyrk]

Our public topics loading time is getting slower, probably needs database query optimization. Home page is still in more or less tolerable time of 2-3 seconds, but subcategories are getting near 10s loading times

• [x] Create query plan to see what part of query is slow
• [x] Update query to fix issue, or add functionality to include/exclude parts of data, to make loading faster
• [x] Check FE if there are places to optimize for loading data and displaying UI faster

[ilmartyrk]

Used http://tatiyants.com/pev/ to visualize result for the query replaced the

LEFT JOIN (
                            SELECT t.id, MAX(a."updatedAt") as "lastActivity"
                            FROM "Topics" t JOIN "Activities" a ON ARRAY[t.id::text] <@ a."topicIds" GROUP BY t.id
                        ) ta ON (ta.id = t.id)

with LEFT JOIN "Activities" a ON ARRAY[t.id::text] <@ a."topicIds" The initial subquery run for all the topics and activities thus adding way more time and weight for the query.

Initial plan time:

1.47
execution time (s)
6.1
planning time (ms)
1.38
slowest node (s)
31,346
largest node (rows)
41,243.52
costliest node

Optimized:

176.72
execution time (ms)
3.69
planning time (ms)
98
slowest node (ms)
17,995
largest node (rows)
12,161.34
costliest node