Re: rows estimate in explain analyze for the BRIN index

> On 30 Dec 2015, at 21:12, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Emre Hasegeli <emre(at)hasegeli(dot)com> writes:
>>> I don’t see how to solve this problem without changing explain analyze output to accommodate for “unknown” value. I don’t think “0” is a non-confusing representation of “unknown” for most people, and from the practical standpoint, a “best effort” estimate is better than 0 (i.e. I will be able to estimate how efficient BRIN index is for my tables in terms of the number of tuples retrieved/thrown away)
>
> We do already have a nearby precedent for returning zero when we don't
> have an accurate answer: that's what BitmapAnd and BitmapOr plan nodes
> do. (This is documented btw, at the bottom of section 14.1.)

+1 for following a precedent.

>
>> The number of retrieved and thrown away rows are already available on
>> the upper part of the plan. Bitmap Index Scan should provide the rows
>> that matched the index.
>
> It doesn't have that information.
>
>> Another alternative would be just returning
>> the number of matching pages (by not multiplying with 10). It might
>> be better understood.
>
> No, it would not, at least not unless we found a way to explicitly mark
> the output as being blocks not rows (which would doubtless break a lot of
> existing client-side code). Zero is fairly clearly an impossible value,
> whereas anything that's not zero is going to be taken at face value by
> many users.

But is it? Is it impossible for the BRIN bitmap index scan to return 0 rows (say, if the value being matched is outside the min/max boundary for every block range?) Granted, if we document that it always returns 0 and should be ignored, then confusing the actual 0 with the 0 as a representation of “unknown” would be less a problem.

--
Oleksii