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

On Sat, Jul 16, 2016 at 8:03 PM, <digoal(at)126(dot)com> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 14253
> Logged by: Zhou Digoal
> Email address: digoal(at)126(dot)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