Topic list loading is getting slow.

[ilmartyrk]

Topic list loading is getting slow.

[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