From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | 德哥 <digoal(at)126(dot)com> |
Cc: | Pg Bugs <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #14253: b-tree no index range scan? |
Date: | 2016-07-18 17:46:41 |
Message-ID: | CAMkU=1ybR3xAj7Xc4RS1SVKW7qeDHBh9egSOV4sz-B8bnf9A=w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2016-07-18 17:54:29 | Re: BUG #14255: Please provide 9.1.9 installation steps |
Previous Message | nagalla.b4u | 2016-07-18 17:31:10 | BUG #14257: steps for upgrade 9.1.0 to 9.1.9 |