API: GET /api/activities - DB out of memory errors on high load

[citizenos]

API: GET /api/activities - DB out of memory errors on high load

[ilmartyrk]

Read a lot of Postgres documentation about functions, indexing:

• https://www.postgresql.org/docs/9.1/sql-createindex.html
• https://www.postgresql.org/docs/9.1/sql-createfunction.html
• https://www.postgresql.org/docs/9.2/queries-table-expressions.html
• https://www.w3schools.com/sql/sql_distinct.asp

also looked around for articles. Probably the simplest to understand and try out was this:

• https://statsbot.co/blog/postgresql-query-optimization/
  Took apart all the main queries and run them separately to find the slowest and found out that they were quite ok. So after that rewrote query that built the returning data into a function and that helped to bring down the cost, but performance got really slow. Finally concentrated on query order. So to first narrow down ID-s to return and after that get all data that is only related to these.
  To get better picture from queries used:
• http://tatiyants.com/pev/#/plans
  Final results without adding any new indexes or altering DB structure (Run on local DB with about 30000 rows in Activities table, created by re-running all the tests over and over):
  Query cost from: 30821841.70 -> 207202.78
  Execution time 2247.545 -> 607.797 ms
  Memory usage under load test described in this issue ~180MB -> ~40MB
  Execution times under load test:
• min 3815ms -> 832ms
• median 8062ms ->2281ms
• max 12332ms ->3859ms