Louis Davidson does some testing:
A few weeks ago, there was a LinkedIn post (I can’t find it anymore) that covered something about how indexes were used by
COUNTin SQL. I think it may have been based on SQL Server, but I am not sure (it is rare that one of the SQL posts on LinkedIn mentions a platform). At the time, I went and tried a few of the mentioned cases and realized this was an interesting question: how does the COUNT aggregate use indexes when you use various different expressions.
Louis has a series of test cases and I got most of them right, though I wasn’t sure about one particular optimization.