Revert friends-of-friends follow recommendation query to using a CTE

[ClearlyClaire]

Also fixes ordering back to the initial intent.

Note that I have not verified whether this had better performances than the previous version. I also used subqueries instead of the usual approach of using Account.not_excluded_by_account and Account.not_domain_blocked_by_account because:

1. we never actually verified if that approach had better performance
2. it's a lot more painful to use without ActiveRecord helpers

[mjankowski]

On the ordering change here -- was this an oversight we missed in review? Or was the intention to change the ordering, but due to the perf issues we're going back to original ordering?

[ClearlyClaire]

The change of ordering in my earlier PR was an oversight of mind, I misunderstood the original ordering, and there were no tests to catch the change. I just noticed the ordering had been changed by starting from the old CTE.

[mjankowski]

Cool - given that, I agree that the spec changes here do recapture what the original pre-refactor ordering looked like. I'll defer to you all on the query change / perf implications / etc.

• Do we want to fix specs and the refactor version (swap the order) first here? just so we have a baseline of a logically correct change? (happy to do this as quick separate PR if you'd like)
• Other than playing around with local data setup and reviewing some explain/analyze combinations ... is there any way we can benchmark this better?

[renchap]

We tested the queries on mastodon.social and perf is similar to the previous code

[ClearlyClaire]

We tested the queries on mastodon.social and perf is similar to the previous code

To add to that, we tested multiple versions of the query for multiple sets of active mastodon.social users (random 400 users, 5 with very few follows, 5 with large amounts of follows and blocks) and the results were always pretty consistent: the code in this PR is roughly on par with the old query that was missing filtering, while the code before this PR was way slower (10 to 50 times slower). The code in main...mjankowski:mastodon:fix-order-in-floof-foof-fooferoni-changes was consistently ~1.5× slower than that of this PR, which could be due to some extra work instead of reusing the CTE for filtering.

• Other than playing around with local data setup and reviewing some explain/analyze combinations ... is there any way we can benchmark this better?

This is a good question. I guess understanding the query plan is the way to go, but even that is specific to what's actually in the database. Also, extracting the query is a bit of a pain depending on how the query is written.

I've been toying with populating fake data, but I think we'd need to take shortcuts in generation for it to be usable.

[mjankowski]

One thing I suspect may have been happening with the poorly performing query is that, for the setup of first_degree, when I was reviewing the changes and thinking about what was being built - I was thinking about that aspect specifically as first grabbing a big array of IDs (which, in itself could have been expensive) before the larger query was built, but what was actually happening is that query is getting included as a subquery in the larger query, which could certainly get expensive depending on the surrounding rows/joins/etc. That said, more than one thing changed at once, so it may be multiple causes.

Before I attempt this -- are we open to repeating the style/compositional improvement of these changes, if we can preserve the current more performant query? (putting aside like, framework sql-quoting style and whatnot). That current WIP branch (that you linked) restores the composed scope approach - but adds an additional join which is not present in this restored/performant version. I suspect that contributes to the ~1.5x perf drop on that branch.