Re: BRIN index which is much faster never chosen by planner

>
> 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!!!

Thanks,
Jeremy