BUG #9757: Why reclaim index deleted pages need twice vacuum - Mailing list pgsql-bugs

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 +