I deliberately included r in the index, to demonstrate the issue that I’m seeing. I know that there is very little locality in this particular, dummy, arbitrary test case. I can try to produce a test case that has some locality, but I expect it to show exactly the same results, i.e. that the BRIN index performs much worse when we try to query on this column as well.
I'm suggesting that adding the second column to the index is the source of your problem, not adding the column to the query.
How does it perform with just the id column in the index?
