activity feed performance debugging thoughts

per https://app.datadoghq.com/apm/traces it looks like the activyt feed is among the slowest pages on the site

this is a big deal because it locks up the app servers, but it is EXISTENTIAL to us in that it consumes 4s of DB/IO resources at the DB at a time x the queries are ALL over the site.

here are some of the queries i see in datadog

```
SELECT ( ? ) FROM dashboard_activity LEFT OUTER JOIN dashboard_bounty ON ( dashboard_activity . bounty_id = dashboard_bounty . id ) WHERE ( ( dashboard_activity . bounty_id IS ? OR dashboard_bounty . network = ? ) AND dashboard_activity . hidden = ? AND dashboard_activity . activity_type IN ( ? ) AND ( dashboard_activity . hackathonevent_id = ? OR dashboard_bounty . event_id = ? ) AND dashboard_activity . id > ? ) LIMIT ?
```

```
SELECT dashboard_activity . id, dashboard_activity . created_on, dashboard_activity . modified_on, dashboard_activity . profile_id, dashboard_activity . bounty_id, dashboard_activity . tip_id, dashboard_activity . kudos_transfer_id, dashboard_activity . kudos_id, dashboard_activity . grant_id, dashboard_activity . subscription_id, dashboard_activity . hackathonevent_id, dashboard_activity . project_id, dashboard_activity . created, dashboard_activity . activity_type, dashboard_activity . metadata, dashboard_activity . needs_review, dashboard_activity . view_count, dashboard_activity . other_profile_id, dashboard_activity . hidden, dashboard_activity . cached_view_props FROM dashboard_activity LEFT OUTER JOIN dashboard_bounty ON ( dashboard_activity . bounty_id = dashboard_bounty . id ) INNER JOIN dashboard_profile ON ( dashboard_activity . profile_id = dashboard_profile . id ) LEFT OUTER JOIN dashboard_profile T4 ON ( dashboard_activity . other_profile_id = T4. id ) WHERE ( ( dashboard_activity . bounty_id IS ? OR dashboard_bounty . network = ? ) AND dashboard_activity . hidden = ? AND ( UPPER ( dashboard_activity . metadata ::text ) LIKE UPPER ( ? ) OR dashboard_profile . handle = ? OR T4. handle = ? ) ) ORDER BY dashboard_activity . created_on DESC LIMIT ?

```

```
SELECT ( ? ) FROM dashboard_activity LEFT OUTER JOIN dashboard_bounty ON ( dashboard_activity . bounty_id = dashboard_bounty . id ) WHERE ( ( dashboard_activity . bounty_id IS ? OR dashboard_bounty . network = ? ) AND dashboard_activity . hidden = ? AND dashboard_activity . activity_type IN ( ? ) AND ( dashboard_activity . hackathonevent_id = ? OR dashboard_bounty . event_id = ? ) AND dashboard_activity . id > ? ) LIMIT ?

```

```
SELECT dashboard_activity . id, dashboard_activity . created_on, dashboard_activity . modified_on, dashboard_activity . profile_id, dashboard_activity . bounty_id, dashboard_activity . tip_id, dashboard_activity . kudos_transfer_id, dashboard_activity . kudos_id, dashboard_activity . grant_id, dashboard_activity . subscription_id, dashboard_activity . hackathonevent_id, dashboard_activity . project_id, dashboard_activity . created, dashboard_activity . activity_type, dashboard_activity . metadata, dashboard_activity . needs_review, dashboard_activity . view_count, dashboard_activity . other_profile_id, dashboard_activity . hidden, dashboard_activity . cached_view_props FROM dashboard_activity LEFT OUTER JOIN dashboard_bounty ON ( dashboard_activity . bounty_id = dashboard_bounty . id ) WHERE ( ( dashboard_activity . bounty_id IS ? OR dashboard_bounty . network = ? ) AND dashboard_activity . hidden = ? AND dashboard_activity . activity_type IN ( ? ) AND ( dashboard_activity . hackathonevent_id = ? OR dashboard_bounty . event_id = ? ) AND dashboard_activity . id > ? ) ORDER BY dashboard_activity . created_on DESC LIMIT ?
```

as you can see here; the querise are somewhat long and complex - that usually isnt an issue if the tables are indexd, buit in this case we have an activity table that has 2810850 rows in it being joined to other tables with 100k + rows in them, it probably creates a lot of IO.

this is the code that generates these issues https://github.com/gitcoinco/web/blob/master/app/retail/views.py#L742

normally i go into the codebase and just refactor the queries to reduce JOINS + add indexes whereever needed, or sometimes add an index upon write to the system (tactics described in https://github.com/gitcoinco/web/blob/master/docs/performance.md ).. but this time, due to the size of the tables + their comiplexity + the fact that the activity table is used in many areas of the site i think we may need another strategy.

right now im thinking about adding a new table ActivityIndex which would look like this
```
key = models.CharField(max_length=255, db_index=True)
activity = models.ForeignKey(
'dashboard.Activity', null=True, on_delete=models.SET_NULL, related_name='activities_index', blank=True
)

```

this would allow us to refactor complicated JOINs like ` ( ( dashboard_activity . bounty_id IS ? OR dashboard_bounty . network = ? ) AND dashboard_activity . hidden = ? AND dashboard_activity . activity_type IN ( ? ) AND ( dashboard_activity . hackathonevent_id = ? OR dashboard_bounty . event_id = ? ) AND dashboard_activity . id > ? ) ` and just put them into one `key` field.

in this query lookup paradigm, instead of doing 3-4 JOINS to get all of the information needed to find whether an activity fed item should show on a page, we just simply look at the `key` field. then upon save, what we do is we evaluate which feeds (which `key`s a feed item needs to be in, and write an `ActivityIndex` when we first save it. perhaps if we really want to future proof it we set a retention policy that only items from the last 6 months even need to be indexed :)

looking forward to discussing tomorrow