Hello.
I found a bug in contrib/pgstatindex.c to reports a strange value of
leaf_fragmentation with some cases.
#Look the following test example.
In GetBTPageStatistics(), stat->fragments is not initialized properly
so that invalid leaf_fragments values are inserted in results.
For example, GetBTPageStatistics() read a dead_leaf_block
(stat->type = 'd';) first, stat->fragments hold a strange value because
stat->fragments is not initialized before "return true"(pgstatindex.c
164th line)
And then, the strange value is just inserted in result. Under normal
circumstances, stat.fragments must be incremented only when stat->type
is 'l'.
So pgstatindex reports strange leaf_fragmentation.
I made the patch.
Please confirm this.
Best regards.
======== Test (postgresql8.2.1)========
test=# CREATE TABLE test (id int);
CREATE TABLE
test=# INSERT INTO test select generate_series(1,1000);
INSERT 0 1000
test=# CREATE INDEX idx_test ON test(id);
CREATE INDEX
test=# SELECT leaf_fragmentation from pgstatindex('idx_test');
-[ RECORD 1 ]------+--
leaf_fragmentation | 0
test=# DELETE FROM test;
DELETE 1000
test=# SELECT leaf_fragmentation from pgstatindex('idx_test');
-[ RECORD 1 ]------+--
leaf_fragmentation | 0
test=# VACUUM test;
VACUUM
test=# SELECT leaf_fragmentation from pgstatindex('idx_test');
-[ RECORD 1 ]------+------------
leaf_fragmentation | 28943878400 <- Invalid value
======== Test (postgresql8.2.1)========
=========gdb============
(gdb) b GetBTPageStatistics
Breakpoint 4 at 0xfd8126: file pgstatindex.c, line 145.
(gdb) c
Continuing.
Breakpoint 4, GetBTPageStatistics (blkno=1, buffer=101, stat=0xbfc3fbe4)
at pgstatindex.c:145
145 Page page = BufferGetPage(buffer);
(gdb) print stat.fragments
$4 = 144719392 <- Already have a invalid value.
(snip)
Breakpoint 3, GetBTPageStatistics (blkno=1, buffer=<value optimized out>,
stat=0xbfc3fbe4) at pgstatindex.c:161
161 if (P_ISDELETED(opaque))
(gdb) n
163 stat->type = 'd';
(snip)
pgstatindex (fcinfo=0xbfc3fc70) at pgstatindex.c:297
297 switch (stat.type)
(gdb) n
300 indexStat.deleted_pages++;
(gdb) n
319 indexStat.fragments += stat.fragments;
(gdb) print stat.fragments
$3 = 144719392 <- Just inserts inavlid value in result even if stat.type is
'dead'.
=========gdb============
=========patch test============
test=# CREATE TABLE test2 (id int);
CREATE TABLE
test=# INSERT INTO test2 select generate_series(1,1000);
INSERT 0 1000
test=# CREATE INDEX idx_test2 ON test2(id);
CREATE INDEX
test=# DELETE FROM test2;
DELETE 1000
test=# VACUUM test2;
VACUUM
test=# SELECT leaf_fragmentation from pgstatindex('idx_test2');
-[ RECORD 1 ]------+--
leaf_fragmentation | 0 <- OK
test=# INSERT INTO test2 select generate_series(1,1000);
INSERT 0 1000
test=# SELECT leaf_fragmentation from pgstatindex('idx_test2');
-[ RECORD 1 ]------+--
leaf_fragmentation | 0 <- OK
test=# INSERT INTO test2 select generate_series(1,1000);
INSERT 0 1000
test=# SELECT leaf_fragmentation from pgstatindex('idx_test2');
-[ RECORD 1 ]------+------
leaf_fragmentation | 28.57 <- OK.
=========patch test============
--
NTT OSS Center
Tatsuhito Kasahara
kasahara.tatsuhito _at_ oss.ntt.co.jp
*** pgstatindex.c.org 2006-09-04 11:03:04.000000000 +0900
--- pgstatindex.c 2007-03-08 01:06:14.000000000 +0900
***************
*** 154,159 ****
--- 154,160 ----
stat->max_avail = BLCKSZ - (BLCKSZ - phdr->pd_special +
SizeOfPageHeaderData);
stat->dead_items = stat->live_items = 0;
+ stat->fragments = 0;
stat->page_size = PageGetPageSize(page);
***************
*** 187,193 ****
* it means a fragmentation.
*----------------------------------------------
*/
- stat->fragments = 0;
if (stat->type == 'l')
{
if (opaque->btpo_next != P_NONE && opaque->btpo_next < blkno)
--- 188,193 ----
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
