The other issue is that the estimation of pages fetched using bitmap heap scan is rather crude - but that's simply hard, and I don't think we can fundamentally improve this.
I wanted to follow up on this specific issue. Isn't this the heart of the matter and a fundamental problem? If I want to rely on BRIN indexes as in a straightforward case as explained in OP, but I don't know if the planner will be nearly reliable enough, how can I depend on them in production? Is this not considered a planner bug or should this kind of case be documented as problematic for BRIN? As another way to look at it: is there a configuration parameter that could be added specific to BRIN or bitmapscan to provide help to cases like this?
On freshly analyzed tables, I tried my original query again and found that even with now() - 3 days it does not choose the BRIN index. In fact it chose another btree on the table like (id1, id2, rec_insert_time). With warm cache, the pg-chosen plan takes 40 seconds to execute, whereas when I force a BRIN scan it takes only 4 seconds.
I could understand more if the execution times were close, but the actual BRIN index is orders of magnitude faster than the plan Postgres is choosing. I appreciate the feedback on this very much, as I am quite eager to use BRIN indexes!!!
