Re: BRIN index which is much faster never chosen by planner - Mailing list pgsql-hackers

From Jeremy Finzel
Subject Re: BRIN index which is much faster never chosen by planner
Date
Msg-id CAMa1XUgXnUyyCR80H62x0sX2mvQ3mGQebQU3tYNiVb3HneNDXg@mail.gmail.com
Whole thread Raw
In response to Re: BRIN index which is much faster never chosen by planner  (Tomas Vondra <tomas.vondra@2ndquadrant.com>)
Responses Re: BRIN index which is much faster never chosen by planner
List pgsql-hackers
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 

pgsql-hackers by date:

Previous
From: Konstantin Knizhnik
Date:
Subject: Columns correlation and adaptive query optimization
Next
From: David Rowley
Date:
Subject: Re: BRIN index which is much faster never chosen by planner