0001-Allow-pgstathashindex-to-show-unused-pages-v1.patch

From e3b59fa85f16d6d15be5360e85b7faf63e8683a9 Mon Sep 17 00:00:00 2001
From: ashu <ashu@localhost.localdomain>
Date: Thu, 23 Mar 2017 23:02:26 +0530
Subject: [PATCH] Allow pgstathashindex to show unused pages v1

---
 contrib/pgstattuple/expected/pgstattuple.out  | 12 ++++++------
 contrib/pgstattuple/pgstatindex.c             | 19 ++++++++++++-------
 contrib/pgstattuple/pgstattuple--1.4--1.5.sql |  1 +
 doc/src/sgml/pgstattuple.sgml                 | 17 ++++++++++++-----
 4 files changed, 31 insertions(+), 18 deletions(-)

diff --git a/contrib/pgstattuple/expected/pgstattuple.out b/contrib/pgstattuple/expected/pgstattuple.out
index 2c3515b..1f1ff46 100644
--- a/contrib/pgstattuple/expected/pgstattuple.out
+++ b/contrib/pgstattuple/expected/pgstattuple.out
@@ -132,9 +132,9 @@ select * from pgstatginindex('test_ginidx');
 
 create index test_hashidx on test using hash (b);
 select * from pgstathashindex('test_hashidx');
- version | bucket_pages | overflow_pages | bitmap_pages | zero_pages | live_items | dead_items | free_percent 
----------+--------------+----------------+--------------+------------+------------+------------+--------------
-       2 |            4 |              0 |            1 |          0 |          0 |          0 |          100
+ version | bucket_pages | overflow_pages | bitmap_pages | zero_pages | unused_pages | live_items | dead_items | free_percent 
+---------+--------------+----------------+--------------+------------+--------------+------------+------------+--------------
+       2 |            4 |              0 |            1 |          0 |            0 |          0 |          0 |          100
 (1 row)
 
 -- these should error with the wrong type
@@ -233,9 +233,9 @@ select pgstatindex('test_partition_idx');
 (1 row)
 
 select pgstathashindex('test_partition_hash_idx');
-   pgstathashindex   
----------------------
- (2,8,0,1,0,0,0,100)
+    pgstathashindex    
+-----------------------
+ (2,8,0,1,0,0,0,0,100)
 (1 row)
 
 drop table test_partitioned;
diff --git a/contrib/pgstattuple/pgstatindex.c b/contrib/pgstattuple/pgstatindex.c
index d448e9e..6fc41d6 100644
--- a/contrib/pgstattuple/pgstatindex.c
+++ b/contrib/pgstattuple/pgstatindex.c
@@ -120,6 +120,7 @@ typedef struct HashIndexStat
 	BlockNumber overflow_pages;
 	BlockNumber bitmap_pages;
 	BlockNumber zero_pages;
+	BlockNumber unused_pages;
 
 	int64	live_items;
 	int64	dead_items;
@@ -588,8 +589,8 @@ pgstathashindex(PG_FUNCTION_ARGS)
 	BufferAccessStrategy bstrategy;
 	HeapTuple	tuple;
 	TupleDesc	tupleDesc;
-	Datum		values[8];
-	bool		nulls[8];
+	Datum		values[9];
+	bool		nulls[9];
 	Buffer		metabuf;
 	HashMetaPage	metap;
 	float8		free_percent;
@@ -667,6 +668,8 @@ pgstathashindex(PG_FUNCTION_ARGS)
 			}
 			else if (opaque->hasho_flag & LH_BITMAP_PAGE)
 				stats.bitmap_pages++;
+			else if (PageIsEmpty(page))
+				stats.unused_pages++;
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_INDEX_CORRUPTED),
@@ -680,8 +683,9 @@ pgstathashindex(PG_FUNCTION_ARGS)
 	/* Done accessing the index */
 	index_close(rel, AccessShareLock);
 
-	/* Count zero pages as free space. */
-	stats.free_space += stats.zero_pages * stats.space_per_page;
+	/* Count zero and unused pages as free space. */
+	stats.free_space += (stats.zero_pages + stats.unused_pages) *
+						 stats.space_per_page;
 
 	/*
 	 * Total space available for tuples excludes the metapage and the bitmap
@@ -711,9 +715,10 @@ pgstathashindex(PG_FUNCTION_ARGS)
 	values[2] = Int64GetDatum((int64) stats.overflow_pages);
 	values[3] = Int64GetDatum((int64) stats.bitmap_pages);
 	values[4] = Int64GetDatum((int64) stats.zero_pages);
-	values[5] = Int64GetDatum(stats.live_items);
-	values[6] = Int64GetDatum(stats.dead_items);
-	values[7] = Float8GetDatum(free_percent);
+	values[5] = Int64GetDatum((int64) stats.unused_pages);
+	values[6] = Int64GetDatum(stats.live_items);
+	values[7] = Int64GetDatum(stats.dead_items);
+	values[8] = Float8GetDatum(free_percent);
 	tuple = heap_form_tuple(tupleDesc, values, nulls);
 
 	PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
diff --git a/contrib/pgstattuple/pgstattuple--1.4--1.5.sql b/contrib/pgstattuple/pgstattuple--1.4--1.5.sql
index 84e112e..eda719a 100644
--- a/contrib/pgstattuple/pgstattuple--1.4--1.5.sql
+++ b/contrib/pgstattuple/pgstattuple--1.4--1.5.sql
@@ -118,6 +118,7 @@ CREATE OR REPLACE FUNCTION pgstathashindex(IN relname regclass,
 	OUT overflow_pages BIGINT,
 	OUT bitmap_pages BIGINT,
 	OUT zero_pages BIGINT,
+	OUT unused_pages BIGINT,
 	OUT live_items BIGINT,
 	OUT dead_items BIGINT,
 	OUT free_percent FLOAT8)
diff --git a/doc/src/sgml/pgstattuple.sgml b/doc/src/sgml/pgstattuple.sgml
index 62b1a6f..3287792 100644
--- a/doc/src/sgml/pgstattuple.sgml
+++ b/doc/src/sgml/pgstattuple.sgml
@@ -368,13 +368,14 @@ pending_tuples | 0
 test=&gt; select * from pgstathashindex('con_hash_index');
 -[ RECORD 1 ]--+-----------------
 version        | 2
-bucket_pages   | 33081
-overflow_pages | 0
+bucket_pages   | 2235
+overflow_pages | 1979
 bitmap_pages   | 1
-zero_pages     | 32455
-live_items     | 10204006
+zero_pages     | 1860
+unused_pages   | 17
+live_items     | 914628
 dead_items     | 0
-free_percent   | 61.8005949100872
+free_percent   | 63.1598221865334
 </programlisting>
      </para>
 
@@ -423,6 +424,12 @@ free_percent   | 61.8005949100872
        </row>
 
        <row>
+        <entry><structfield>unused_pages</structfield></entry>
+        <entry><type>bigint</type></entry>
+        <entry>Number of unused pages</entry>
+       </row>
+
+       <row>
         <entry><structfield>live_items</structfield></entry>
         <entry><type>bigint</type></entry>
         <entry>Number of live tuples</entry>
-- 
1.8.3.1