Re: rows estimate in explain analyze for the BRIN index

> On 30 Dec 2015, at 17:02, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Oleksii Kliukin <alexk(at)hintbits(dot)com> writes:
>> Bitmap Heap Scan on example (cost=744.44..757.64 rows=6 width=0) (actual time=73.895..73.895 rows=0 loops=1)
>> Output: 1
>> Recheck Cond: (example.event_time = (now() - '5 mons'::interval))
>> Rows Removed by Index Recheck: 4030
>> Heap Blocks: lossy=128
>> Buffers: shared hit=629
>> -> Bitmap Index Scan on example_event_time_idx1 (cost=0.00..744.41 rows=6 width=0) (actual time=70.335..70.335 rows=1280 loops=1)
>> Index Cond: (example.event_time = (now() - '5 mons'::interval))
>> Buffers: shared hit=501
>
>> - how does it get 1280 rows from the BRIN index scan, given that BRIN only stores pointers to the heap blocks, not individual rows. Does it calculate the number of rows in the blocks returned?
>
> It evidently returned 128 block IDs to the heapscan logic. I have not
> looked at the code, but a reasonable bet is that it's just guessing that
> there are 10 rows per block.

You are right, this is at the end of bringetbitmap in brin.c
/*
* XXX We have an approximation of the number of *pages* that our scan
* returns, but we don't have a precise idea of the number of heap tuples
* involved.
*/
PG_RETURN_INT64(totalpages * 10);

>
> That seems like an awfully low number, though; it equates to assuming
> that rows are 800 bytes wide on average. If we're going to use a fixed
> number, 100 rows per block would probably be more nearly the correct
> order of magnitude.
>
> Another idea would be to use the heap's row density as calculated
> by the last ANALYZE (ie, reltuples/relpages), with a fallback to 100
> if relpages=0. This'd only be convenient if the bitmap scan node has
> the parent heap rel open, which it might not.

+1

Kind regards,
--
Oleksii