Обсуждение: BUG #9757: Why reclaim index deleted pages need twice vacuum

The following bug has been logged on the website:

Bug reference:      9757
Logged by:          digoal.zhou
Email address:      digoal@126.com
PostgreSQL version: 9.3.3
Operating system:   CentOS 6.4 x64
Description:

When I'm testing a index page recycling, found that the index page must be
two vacuum can be reused.
The reason is thatVacuum for the first time, the main fork freespace map
file will be generated, but do not generate the index of free space map
file.
THE DETAIL :
digoal=# create extension pageinspect;
CREATE EXTENSION
digoal=# create table test(id int primary key, info text, crt_time
timestamp);
CREATE TABLE
digoal=# insert into test select
generate_series(1,5000000),md5(random()::text),clock_timestamp();
INSERT 0 5000000
digoal=# delete from test where id<>5000000;
DELETE 4999999
-- the first vacuum, deleted index page can't reuse.
digoal=# vacuum verbose analyze test;
INFO:  vacuuming "public.test"
INFO:  scanned index "test_pkey" to remove 4999999 row versions
DETAIL:  CPU 0.00s/1.75u sec elapsed 1.77 sec.
INFO:  "test": removed 4999999 row versions in 11628 pages
DETAIL:  CPU 0.00s/0.19u sec elapsed 0.22 sec.
INFO:  index "test_pkey" now contains 1 row versions in 3404 pages
DETAIL:  4999999 index row versions were removed.
3398 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "test": found 4999999 removable, 1 nonremovable row versions in 11628
out of 11628 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.01s/2.83u sec elapsed 2.91 sec.
INFO:  vacuuming "pg_toast.pg_toast_16432"
INFO:  index "pg_toast_16432_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_16432": found 0 removable, 0 nonremovable row versions in 0
out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.test"
INFO:  "test": scanned 11628 of 11628 pages, containing 1 live rows and 0
dead rows; 1 rows in sample, 1 estimated total rows
VACUUM
-- and no index fsm page.
digoal=# select * from
fsm_page_contents(get_raw_page('test_pkey','fsm',0));
ERROR:  could not open file "base/16384/16471_fsm": No such file or
directory
digoal=# select * from
fsm_page_contents(get_raw_page('test_pkey','fsm',1));
ERROR:  could not open file "base/16384/16471_fsm": No such file or
directory
digoal=# select * from
fsm_page_contents(get_raw_page('test_pkey','fsm',2));
ERROR:  could not open file "base/16384/16471_fsm": No such file or
directory
-- but table fsm page generated.
digoal=# select * from fsm_page_contents(get_raw_page('test','fsm',0));
 fsm_page_contents
-------------------
 0: 244           +
 1: 244           +
 3: 244           +
 7: 244           +
.....so on
-- the second vacuum.
digoal=# vacuum verbose analyze test;
INFO:  vacuuming "public.test"
INFO:  index "test_pkey" now contains 1 row versions in 3404 pages
DETAIL:  0 index row versions were removed.
3400 index pages have been deleted, 3400 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "test": found 0 removable, 0 nonremovable row versions in 0 out of
11628 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_16432"
INFO:  index "pg_toast_16432_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_16432": found 0 removable, 0 nonremovable row versions in 0
out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.test"
INFO:  "test": scanned 11628 of 11628 pages, containing 1 live rows and 0
dead rows; 1 rows in sample, 1 estimated total rows
VACUUM
-- generate the index fsm pages. and this time , the deleted index page can
reuse.
digoal=# select * from
fsm_page_contents(get_raw_page('test_pkey','fsm',0));
 fsm_page_contents
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
255 + 0:
255 + 1:
255 + 3:
7:255 +

On 03/28/2014 10:15 AM, digoal@126.com wrote:
> The following bug has been logged on the website:
>
> Bug reference:      9757
> Logged by:          digoal.zhou
> Email address:      digoal@126.com
> PostgreSQL version: 9.3.3
> Operating system:   CentOS 6.4 x64
> Description:
>
> When I'm testing a index page recycling, found that the index page must be
> two vacuum can be reused.

Yep, that's how the b-tree works [1]. A deleted page cannot be
immediately reused, because there might be concurrent scans that are
just about to visit the page. So when a page is deleted, i.e unlinked
from the tree, it is stamped with the next transaction ID, and left in
place. The next vacuum checks that the transaction ID is no longer
visible to anyone, which ensures that there are no transactions running
that might've seen a reference to that page. Only after that the page
can be reused.

That's quite pessimal; in most cases the page could be reused much
earlier, because it is a very tight window for vacuum to delete a page
just when a concurrent scan has read a link to the page and is about to
follow it. But B-tree pages are usually not deleted that often that it
would matter in practice, so there has been no effort to optimize it.

In short, it's not a bug :-).

[1]
http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/nbtree/README#l283

- Heikki

