Hi!
The reason it may not be using the index is the query planner may have determined that the size of your data set is such that it is faster to perform a sequential scan than an index scan. It looks like in your data set there are only 813 rows, and if you look at the execution time, it's < 1ms, so the sequential scan is doing pretty well in this case ;-)
If you try running it with several tens of thousands of roles, I would bet it would perform an index scan.
Nice blog post!
Comment
See my stackoverflow question: https://stackoverflow.com/questions/48233665/can-aggregate-filter-expressions-not-use-indices
In that table (which has 4.6M rows) and trying to select a subset of about 10K rows, the total running time is repeatedly about 1.5s whereas the more traditional query takes 0.005s.
Parent comment
Hi! The reason it may not be using the index is the query planner may have determined that the size of your data set is such that it is faster to perform a sequential scan than an index scan. It looks like in your data set there are only 813 rows, and if you look at the execution time, it's < 1ms, so the sequential scan is doing pretty well in this case ;-) If you try running it with several tens of thousands of roles, I would bet it would perform an index scan. Nice blog post!