Re: rows estimate in explain analyze for the BRIN index

On 30 Dec 2015, at 17:02, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Oleksii Kliukin <alexk@hintbits.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.

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.