VGhhbmtzIHZlcnkgbXVjaCwgYnV0IGluIHJhcmUgY29uZGl0aW9uLCBpdCdzIGEgc21hbGwgdHJv
dWJsZS4NCg0KDQoNCg0KLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tDQrlhaznm4rmmK/kuIDovojlrZDnmoTkuossIEknbSBEaWdvYWwg
LCBKdXN0IERvIGl077yBDQrlvrflk6UoRGlnb2FsLlpob3UpDQrmlbDmja7lupPmioDmnK/nu4/n
kIYNCioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqKioqDQrmna3l
t57mlq/lh6/nvZHnu5znp5HmioDmnInpmZDlhazlj7gNCuadreW3nuW4gue0q+iNhuiKsei3rzLl
j7fogZTlkIjlpKfljqZC5bqnMTHlsYINCumCrue8ljogICAzMTAwMTMNCuaJi+acujogICArODYg
MTM0ODQwMjE5NTMNCuW6p+acujogICArODYgNTcxIDg5NzEwOTQ4DQpRUTogICAgIDI3NjczMjQz
MQ0KZW1haWw6ICBkaWdvYWwuemhvdUBtb3BvLmNvbQ0KTVNOOiAgICB6enpxd2FyZUBob3RtYWls
LmNvbQ0KQmxvZzogaHR0cDovL2Jsb2cuMTYzLmNvbS9kaWdvYWxAMTI2Lw0KR2l0aHViOiBodHRw
czovL2dpdGh1Yi5jb20vZGlnb2FsDQoNCkZyb206IEhlaWtraSBMaW5uYWthbmdhcw0KRGF0ZTog
MjAxNC0wMy0zMSAxNDo1NA0KVG86IGRpZ29hbA0KQ0M6IHBnc3FsLWJ1Z3MNClN1YmplY3Q6IFJl
OiBbQlVHU10gQlVHICM5NzU3OiBXaHkgcmVjbGFpbSBpbmRleCBkZWxldGVkIHBhZ2VzIG5lZWQg
dHdpY2UgdmFjdXVtDQpPbiAwMy8yOC8yMDE0IDEwOjE1IEFNLCBkaWdvYWxAMTI2LmNvbSB3cm90
ZToNCj4gVGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJzaXRlOg0K
Pg0KPiBCdWcgcmVmZXJlbmNlOiAgICAgIDk3NTcNCj4gTG9nZ2VkIGJ5OiAgICAgICAgICBkaWdv
YWwuemhvdQ0KPiBFbWFpbCBhZGRyZXNzOiAgICAgIGRpZ29hbEAxMjYuY29tDQo+IFBvc3RncmVT
UUwgdmVyc2lvbjogOS4zLjMNCj4gT3BlcmF0aW5nIHN5c3RlbTogICBDZW50T1MgNi40IHg2NA0K
PiBEZXNjcmlwdGlvbjoNCj4NCj4gV2hlbiBJJ20gdGVzdGluZyBhIGluZGV4IHBhZ2UgcmVjeWNs
aW5nLCBmb3VuZCB0aGF0IHRoZSBpbmRleCBwYWdlIG11c3QgYmUNCj4gdHdvIHZhY3V1bSBjYW4g
YmUgcmV1c2VkLg0KDQpZZXAsIHRoYXQncyBob3cgdGhlIGItdHJlZSB3b3JrcyBbMV0uIEEgZGVs
ZXRlZCBwYWdlIGNhbm5vdCBiZSANCmltbWVkaWF0ZWx5IHJldXNlZCwgYmVjYXVzZSB0aGVyZSBt
aWdodCBiZSBjb25jdXJyZW50IHNjYW5zIHRoYXQgYXJlIA0KanVzdCBhYm91dCB0byB2aXNpdCB0
aGUgcGFnZS4gU28gd2hlbiBhIHBhZ2UgaXMgZGVsZXRlZCwgaS5lIHVubGlua2VkIA0KZnJvbSB0
aGUgdHJlZSwgaXQgaXMgc3RhbXBlZCB3aXRoIHRoZSBuZXh0IHRyYW5zYWN0aW9uIElELCBhbmQg
bGVmdCBpbiANCnBsYWNlLiBUaGUgbmV4dCB2YWN1dW0gY2hlY2tzIHRoYXQgdGhlIHRyYW5zYWN0
aW9uIElEIGlzIG5vIGxvbmdlciANCnZpc2libGUgdG8gYW55b25lLCB3aGljaCBlbnN1cmVzIHRo
YXQgdGhlcmUgYXJlIG5vIHRyYW5zYWN0aW9ucyBydW5uaW5nIA0KdGhhdCBtaWdodCd2ZSBzZWVu
IGEgcmVmZXJlbmNlIHRvIHRoYXQgcGFnZS4gT25seSBhZnRlciB0aGF0IHRoZSBwYWdlIA0KY2Fu
IGJlIHJldXNlZC4NCg0KVGhhdCdzIHF1aXRlIHBlc3NpbWFsOyBpbiBtb3N0IGNhc2VzIHRoZSBw
YWdlIGNvdWxkIGJlIHJldXNlZCBtdWNoIA0KZWFybGllciwgYmVjYXVzZSBpdCBpcyBhIHZlcnkg
dGlnaHQgd2luZG93IGZvciB2YWN1dW0gdG8gZGVsZXRlIGEgcGFnZSANCmp1c3Qgd2hlbiBhIGNv
bmN1cnJlbnQgc2NhbiBoYXMgcmVhZCBhIGxpbmsgdG8gdGhlIHBhZ2UgYW5kIGlzIGFib3V0IHRv
IA0KZm9sbG93IGl0LiBCdXQgQi10cmVlIHBhZ2VzIGFyZSB1c3VhbGx5IG5vdCBkZWxldGVkIHRo
YXQgb2Z0ZW4gdGhhdCBpdCANCndvdWxkIG1hdHRlciBpbiBwcmFjdGljZSwgc28gdGhlcmUgaGFz
IGJlZW4gbm8gZWZmb3J0IHRvIG9wdGltaXplIGl0Lg0KDQpJbiBzaG9ydCwgaXQncyBub3QgYSBi
dWcgOi0pLg0KDQpbMV0gDQpodHRwOi8vZ2l0LnBvc3RncmVzcWwub3JnL2dpdHdlYi8/cD1wb3N0
Z3Jlc3FsLmdpdDthPWJsb2I7Zj1zcmMvYmFja2VuZC9hY2Nlc3MvbmJ0cmVlL1JFQURNRSNsMjgz
DQoNCi0gSGVpa2tp