Let me check the plans for those queries in Meta.
Queries are quite fast on Meta, but I can see how they can perform badly on under powered databases. We are using ActiveRecord and the code is quite clean, but the generated SQL is quite messy.
Doing the SQL manually we can make the query use existing indexes and optimize it a lot:
Before
After
In the optimized query we only retrieve posts from the user, limiting the loop of posts for the join happening too late.
So I believe we can get better performance without adding index overhead.