Re: Show lossy heap block info in EXPLAIN ANALYZE for bitmap heap scan

On 25 November 2013 13:37, Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>wrote:

>
> Reconsidering that, I wish to know your opinion. The patch shows the
> number of exact/lossy pages that has been fetched in a bitmap heap scan.
> But the number varies with the fraction of tuples to be retrieved like the
> following.
>
> postgres=# EXPLAIN ANALYZE SELECT * FROM demo WHERE col2 between 0.01 and
> 0.02;
> QUERY PLAN
>
> ------------------------------------------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on demo (cost=2187.35..101419.96 rows=102919 width=42)
> (actual time=23.684..1302.382 rows=99803 loops=1)
> Recheck Cond: ((col2 >= 0.01::double precision) AND (col2 <=
> 0.02::double precision))
> Rows Removed by Index Recheck: 6279502
> Heap Blocks: exact=1990 lossy=59593
> -> Bitmap Index Scan on demo_col2_idx (cost=0.00..2161.62 rows=102919
> width=0) (actual time=23.330..23.330 rows=99803 loops=1)
> Index Cond: ((col2 >= 0.01::double precision) AND (col2 <=
> 0.02::double precision))
> Total runtime: 1311.949 ms
> (7 rows)
>
> postgres=# EXPLAIN ANALYZE SELECT * FROM demo WHERE col2 between 0.01 and
> 0.02 LIMIT 5000;
> QUERY PLAN
>
> ------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=2187.35..7008.26 rows=5000 width=42) (actual
> time=23.543..86.093 rows=5000 loops=1)
> -> Bitmap Heap Scan on demo (cost=2187.35..101419.96 rows=102919
> width=42) (actual time=23.542..85.196 rows=5000 loops=1)
> Recheck Cond: ((col2 >= 0.01::double precision) AND (col2 <=
> 0.02::double precision))
> Rows Removed by Index Recheck: 312179
> Heap Blocks: exact=99 lossy=2963
> -> Bitmap Index Scan on demo_col2_idx (cost=0.00..2161.62
> rows=102919 width=0) (actual time=23.189..23.189 rows=99803 loops=1)
> Index Cond: ((col2 >= 0.01::double precision) AND (col2 <=
> 0.02::double precision))
> Total runtime: 86.626 ms
> (8 rows)
>
> So, my question is, we should show the number of exact/lossy pages in a
> TIDBitmap, not the number of these pages that has been fetched in the
> bitmap heap scan?
>

Yes, I agree that rather than looking at the bitmap heap scan to track the
number of pages, we should look somewhere in the underlying index scan.
Yes, we should get a constant number of index pages regardless of the
actual parent table rows. I can see that btgetbitmap() adds all the tuples
into the bitmap, so somewhere below under btgetbitmap() might be the right
place to track. Somewhere in tbm_create_pagetable(), but not sure.

> Thanks,
>
> Best regards,
> Etsuro Fujita
>
>