If we look at the joins, we are already joining with
JOIN users u on u.id = :user_id.
there are potential performance impacts here, a bit concerned about fiddling with this, can you confirm query plan is identical for a user with lots of state?
I tested this locally and both query plans are the same.
Can you test on a few people with large amounts of read state on say meta?
I think that for inner joins, multiple FROM, JOIN … ON or JOIN … WHERE are the same. I think Postgres will rewrite this query into lists of FROM anywhere because of:
Closing this first as I think it is too much effort for minimal gains.