Cohorts page hangs

[jamesefhawkins]

Describe the bug
Cohorts won't load on app.

To Reproduce
Steps to reproduce the behavior:

1. Go to cohorts
2. Try to view a cohort
3. It hangs

Expected behavior
It should load.

Screenshots
Attached.

[timgl]

The basic problem with the Cohort query (and the Funnel queries) is that for each person in the database, it does a query against the Events table. Those events queries are pretty quick (and usually indexed correctly) but we end up doing 40k of them for our own instance for example.

There's got to be a better way to do this, I just don't know how.

[mariusandra]

When you say "does a query against the events table", are you referring to the subqueries I see for example in the funnels code? Or is there a legit python loop that makes these queries? I remember seeing one a long time ago, but I couldn't find it now.

There is something called the postgres LATERAL JOIN that should speed up the funnels a bit more than the subqueries.

For cohorts, I need to think a bit more about this. I seem to have run out of brainpower for today :).

[timgl]

yep I'm talking about the subqueries. For funnels and cohorts we really only do one query, but they take 10-30 seconds (and probably longer for the cohorts ones).

I hadn't heard of LATERAL JOIN but that looks like it's exactly what we're looking for. There's even an example of someone doing a funnel.

Annoyingly I think Django doesn't support this in the ORM but I think that's worth the tradeoff

[timgl]

@jamesefhawkins I think I actually fixed this with my previous attempt, if I go on app now cohorts just work... would you mind checking again?

There was an issue with duplicate people within cohorts, so have created a pr for that #552.