Fix pgstatindex using for large indexes - Mailing list pgsql-patches

From Tatsuhito Kasahara
Subject Fix pgstatindex using for large indexes
Date
Msg-id 47BD8093.7060105@oss.ntt.co.jp
Whole thread Raw
Responses Re: Fix pgstatindex using for large indexes  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-patches
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;


pgsql-patches by date:

Previous
From:
Date:
Subject: Re: 2WRS [WIP]
Next
From: "Dave Page"
Date:
Subject: Fix for initdb failures on Vista