Hi.
In pgstatindex.c and pgstattuple.sql, some variables are defined with
int type. So when we try to get informations about a large index by using
pgstatindex, we get strange value of size and density.
Because the values exceed int-max.
# Like following output. I used pgstatindex just after data load.
So "density" is should be nearly 90.
test=# SELECT * FROM pgstatindex('large_index');
-[ RECORD 1 ]------+------------
version | 2
tree_level | 4
index_size | -1349410816 ★
root_block_no | 119666
internal_pages | 28936
leaf_pages | 1379204
empty_pages | 0
deleted_pages | 0
avg_leaf_density | 60.33 ★
leaf_fragmentation | 0
I think that max_avail and free_space should be uint64.
And the output format for index_size should be "%lld" (INT64_FORMAT).
I made the patch and tryed it. (And it seemed OK.)
test=# SELECT * FROM pgstatindex('large_index');
-[ RECORD 1 ]------+------------
version | 2
tree_level | 4
index_size | 11535491072
root_block_no | 119666
internal_pages | 28936
leaf_pages | 1379204
empty_pages | 0
deleted_pages | 0
avg_leaf_density | 90.64
leaf_fragmentation | 0
I also fix *_pages variables just in case.
Please confirm this.
Best regards.
--
NTT OSS Center
Tatsuhito Kasahara
kasahara.tatsuhito _at_ oss.ntt.co.jp
diff -crN postgresql-8.3.0.org/contrib/pgstattuple/pgstatindex.c
postgresql-8.3.0/contrib/pgstattuple/pgstatindex.c
*** postgresql-8.3.0.org/contrib/pgstattuple/pgstatindex.c 2007-11-16
06:14:31.000000000 +0900
--- postgresql-8.3.0/contrib/pgstattuple/pgstatindex.c 2008-02-21
22:34:40.000000000 +0900
***************
*** 63,77 ****
uint32 level;
uint32 root_pages;
! uint32 internal_pages;
! uint32 leaf_pages;
! uint32 empty_pages;
! uint32 deleted_pages;
! uint32 max_avail;
! uint32 free_space;
! uint32 fragments;
} BTIndexStat;
/* ------------------------------------------------------
--- 63,77 ----
uint32 level;
uint32 root_pages;
! uint64 internal_pages;
! uint64 leaf_pages;
! uint64 empty_pages;
! uint64 deleted_pages;
! uint64 max_avail;
! uint64 free_space;
! uint64 fragments;
} BTIndexStat;
/* ------------------------------------------------------
***************
*** 87,94 ****
Relation rel;
RangeVar *relrv;
Datum result;
! uint32 nblocks;
! uint32 blkno;
BTIndexStat indexStat;
if (!superuser())
--- 87,94 ----
Relation rel;
RangeVar *relrv;
Datum result;
! BlockNumber nblocks;
! BlockNumber blkno;
BTIndexStat indexStat;
if (!superuser())
***************
*** 207,213 ****
values[j] = palloc(32);
snprintf(values[j++], 32, "%d", indexStat.level);
values[j] = palloc(32);
! snprintf(values[j++], 32, "%d", (indexStat.root_pages +
indexStat.leaf_pages +
indexStat.internal_pages +
indexStat.deleted_pages +
--- 207,213 ----
values[j] = palloc(32);
snprintf(values[j++], 32, "%d", indexStat.level);
values[j] = palloc(32);
! snprintf(values[j++], 32, INT64_FORMAT, (indexStat.root_pages +
indexStat.leaf_pages +
indexStat.internal_pages +
indexStat.deleted_pages +
***************
*** 215,231 ****
values[j] = palloc(32);
snprintf(values[j++], 32, "%d", indexStat.root_blkno);
values[j] = palloc(32);
! snprintf(values[j++], 32, "%d", indexStat.internal_pages);
values[j] = palloc(32);
! snprintf(values[j++], 32, "%d", indexStat.leaf_pages);
values[j] = palloc(32);
! snprintf(values[j++], 32, "%d", indexStat.empty_pages);
values[j] = palloc(32);
! snprintf(values[j++], 32, "%d", indexStat.deleted_pages);
values[j] = palloc(32);
! snprintf(values[j++], 32, "%.2f", 100.0 - (float)
indexStat.free_space / (float) indexStat.max_avail * 100.0);
values[j] = palloc(32);
! snprintf(values[j++], 32, "%.2f", (float) indexStat.fragments /
(float) indexStat.leaf_pages * 100.0);
tuple =
BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
values);
--- 215,231 ----
values[j] = palloc(32);
snprintf(values[j++], 32, "%d", indexStat.root_blkno);
values[j] = palloc(32);
! snprintf(values[j++], 32, INT64_FORMAT,
indexStat.internal_pages);
values[j] = palloc(32);
! snprintf(values[j++], 32, INT64_FORMAT, indexStat.leaf_pages);
values[j] = palloc(32);
! snprintf(values[j++], 32, INT64_FORMAT, indexStat.empty_pages);
values[j] = palloc(32);
! snprintf(values[j++], 32, INT64_FORMAT,
indexStat.deleted_pages);
values[j] = palloc(32);
! snprintf(values[j++], 32, "%.2f", 100.0 - (double)
indexStat.free_space / (double) indexStat.max_avail * 100.0);
values[j] = palloc(32);
! snprintf(values[j++], 32, "%.2f", (double) indexStat.fragments
/ (double) indexStat.leaf_pages * 100.0);
tuple =
BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
values);
diff -crN postgresql-8.3.0.org/contrib/pgstattuple/pgstattuple.sql.in
postgresql-8.3.0/contrib/pgstattuple/pgstattuple.sql.in
*** postgresql-8.3.0.org/contrib/pgstattuple/pgstattuple.sql.in 2007-11-13
13:24:28.000000000 +0900
--- postgresql-8.3.0/contrib/pgstattuple/pgstattuple.sql.in 2008-02-21
21:33:02.000000000 +0900
***************
*** 33,48 ****
-- pgstatindex
--
CREATE OR REPLACE FUNCTION pgstatindex(IN relname text,
! OUT version int4,
! OUT tree_level int4,
! OUT index_size int4,
! OUT root_block_no int4,
! OUT internal_pages int4,
! OUT leaf_pages int4,
! OUT empty_pages int4,
! OUT deleted_pages int4,
! OUT avg_leaf_density float8,
! OUT leaf_fragmentation float8)
AS 'MODULE_PATHNAME', 'pgstatindex'
LANGUAGE C STRICT;
--- 33,48 ----
-- pgstatindex
--
CREATE OR REPLACE FUNCTION pgstatindex(IN relname text,
! OUT version INT,
! OUT tree_level INT,
! OUT index_size BIGINT,
! OUT root_block_no INT,
! OUT internal_pages BIGINT,
! OUT leaf_pages BIGINT,
! OUT empty_pages BIGINT,
! OUT deleted_pages BIGINT,
! OUT avg_leaf_density FLOAT8,
! OUT leaf_fragmentation FLOAT8)
AS 'MODULE_PATHNAME', 'pgstatindex'
LANGUAGE C STRICT;
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
