Wow Rhymes, thanks for such an excellent answer!!! The query plans really help to see how they are performing. Interesting that FILTER allows you to use aggregates much faster than a subquery. Definitely have to read a book or two about SQL now!
In case you're wondering, getting 119 rows took about 4 minutes to run the GROUP BY query. We're not looking to put in this app by any means, so the time is totally fine. Definitely helps me think about how to structure AnalyticsService.rb queries though.
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.
Wow Rhymes, thanks for such an excellent answer!!! The query plans really help to see how they are performing. Interesting that
FILTERallows you to use aggregates much faster than a subquery. Definitely have to read a book or two about SQL now!In case you're wondering, getting 119 rows took about 4 minutes to run the
GROUP BYquery. We're not looking to put in this app by any means, so the time is totally fine. Definitely helps me think about how to structureAnalyticsService.rbqueries though.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