Обсуждение: BUG #14253: b-tree no index range scan?
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDI1MwpMb2dnZWQgYnk6ICAg ICAgICAgIFpob3UgRGlnb2FsCkVtYWlsIGFkZHJlc3M6ICAgICAgZGlnb2Fs QDEyNi5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjUuMwpPcGVyYXRpbmcg c3lzdGVtOiAgIENlbnRPUyA2LnggeDY0CkRlc2NyaXB0aW9uOiAgICAgICAg CgpISSwNCiAgd2hlbiBpIHVzZSBiLXRyZWUgc2NhbiBtYW55IHR1cGxlcyhz cHJlYWQgaW5kZXggbGVhZiBwYWdlKSwgdGhlcmUgaGFzIGJpZwppbmRleCBw YWdlIHNjYW5zLCBsYXJnZXIgdGhhbiB0aGUgaW5kZXgncyByZWFsIHNpemUu ICB3aHk/DQppcyB0aGUgZXhwbGFpbidzIGJ1Zz8gb3IgUG9zdGdyZVNRTCBu byBpbmRleCByYW5nZSBzY2FuPyAgDQoNCg0KcG9zdGdyZXM9IyBjcmVhdGUg dW5sb2dnZWQgdGFibGUgdGJsKGlkIGludCBwcmltYXJ5IGtleSwgaW5mbyB0 ZXh0LCBjcnRfdGltZQp0aW1lc3RhbXApOw0KcG9zdGdyZXM9IyBpbnNlcnQg aW50byB0Ymwgc2VsZWN0IHRydW5jKHJhbmRvbSgpKjEwMDAwMDAwMCksJ3Rl c3QnLG5vdygpCmZyb20gZ2VuZXJhdGVfc2VyaWVzKDEsNTAwMDAwMDApIG9u IGNvbmZsaWN0IG9uIGNvbnN0cmFpbnQgdGJsX3BrZXkgZG8Kbm90aGluZzsN CnBvc3RncmVzPSMgdmFjdXVtIGFuYWx5emUgdGJsOw0K57Si5byV5ZKMaGVh cOeahHBhZ2Vz5Y2g55SoDQoNCnBvc3RncmVzPSMgc2VsZWN0IHJlbHBhZ2Vz IGZyb20gcGdfY2xhc3Mgd2hlcmUgcmVsbmFtZT0ndGJsJzsNCiByZWxwYWdl cyANCi0tLS0tLS0tLS0NCiAgIDI1MDYwMA0KKDEgcm93KQ0KDQpwb3N0Z3Jl cz0jIHNlbGVjdCByZWxwYWdlcyBmcm9tIHBnX2NsYXNzIHdoZXJlIHJlbG5h bWU9J3RibF9wa2V5JzsNCiByZWxwYWdlcyANCi0tLS0tLS0tLS0NCiAgIDEw Nzg4MQ0KKDEgcm93KQ0KDQoNCmkgc2VlIHBvc3RncmVzcWwgaGFzIGJpLWxp bmsgYmV0d2VlbiBpbmRleCBwYWdlcywgd2h5IG5vdCB1c2UgaXQgcmVkdWNl IHNjYW4KaW5kZXggcGFnZXM/ICANCnNyYy9pbmNsdWRlL2FjY2Vzcy9uYnRy ZWUuaA0KDQp0eXBlZGVmIHN0cnVjdCBCVFBhZ2VPcGFxdWVEYXRhDQp7DQog ICAgICAgIEJsb2NrTnVtYmVyIGJ0cG9fcHJldjsgICAgICAgICAgLyogbGVm dCBzaWJsaW5nLCBvciBQX05PTkUgaWYKbGVmdG1vc3QgKi8NCiAgICAgICAg QmxvY2tOdW1iZXIgYnRwb19uZXh0OyAgICAgICAgICAvKiByaWdodCBzaWJs aW5nLCBvciBQX05PTkUgaWYKcmlnaHRtb3N0ICovDQogICAgICAgIHVuaW9u DQogICAgICAgIHsNCiAgICAgICAgICAgICAgICB1aW50MzIgICAgICAgICAg bGV2ZWw7ICAgICAgICAgIC8qIHRyZWUgbGV2ZWwgLS0tIHplcm8gZm9yCmxl YWYgcGFnZXMgKi8NCiAgICAgICAgICAgICAgICBUcmFuc2FjdGlvbklkIHhh Y3Q7ICAgICAgICAgICAgIC8qIG5leHQgdHJhbnNhY3Rpb24gSUQsIGlmCmRl bGV0ZWQgKi8NCiAgICAgICAgfSAgICAgICAgICAgICAgICAgICAgICAgYnRw bzsNCiAgICAgICAgdWludDE2ICAgICAgICAgIGJ0cG9fZmxhZ3M7ICAgICAg ICAgICAgIC8qIGZsYWcgYml0cywgc2VlIGJlbG93ICovDQogICAgICAgIEJU Q3ljbGVJZCAgICAgICBidHBvX2N5Y2xlaWQ7ICAgLyogdmFjdXVtIGN5Y2xl IElEIG9mIGxhdGVzdCBzcGxpdAoqLw0KfSBCVFBhZ2VPcGFxdWVEYXRhOw0K DQoNCnBvc3RncmVzPSMgZXhwbGFpbiAoYW5hbHl6ZSx2ZXJib3NlLHRpbWlu Zyxjb3N0cyxidWZmZXJzKSBzZWxlY3QgaWQgZnJvbSB0YmwKb2Zmc2V0IDEw MDAwMDAgbGltaXQgMTA7DQogICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBRVUVS WQpQTEFOICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIA0KLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0NCiBMaW1pdCAgKGNvc3Q9MjU5NjguNDkuLjI1OTY4Ljc1IHJvd3M9MTAg d2lkdGg9NCkgKGFjdHVhbAp0aW1lPTUyOC45MTQuLjUyOC45MjEgcm93cz0x MCBsb29wcz0xKQ0KICAgT3V0cHV0OiBpZA0KICAgQnVmZmVyczogc2hhcmVk IGhpdD03NTA1NTQNCiAgIC0+ICBJbmRleCBPbmx5IFNjYW4gdXNpbmcgdGJs X3BrZXkgb24gcHVibGljLnRibCAgKGNvc3Q9MC41Ni4uMTAyMTY4Ny4zMgpy b3dzPTM5MzQ0MTg0IHdpZHRoPTQpIChhY3R1YWwgdGltZT0wLjAzMC4uMzQ3 LjQwOSByb3dzPTEwMDAwMTAgbG9vcHM9MSkNCiAgICAgICAgIE91dHB1dDog aWQNCiAgICAgICAgIEhlYXAgRmV0Y2hlczogMA0KICAgICAgICAgQnVmZmVy czogc2hhcmVkIGhpdD03NTA1NTQgICANCiBQbGFubmluZyB0aW1lOiAwLjA4 MyBtcw0KIEV4ZWN1dGlvbiB0aW1lOiA1MjguOTQ4IG1zDQooOSByb3dzKQ0K DQoNCnBvc3RncmVzPSMgZXhwbGFpbiAoYW5hbHl6ZSx2ZXJib3NlLHRpbWlu Zyxjb3N0cyxidWZmZXJzKSBzZWxlY3QgaWQgZnJvbSB0YmwKbGltaXQgMTAw MDAxMDsNCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIFFVRVJZClBMQU4gICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgDQotLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KIExpbWl0 ICAoY29zdD0wLjU2Li4yNTk2OC43NSByb3dzPTEwMDAwMTAgd2lkdGg9NCkg KGFjdHVhbAp0aW1lPTAuMDMyLi43MzYuOTI5IHJvd3M9MTAwMDAxMCBsb29w cz0xKQ0KICAgT3V0cHV0OiBpZA0KICAgQnVmZmVyczogc2hhcmVkIGhpdD03 NTA1NTQNCiAgIC0+ICBJbmRleCBPbmx5IFNjYW4gdXNpbmcgdGJsX3BrZXkg b24gcHVibGljLnRibCAgKGNvc3Q9MC41Ni4uMTAyMTY4Ny4zMgpyb3dzPTM5 MzQ0MTg0IHdpZHRoPTQpIChhY3R1YWwgdGltZT0wLjAzMS4uMzYyLjc5MSBy b3dzPTEwMDAwMTAgbG9vcHM9MSkNCiAgICAgICAgIE91dHB1dDogaWQNCiAg ICAgICAgIEhlYXAgRmV0Y2hlczogMA0KICAgICAgICAgQnVmZmVyczogc2hh cmVkIGhpdD03NTA1NTQgIA0KIFBsYW5uaW5nIHRpbWU6IDAuMDk3IG1zDQog RXhlY3V0aW9uIHRpbWU6IDkxNi4yNTYgbXMNCig5IHJvd3MpDQoNCnRoYW5r cywNCmJlc3QgcmVnYXJkcywNCmRpZ29hbA0KCgo=
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