Interesting that FILTER allows you to use aggregates much faster than a subquery.
I think it's because the DB is able to do it with less work by using multiple conditions on the same set of data (the joined table on the right of the join, reactions in this example), whereas with a subquery, it has to execute a different subquery for each row in the articles table. Not much to optimize there.
In case you're wondering, getting 119 rows took about 4 minutes to run the GROUP BY query.
Mmm not great though :D how long was it taking with the subqueries? Anyhow, it's something. I have a feeling it can be further optimized by checking if and which indexes are being used and the other idea I had (untested and unexplored) was to use window functions but I haven't tried, nor I know if it can be done or if it's faster :D
We're not looking to put in this app by any means, so the time is totally fine
Good :D
Definitely helps me think about how to structure AnalyticsService.rb queries though
Let me know if you find a different approach for that. After all you have the great advantage of having a working version that you can use as a comparison. You extract a set of data and then play with grouping until you get the same result. :D
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
Glad it helped!
I think it's because the DB is able to do it with less work by using multiple conditions on the same set of data (the joined table on the right of the join,
reactionsin this example), whereas with a subquery, it has to execute a different subquery for each row in thearticlestable. Not much to optimize there.Mmm not great though :D how long was it taking with the subqueries? Anyhow, it's something. I have a feeling it can be further optimized by checking if and which indexes are being used and the other idea I had (untested and unexplored) was to use
window functionsbut I haven't tried, nor I know if it can be done or if it's faster :DGood :D
Let me know if you find a different approach for that. After all you have the great advantage of having a working version that you can use as a comparison. You extract a set of data and then play with grouping until you get the same result. :D