Re: BUG #14253: b-tree no index range scan?

On Sat, Jul 16, 2016 at 8:03 PM,  <digoal@126.com> wrote:
> The following bug has been logged on the website:
>
> Bug reference:      14253
> Logged by:          Zhou Digoal
> Email address:      digoal@126.com
> PostgreSQL version: 9.5.3
> Operating system:   CentOS 6.x x64
> Description:
>
> HI,
>   when i use b-tree scan many tuples(spread index leaf page), there has big
> index page scans, larger than the index's real size.  why?
> is the explain's bug? or PostgreSQL no index range scan?

...

> postgres=# explain (analyze,verbose,timing,costs,buffers) select id from tbl
> offset 1000000 limit 10;
>                                                                      QUERY
> PLAN
>
-----------------------------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=25968.49..25968.75 rows=10 width=4) (actual
> time=528.914..528.921 rows=10 loops=1)
>    Output: id
>    Buffers: shared hit=750554
>    ->  Index Only Scan using tbl_pkey on public.tbl  (cost=0.56..1021687.32
> rows=39344184 width=4) (actual time=0.030..347.409 rows=1000010 loops=1)
>          Output: id
>          Heap Fetches: 0
>          Buffers: shared hit=750554

For index-only scan, every time two consecutive index tuples point to
a different page in the visibility map, it counts as a buffer read.
That is because the scan maintains a pin on the last used vm page, and
if the next needed one is different it drops the pin on the old page
and takes a pin one on the needed page.

Your table has 4 pages in the vm (on 9.5) and your table heap is
uncorrelated with the index, so there is a 25% chance of each
consecutive pair of index tuples pointing the same vm page and a 75%
of them pointing to different pages.  This very closely fits your
observed data.

So, not a bug.

Cheers,

Jeff