Add pgstathashindex() to get hash index table statistics.

From fc6e175e52784502ac50fbd49b099f1300868f0b Mon Sep 17 00:00:00 2001
From: ashu <ashu@localhost.localdomain>
Date: Fri, 6 Jan 2017 18:38:36 +0530
Subject: [PATCH] Add pgstathashindex() to pgstattuple extension v2

This allows us to see the hash index table statistics.
We could have directly used pgstattuple() to see the
hash table statistics but it doesn't include some of
the stats related to hash index like number of bucket
pages, overflow pages, zero pages etc. Moreover, it
can't be used if hash index table contains a zero page.

The test-case added here needs to be changed when
WAL patch for hash index gets committed.

Patch by Ashutosh. Needs review.
---
 contrib/pgstattuple/Makefile                  |   8 +-
 contrib/pgstattuple/expected/pgstattuple.out  |   8 +
 contrib/pgstattuple/pgstatindex.c             | 236 ++++++++++++++++++++++++++
 contrib/pgstattuple/pgstattuple--1.4.sql      |  15 ++
 contrib/pgstattuple/pgstattuple--1.5--1.6.sql |  22 +++
 contrib/pgstattuple/pgstattuple.control       |   2 +-
 contrib/pgstattuple/sql/pgstattuple.sql       |   4 +
 doc/src/sgml/pgstattuple.sgml                 | 106 ++++++++++++
 8 files changed, 396 insertions(+), 5 deletions(-)
 create mode 100644 contrib/pgstattuple/pgstattuple--1.5--1.6.sql

diff --git a/contrib/pgstattuple/Makefile b/contrib/pgstattuple/Makefile
index 294077d..a1601ec 100644
--- a/contrib/pgstattuple/Makefile
+++ b/contrib/pgstattuple/Makefile
@@ -4,10 +4,10 @@ MODULE_big	= pgstattuple
 OBJS		= pgstattuple.o pgstatindex.o pgstatapprox.o $(WIN32RES)
 
 EXTENSION = pgstattuple
-DATA = pgstattuple--1.4.sql pgstattuple--1.4--1.5.sql \
-	pgstattuple--1.3--1.4.sql pgstattuple--1.2--1.3.sql \
-	pgstattuple--1.1--1.2.sql pgstattuple--1.0--1.1.sql \
-	pgstattuple--unpackaged--1.0.sql
+DATA = pgstattuple--1.5--1.6.sql pgstattuple--1.4--1.5.sql \
+	pgstattuple--1.4.sql pgstattuple--1.3--1.4.sql \
+	pgstattuple--1.2--1.3.sql pgstattuple--1.1--1.2.sql	\
+	pgstattuple--1.0--1.1.sql pgstattuple--unpackaged--1.0.sql
 PGFILEDESC = "pgstattuple - tuple-level statistics"
 
 REGRESS = pgstattuple
diff --git a/contrib/pgstattuple/expected/pgstattuple.out b/contrib/pgstattuple/expected/pgstattuple.out
index e920234..26d43c8 100644
--- a/contrib/pgstattuple/expected/pgstattuple.out
+++ b/contrib/pgstattuple/expected/pgstattuple.out
@@ -130,3 +130,11 @@ select * from pgstatginindex('test_ginidx');
        2 |             0 |              0
 (1 row)
 
+create index test_hashidx on test using hash (b);
+WARNING:  hash indexes are not WAL-logged and their use is discouraged
+select * from pgstathashindex('test_hashidx');
+ version | total_pages | bucket_pages | overflow_pages | bitmap_pages | zero_pages | ntuples | tuples_per_bucket | live_items | dead_items | free_percent 
+---------+-------------+--------------+----------------+--------------+------------+---------+-------------------+------------+------------+--------------
+       2 |           6 |            4 |              0 |            1 |          0 |       0 |               307 |          0 |          0 |          100
+(1 row)
+
diff --git a/contrib/pgstattuple/pgstatindex.c b/contrib/pgstattuple/pgstatindex.c
index d9a722a..032253e 100644
--- a/contrib/pgstattuple/pgstatindex.c
+++ b/contrib/pgstattuple/pgstatindex.c
@@ -29,6 +29,7 @@
 
 #include "access/gin_private.h"
 #include "access/heapam.h"
+#include "access/hash.h"
 #include "access/htup_details.h"
 #include "access/nbtree.h"
 #include "catalog/namespace.h"
@@ -36,6 +37,7 @@
 #include "funcapi.h"
 #include "miscadmin.h"
 #include "storage/bufmgr.h"
+#include "storage/lmgr.h"
 #include "utils/builtins.h"
 #include "utils/rel.h"
 
@@ -53,6 +55,7 @@ PG_FUNCTION_INFO_V1(pgstatindexbyid);
 PG_FUNCTION_INFO_V1(pg_relpages);
 PG_FUNCTION_INFO_V1(pg_relpagesbyid);
 PG_FUNCTION_INFO_V1(pgstatginindex);
+PG_FUNCTION_INFO_V1(pgstathashindex);
 
 PG_FUNCTION_INFO_V1(pgstatindex_v1_5);
 PG_FUNCTION_INFO_V1(pgstatindexbyid_v1_5);
@@ -60,11 +63,23 @@ PG_FUNCTION_INFO_V1(pg_relpages_v1_5);
 PG_FUNCTION_INFO_V1(pg_relpagesbyid_v1_5);
 PG_FUNCTION_INFO_V1(pgstatginindex_v1_5);
 
+PG_FUNCTION_INFO_V1(pgstathashindex_v1_6);
+
 Datum pgstatginindex_internal(Oid relid, FunctionCallInfo fcinfo);
+Datum pgstathashindex_internal(Oid relid, FunctionCallInfo fcinfo);
 
 #define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
 #define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
 #define IS_GIN(r) ((r)->rd_rel->relam == GIN_AM_OID)
+#define IS_HASH(r) ((r)->rd_rel->relam == HASH_AM_OID)
+
+/*
+ * HASH_ALLOCATABLE_PAGE_SZ represents allocatable
+ * space (pd_upper - pd_lower) on a hash page.
+ */
+#define HASH_ALLOCATABLE_PAGE_SZ \
+		BLCKSZ - \
+		(SizeOfPageHeaderData + sizeof(HashPageOpaqueData))
 
 /* ------------------------------------------------
  * A structure for a whole btree index statistics
@@ -101,7 +116,29 @@ typedef struct GinIndexStat
 	int64		pending_tuples;
 } GinIndexStat;
 
+/* ------------------------------------------------
+ * A structure for a whole HASH index statistics
+ * used by pgstathashindex().
+ * ------------------------------------------------
+ */
+typedef struct HashIndexStat
+{
+	uint32	version;
+	uint32	total_pages;
+	uint32	bucket_pages;
+	uint32	overflow_pages;
+	uint32	bitmap_pages;
+	uint32	zero_pages;
+	uint64	ntuples;
+	uint16	ffactor;
+	uint64	live_items;
+	uint64	dead_items;
+	uint64	free_space;
+} HashIndexStat;
+
 static Datum pgstatindex_impl(Relation rel, FunctionCallInfo fcinfo);
+static void GetHashPageStats(Page page, HashIndexStat *stats);
+
 
 /* ------------------------------------------------------
  * pgstatindex()
@@ -527,3 +564,202 @@ pgstatginindex_internal(Oid relid, FunctionCallInfo fcinfo)
 
 	return (result);
 }
+
+/* ------------------------------------------------------
+ * pgstathashindex()
+ *
+ * Usage: SELECT * FROM pgstathashindex('hashindex');
+ *
+ * Must keep superuser() check, see above.
+ * ------------------------------------------------------
+ */
+Datum
+pgstathashindex(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+
+	if (!superuser())
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 (errmsg("must be superuser to use pgstattuple functions"))));
+
+	PG_RETURN_DATUM(pgstathashindex_internal(relid, fcinfo));
+}
+
+/* No need for superuser checks from v1.5 onwards, see above */
+Datum
+pgstathashindex_v1_6(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+
+	PG_RETURN_DATUM(pgstathashindex_internal(relid, fcinfo));
+}
+
+Datum
+pgstathashindex_internal(Oid relid, FunctionCallInfo fcinfo)
+{
+	BlockNumber	nblocks;
+	BlockNumber	blkno;
+	Relation	rel;
+	HashIndexStat stats = {0};
+	HeapTuple	tuple;
+	TupleDesc	tupleDesc;
+	Datum		values[11];
+	bool		nulls[11];
+	Datum		result;
+	Buffer		metabuf;
+	HashMetaPage	metap;
+	float4		free_percent;
+	uint64		table_len;
+
+	rel = index_open(relid, AccessShareLock);
+
+	if (!IS_HASH(rel))
+		elog(ERROR, "relation \"%s\" is not a HASH index",
+			 RelationGetRelationName(rel));
+
+	/*
+	 * Reject attempts to read non-local temporary relations; we would be
+	 * likely to get wrong data since we have no visibility into the owning
+	 * session's local buffers.
+	 */
+	if (RELATION_IS_OTHER_TEMP(rel))
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+			   errmsg("cannot access temporary indexes of other sessions")));
+
+	/* Get the current relation length */
+	LockRelationForExtension(rel, ExclusiveLock);
+	nblocks = RelationGetNumberOfBlocks(rel);
+	UnlockRelationForExtension(rel, ExclusiveLock);
+
+	stats.total_pages = nblocks;
+
+	/* Start from blkno 1 as 0th block is metapage */
+	for (blkno = 1; blkno < nblocks; blkno++)
+	{
+		Buffer		buf;
+		Page		page;
+		HashPageOpaque	opaque;
+
+		CHECK_FOR_INTERRUPTS();
+
+		buf = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, NULL);
+		LockBuffer(buf, BUFFER_LOCK_SHARE);
+		page = (Page) BufferGetPage(buf);
+
+		if (PageIsNew(page))
+			stats.zero_pages++;
+		else if (PageGetSpecialSize(page) != MAXALIGN(sizeof(HashPageOpaqueData)))
+			ereport(ERROR,
+					(errcode(ERRCODE_INDEX_CORRUPTED),
+					 errmsg("index \"%s\" contains corrupted page at block %u",
+							RelationGetRelationName(rel),
+							BufferGetBlockNumber(buf)),
+			errhint("Please REINDEX it.")));
+		else
+		{
+			opaque = (HashPageOpaque) PageGetSpecialPointer(page);
+			if (opaque->hasho_flag & LH_BUCKET_PAGE)
+			{
+				stats.bucket_pages++;
+				GetHashPageStats(page, &stats);
+			}
+			else if (opaque->hasho_flag & LH_OVERFLOW_PAGE)
+			{
+				stats.overflow_pages++;
+				GetHashPageStats(page, &stats);
+			}
+			else if (opaque->hasho_flag & LH_BITMAP_PAGE)
+				stats.bitmap_pages++;
+			else
+				ereport(ERROR,
+						(errcode(ERRCODE_INDEX_CORRUPTED),
+					errmsg("unexpected page type 0x%04X in HASH index \"%s\" block %u",
+							opaque->hasho_flag, RelationGetRelationName(rel),
+							BufferGetBlockNumber(buf))));
+		}
+		UnlockReleaseBuffer(buf);
+	}
+
+	/* Read the metapage so we can determine things like ntuples, ffactor etc. */
+	metabuf = _hash_getbuf(rel, HASH_METAPAGE, HASH_READ, LH_META_PAGE);
+	metap = HashPageGetMeta(BufferGetPage(metabuf));
+
+	stats.ntuples = metap->hashm_ntuples;
+	stats.version = metap->hashm_version;
+	stats.ffactor = metap->hashm_ffactor;
+
+	stats.free_space += ((HASH_ALLOCATABLE_PAGE_SZ) * stats.zero_pages);
+
+	/*
+	 * Let us ignore metapage and bitmap page when calculating
+	 * free space percentage for tuples in a table.
+	 */
+	table_len = (stats.total_pages - (stats.bitmap_pages + 1)) *
+									(HASH_ALLOCATABLE_PAGE_SZ);
+
+	if (table_len == 0)
+		free_percent = 0.0;
+	else
+		free_percent = 100.0 * stats.free_space / table_len;
+
+	_hash_relbuf(rel, metabuf);
+
+	index_close(rel, AccessShareLock);
+
+	/*
+	 * Build a tuple descriptor for our result type
+	 */
+	if (get_call_result_type(fcinfo, NULL, &tupleDesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, "return type must be a row type");
+
+	tupleDesc = BlessTupleDesc(tupleDesc);
+
+	MemSet(nulls, 0, sizeof(nulls));
+
+	values[0] = UInt32GetDatum(stats.version);
+	values[1] = UInt32GetDatum(stats.total_pages);
+	values[2] = UInt32GetDatum(stats.bucket_pages);
+	values[3] = UInt32GetDatum(stats.overflow_pages);
+	values[4] = UInt32GetDatum(stats.bitmap_pages);
+	values[5] = UInt32GetDatum(stats.zero_pages);
+	values[6] = UInt64GetDatum(stats.ntuples);
+	values[7] = UInt16GetDatum(stats.ffactor);
+	values[8] = UInt64GetDatum(stats.live_items);
+	values[9] = UInt64GetDatum(stats.dead_items);
+	values[10] = Float4GetDatum(free_percent);
+
+	/*
+	 * Build and return the tuple
+	 */
+	tuple = heap_form_tuple(tupleDesc, values, nulls);
+	result = HeapTupleGetDatum(tuple);
+
+	return (result);
+}
+
+/* -------------------------------------------------
+ * GetHashPageStatis()
+ *
+ * Collect statistics of single hash page
+ * -------------------------------------------------
+ */
+static void
+GetHashPageStats(Page page, HashIndexStat *stats)
+{
+	OffsetNumber maxoff = PageGetMaxOffsetNumber(page);
+	int off;
+
+	/* count live and dead tuples, and free space */
+	for (off = FirstOffsetNumber; off <= maxoff; off++)
+	{
+		ItemId      id = PageGetItemId(page, off);
+
+		if (!ItemIdIsDead(id))
+			stats->live_items++;
+		else
+			stats->dead_items++;
+	}
+	stats->free_space += PageGetExactFreeSpace(page);
+}
diff --git a/contrib/pgstattuple/pgstattuple--1.4.sql b/contrib/pgstattuple/pgstattuple--1.4.sql
index 47377eb..94828bf 100644
--- a/contrib/pgstattuple/pgstattuple--1.4.sql
+++ b/contrib/pgstattuple/pgstattuple--1.4.sql
@@ -35,6 +35,21 @@ RETURNS BIGINT
 AS 'MODULE_PATHNAME', 'pg_relpages'
 LANGUAGE C STRICT PARALLEL SAFE;
 
+CREATE FUNCTION pgstathashindex(IN relname regclass,
+    OUT version INT4,
+    OUT total_pages INT4,
+    OUT bucket_pages INT4,
+    OUT overflow_pages INT4,
+    OUT bitmap_pages INT4,
+    OUT zero_pages INT4,
+    OUT ntuples BIGINT,
+    OUT tuples_per_bucket BIGINT,
+    OUT live_items BIGINT,
+    OUT dead_items BIGINT,
+    OUT free_percent FLOAT4)
+AS 'MODULE_PATHNAME', 'pgstathashindex'
+LANGUAGE C STRICT PARALLEL SAFE;
+
 /* New stuff in 1.1 begins here */
 
 CREATE FUNCTION pgstatginindex(IN relname regclass,
diff --git a/contrib/pgstattuple/pgstattuple--1.5--1.6.sql b/contrib/pgstattuple/pgstattuple--1.5--1.6.sql
new file mode 100644
index 0000000..83eebd8
--- /dev/null
+++ b/contrib/pgstattuple/pgstattuple--1.5--1.6.sql
@@ -0,0 +1,22 @@
+/* contrib/pgstattuple/pgstattuple--1.5--1.6.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pgstattuple UPDATE TO '1.6'" to load this file. \quit
+
+
+CREATE OR REPLACE FUNCTION pgstathashindex(IN relname regclass,
+	OUT version INT4,
+	OUT total_pages INT4,
+	OUT bucket_pages INT4,
+	OUT overflow_pages INT4,
+	OUT bitmap_pages INT4,
+	OUT zero_pages INT4,
+	OUT ntuples BIGINT,
+	OUT tuples_per_bucket BIGINT,
+	OUT live_items BIGINT,
+	OUT dead_items BIGINT,
+	OUT free_percent FLOAT4)
+AS 'MODULE_PATHNAME', 'pgstathashindex_v1_6'
+LANGUAGE C STRICT PARALLEL SAFE;
+
+REVOKE EXECUTE ON FUNCTION pgstathashindex(regclass) FROM PUBLIC;
diff --git a/contrib/pgstattuple/pgstattuple.control b/contrib/pgstattuple/pgstattuple.control
index 6af4075..80d0695 100644
--- a/contrib/pgstattuple/pgstattuple.control
+++ b/contrib/pgstattuple/pgstattuple.control
@@ -1,5 +1,5 @@
 # pgstattuple extension
 comment = 'show tuple-level statistics'
-default_version = '1.5'
+default_version = '1.6'
 module_pathname = '$libdir/pgstattuple'
 relocatable = true
diff --git a/contrib/pgstattuple/sql/pgstattuple.sql b/contrib/pgstattuple/sql/pgstattuple.sql
index d22c9f1..81fd5d6 100644
--- a/contrib/pgstattuple/sql/pgstattuple.sql
+++ b/contrib/pgstattuple/sql/pgstattuple.sql
@@ -47,3 +47,7 @@ select pg_relpages(relname) from pg_class where relname = 'test_pkey';
 create index test_ginidx on test using gin (b);
 
 select * from pgstatginindex('test_ginidx');
+
+create index test_hashidx on test using hash (b);
+
+select * from pgstathashindex('test_hashidx');
diff --git a/doc/src/sgml/pgstattuple.sgml b/doc/src/sgml/pgstattuple.sgml
index d2fa524..6a5c12d 100644
--- a/doc/src/sgml/pgstattuple.sgml
+++ b/doc/src/sgml/pgstattuple.sgml
@@ -355,6 +355,112 @@ pending_tuples | 0
    <varlistentry>
     <term>
      <indexterm>
+      <primary>pgstathashindex</primary>
+     </indexterm>
+     <function>pgstathashindex(regclass) returns record</>
+    </term>
+
+    <listitem>
+     <para>
+      <function>pgstathashindex</function> returns a record showing information
+      about a HASH index.  For example:
+<programlisting>
+test=&gt; select * from pgstathashindex('con_hash_index');
+ version | total_pages | bucket_pages | overflow_pages | bitmap_pages | zero_pages | ntuples | tuples_per_bucket | live_items | dead_items | free_percent 
+---------+-------------+--------------+----------------+--------------+------------+---------+-------------------+------------+------------+--------------
+       2 |        5598 |         4096 |           1500 |            1 |          0 |  686000 |               307 |     686000 |          0 |      69.9245
+</programlisting>
+     </para>
+
+    <para>
+     The output columns are:
+
+    <informaltable>
+     <tgroup cols="3">
+      <thead>
+       <row>
+        <entry>Column</entry>
+        <entry>Type</entry>
+        <entry>Description</entry>
+       </row>
+      </thead>
+
+      <tbody>
+       <row>
+        <entry><structfield>version</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>HASH version number</entry>
+       </row>
+
+       <row>
+        <entry><structfield>total_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>bucket_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of bucket pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>overflow_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of overflow pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>bitmap_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of bitmap pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>zero_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of new or zero pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>ntuples</structfield></entry>
+        <entry><type>bigint</type></entry>
+        <entry>Total number of tuples in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>tuples_per_bucket</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Average number of tuples per bucket</entry>
+       </row>
+
+       <row>
+        <entry><structfield>live_items</structfield></entry>
+        <entry><type>bigint</type></entry>
+        <entry>Total number of alive tuples in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>dead_tuples</structfield></entry>
+        <entry><type>bigint</type></entry>
+        <entry>Total number of dead tuples in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>free_percent</structfield></entry>
+        <entry><type>float</type></entry>
+        <entry>Percentage of free space available in the hash table</entry>
+       </row>
+
+      </tbody>
+     </tgroup>
+    </informaltable>
+    </para>
+    </listitem>
+   </varlistentry>
+   <varlistentry>
+    <term>
+     <indexterm>
       <primary>pg_relpages</primary>
      </indexterm>
      <function>pg_relpages(regclass) returns bigint</>
-- 
1.8.3.1

From 333739eba91736b04164e29bfa170b22d8cd1c51 Mon Sep 17 00:00:00 2001
From: ashu <ashu@localhost.localdomain>
Date: Thu, 19 Jan 2017 12:02:56 +0530
Subject: [PATCH] Add pgstathashindex() to pgstattuple extension v3

This allows us to see the hash index table statistics.
We could have directly used pgstattuple() to see the
hash table statistics but it doesn't include some of
the stats related to hash index like number of bucket
pages, overflow pages, zero pages etc. Moreover, it
can't be used if hash index table contains a zero page.

The test-case added here needs to be changed when
WAL patch for hash index gets committed.

Patch by Ashutosh.
---
 contrib/pgstattuple/Makefile                  |   8 +-
 contrib/pgstattuple/expected/pgstattuple.out  |   8 +
 contrib/pgstattuple/pgstatindex.c             | 240 ++++++++++++++++++++++++++
 contrib/pgstattuple/pgstattuple--1.4.sql      |  15 ++
 contrib/pgstattuple/pgstattuple--1.5--1.6.sql |  22 +++
 contrib/pgstattuple/pgstattuple.control       |   2 +-
 contrib/pgstattuple/sql/pgstattuple.sql       |   4 +
 doc/src/sgml/pgstattuple.sgml                 | 106 ++++++++++++
 8 files changed, 400 insertions(+), 5 deletions(-)
 create mode 100644 contrib/pgstattuple/pgstattuple--1.5--1.6.sql

diff --git a/contrib/pgstattuple/Makefile b/contrib/pgstattuple/Makefile
index 294077d..a1601ec 100644
--- a/contrib/pgstattuple/Makefile
+++ b/contrib/pgstattuple/Makefile
@@ -4,10 +4,10 @@ MODULE_big	= pgstattuple
 OBJS		= pgstattuple.o pgstatindex.o pgstatapprox.o $(WIN32RES)
 
 EXTENSION = pgstattuple
-DATA = pgstattuple--1.4.sql pgstattuple--1.4--1.5.sql \
-	pgstattuple--1.3--1.4.sql pgstattuple--1.2--1.3.sql \
-	pgstattuple--1.1--1.2.sql pgstattuple--1.0--1.1.sql \
-	pgstattuple--unpackaged--1.0.sql
+DATA = pgstattuple--1.5--1.6.sql pgstattuple--1.4--1.5.sql \
+	pgstattuple--1.4.sql pgstattuple--1.3--1.4.sql \
+	pgstattuple--1.2--1.3.sql pgstattuple--1.1--1.2.sql	\
+	pgstattuple--1.0--1.1.sql pgstattuple--unpackaged--1.0.sql
 PGFILEDESC = "pgstattuple - tuple-level statistics"
 
 REGRESS = pgstattuple
diff --git a/contrib/pgstattuple/expected/pgstattuple.out b/contrib/pgstattuple/expected/pgstattuple.out
index e920234..26d43c8 100644
--- a/contrib/pgstattuple/expected/pgstattuple.out
+++ b/contrib/pgstattuple/expected/pgstattuple.out
@@ -130,3 +130,11 @@ select * from pgstatginindex('test_ginidx');
        2 |             0 |              0
 (1 row)
 
+create index test_hashidx on test using hash (b);
+WARNING:  hash indexes are not WAL-logged and their use is discouraged
+select * from pgstathashindex('test_hashidx');
+ version | total_pages | bucket_pages | overflow_pages | bitmap_pages | zero_pages | ntuples | tuples_per_bucket | live_items | dead_items | free_percent 
+---------+-------------+--------------+----------------+--------------+------------+---------+-------------------+------------+------------+--------------
+       2 |           6 |            4 |              0 |            1 |          0 |       0 |               307 |          0 |          0 |          100
+(1 row)
+
diff --git a/contrib/pgstattuple/pgstatindex.c b/contrib/pgstattuple/pgstatindex.c
index d9a722a..5820cb5 100644
--- a/contrib/pgstattuple/pgstatindex.c
+++ b/contrib/pgstattuple/pgstatindex.c
@@ -29,6 +29,7 @@
 
 #include "access/gin_private.h"
 #include "access/heapam.h"
+#include "access/hash.h"
 #include "access/htup_details.h"
 #include "access/nbtree.h"
 #include "catalog/namespace.h"
@@ -36,6 +37,7 @@
 #include "funcapi.h"
 #include "miscadmin.h"
 #include "storage/bufmgr.h"
+#include "storage/lmgr.h"
 #include "utils/builtins.h"
 #include "utils/rel.h"
 
@@ -53,6 +55,7 @@ PG_FUNCTION_INFO_V1(pgstatindexbyid);
 PG_FUNCTION_INFO_V1(pg_relpages);
 PG_FUNCTION_INFO_V1(pg_relpagesbyid);
 PG_FUNCTION_INFO_V1(pgstatginindex);
+PG_FUNCTION_INFO_V1(pgstathashindex);
 
 PG_FUNCTION_INFO_V1(pgstatindex_v1_5);
 PG_FUNCTION_INFO_V1(pgstatindexbyid_v1_5);
@@ -60,11 +63,23 @@ PG_FUNCTION_INFO_V1(pg_relpages_v1_5);
 PG_FUNCTION_INFO_V1(pg_relpagesbyid_v1_5);
 PG_FUNCTION_INFO_V1(pgstatginindex_v1_5);
 
+PG_FUNCTION_INFO_V1(pgstathashindex_v1_6);
+
 Datum pgstatginindex_internal(Oid relid, FunctionCallInfo fcinfo);
+Datum pgstathashindex_internal(Oid relid, FunctionCallInfo fcinfo);
 
 #define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
 #define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
 #define IS_GIN(r) ((r)->rd_rel->relam == GIN_AM_OID)
+#define IS_HASH(r) ((r)->rd_rel->relam == HASH_AM_OID)
+
+/*
+ * HASH_ALLOCATABLE_PAGE_SZ represents allocatable
+ * space on a empty hash page.
+ */
+#define HASH_ALLOCATABLE_PAGE_SZ \
+		BLCKSZ - \
+		(SizeOfPageHeaderData + sizeof(HashPageOpaqueData))
 
 /* ------------------------------------------------
  * A structure for a whole btree index statistics
@@ -101,7 +116,29 @@ typedef struct GinIndexStat
 	int64		pending_tuples;
 } GinIndexStat;
 
+/* ------------------------------------------------
+ * A structure for a whole HASH index statistics
+ * used by pgstathashindex().
+ * ------------------------------------------------
+ */
+typedef struct HashIndexStat
+{
+	uint32	version;
+	uint32	total_pages;
+	uint32	bucket_pages;
+	uint32	overflow_pages;
+	uint32	bitmap_pages;
+	uint32	zero_pages;
+	uint64	ntuples;
+	uint16	ffactor;
+	uint64	live_items;
+	uint64	dead_items;
+	uint64	free_space;
+} HashIndexStat;
+
 static Datum pgstatindex_impl(Relation rel, FunctionCallInfo fcinfo);
+static void GetHashPageStats(Page page, HashIndexStat *stats);
+
 
 /* ------------------------------------------------------
  * pgstatindex()
@@ -527,3 +564,206 @@ pgstatginindex_internal(Oid relid, FunctionCallInfo fcinfo)
 
 	return (result);
 }
+
+/* ------------------------------------------------------
+ * pgstathashindex()
+ *
+ * Usage: SELECT * FROM pgstathashindex('hashindex');
+ *
+ * Must keep superuser() check, see above.
+ * ------------------------------------------------------
+ */
+Datum
+pgstathashindex(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+
+	if (!superuser())
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 (errmsg("must be superuser to use pgstattuple functions"))));
+
+	PG_RETURN_DATUM(pgstathashindex_internal(relid, fcinfo));
+}
+
+/* No need for superuser checks from v1.5 onwards, see above */
+Datum
+pgstathashindex_v1_6(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+
+	PG_RETURN_DATUM(pgstathashindex_internal(relid, fcinfo));
+}
+
+Datum
+pgstathashindex_internal(Oid relid, FunctionCallInfo fcinfo)
+{
+	BlockNumber	nblocks;
+	BlockNumber	blkno;
+	Relation	rel;
+	HashIndexStat stats = {0};
+	BufferAccessStrategy bstrategy;
+	HeapTuple	tuple;
+	TupleDesc	tupleDesc;
+	Datum		values[11];
+	bool		nulls[11];
+	Datum		result;
+	Buffer		metabuf;
+	HashMetaPage	metap;
+	float4		free_percent;
+	uint64		table_len;
+
+	rel = index_open(relid, AccessShareLock);
+
+	if (!IS_HASH(rel))
+		elog(ERROR, "relation \"%s\" is not a HASH index",
+			 RelationGetRelationName(rel));
+
+	/*
+	 * Reject attempts to read non-local temporary relations; we would be
+	 * likely to get wrong data since we have no visibility into the owning
+	 * session's local buffers.
+	 */
+	if (RELATION_IS_OTHER_TEMP(rel))
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+			   errmsg("cannot access temporary indexes of other sessions")));
+
+	/* Get the current relation length */
+	LockRelationForExtension(rel, ExclusiveLock);
+	nblocks = RelationGetNumberOfBlocks(rel);
+	UnlockRelationForExtension(rel, ExclusiveLock);
+
+	stats.total_pages = nblocks;
+
+	/* prepare access strategy for this index */
+	bstrategy = GetAccessStrategy(BAS_BULKREAD);
+
+	/* Start from blkno 1 as 0th block is metapage */
+	for (blkno = 1; blkno < nblocks; blkno++)
+	{
+		Buffer		buf;
+		Page		page;
+		HashPageOpaque	opaque;
+
+		CHECK_FOR_INTERRUPTS();
+
+		buf = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy);
+		LockBuffer(buf, BUFFER_LOCK_SHARE);
+		page = (Page) BufferGetPage(buf);
+
+		if (PageIsNew(page))
+			stats.zero_pages++;
+		else if (PageGetSpecialSize(page) != MAXALIGN(sizeof(HashPageOpaqueData)))
+			ereport(ERROR,
+					(errcode(ERRCODE_INDEX_CORRUPTED),
+					 errmsg("index \"%s\" contains corrupted page at block %u",
+							RelationGetRelationName(rel),
+							BufferGetBlockNumber(buf)),
+			errhint("Please REINDEX it.")));
+		else
+		{
+			opaque = (HashPageOpaque) PageGetSpecialPointer(page);
+			if (opaque->hasho_flag & LH_BUCKET_PAGE)
+			{
+				stats.bucket_pages++;
+				GetHashPageStats(page, &stats);
+			}
+			else if (opaque->hasho_flag & LH_OVERFLOW_PAGE)
+			{
+				stats.overflow_pages++;
+				GetHashPageStats(page, &stats);
+			}
+			else if (opaque->hasho_flag & LH_BITMAP_PAGE)
+				stats.bitmap_pages++;
+			else
+				ereport(ERROR,
+						(errcode(ERRCODE_INDEX_CORRUPTED),
+					errmsg("unexpected page type 0x%04X in HASH index \"%s\" block %u",
+							opaque->hasho_flag, RelationGetRelationName(rel),
+							BufferGetBlockNumber(buf))));
+		}
+		UnlockReleaseBuffer(buf);
+	}
+
+	/* Read the metapage so we can determine things like ntuples, ffactor etc. */
+	metabuf = _hash_getbuf(rel, HASH_METAPAGE, HASH_READ, LH_META_PAGE);
+	metap = HashPageGetMeta(BufferGetPage(metabuf));
+
+	stats.ntuples = metap->hashm_ntuples;
+	stats.version = metap->hashm_version;
+	stats.ffactor = metap->hashm_ffactor;
+
+	stats.free_space += ((HASH_ALLOCATABLE_PAGE_SZ) * stats.zero_pages);
+
+	/*
+	 * Let us ignore metapage and bitmap page when calculating
+	 * free space percentage for tuples in a table.
+	 */
+	table_len = (stats.total_pages - (stats.bitmap_pages + 1)) *
+									(HASH_ALLOCATABLE_PAGE_SZ);
+
+	if (table_len == 0)
+		free_percent = 0.0;
+	else
+		free_percent = 100.0 * stats.free_space / table_len;
+
+	_hash_relbuf(rel, metabuf);
+
+	index_close(rel, AccessShareLock);
+
+	/*
+	 * Build a tuple descriptor for our result type
+	 */
+	if (get_call_result_type(fcinfo, NULL, &tupleDesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, "return type must be a row type");
+
+	tupleDesc = BlessTupleDesc(tupleDesc);
+
+	MemSet(nulls, 0, sizeof(nulls));
+
+	values[0] = UInt32GetDatum(stats.version);
+	values[1] = UInt32GetDatum(stats.total_pages);
+	values[2] = UInt32GetDatum(stats.bucket_pages);
+	values[3] = UInt32GetDatum(stats.overflow_pages);
+	values[4] = UInt32GetDatum(stats.bitmap_pages);
+	values[5] = UInt32GetDatum(stats.zero_pages);
+	values[6] = UInt64GetDatum(stats.ntuples);
+	values[7] = UInt16GetDatum(stats.ffactor);
+	values[8] = UInt64GetDatum(stats.live_items);
+	values[9] = UInt64GetDatum(stats.dead_items);
+	values[10] = Float4GetDatum(free_percent);
+
+	/*
+	 * Build and return the tuple
+	 */
+	tuple = heap_form_tuple(tupleDesc, values, nulls);
+	result = HeapTupleGetDatum(tuple);
+
+	return (result);
+}
+
+/* -------------------------------------------------
+ * GetHashPageStatis()
+ *
+ * Collect statistics of single hash page
+ * -------------------------------------------------
+ */
+static void
+GetHashPageStats(Page page, HashIndexStat *stats)
+{
+	OffsetNumber maxoff = PageGetMaxOffsetNumber(page);
+	int off;
+
+	/* count live and dead tuples, and free space */
+	for (off = FirstOffsetNumber; off <= maxoff; off++)
+	{
+		ItemId      id = PageGetItemId(page, off);
+
+		if (!ItemIdIsDead(id))
+			stats->live_items++;
+		else
+			stats->dead_items++;
+	}
+	stats->free_space += PageGetExactFreeSpace(page);
+}
diff --git a/contrib/pgstattuple/pgstattuple--1.4.sql b/contrib/pgstattuple/pgstattuple--1.4.sql
index 47377eb..94828bf 100644
--- a/contrib/pgstattuple/pgstattuple--1.4.sql
+++ b/contrib/pgstattuple/pgstattuple--1.4.sql
@@ -35,6 +35,21 @@ RETURNS BIGINT
 AS 'MODULE_PATHNAME', 'pg_relpages'
 LANGUAGE C STRICT PARALLEL SAFE;
 
+CREATE FUNCTION pgstathashindex(IN relname regclass,
+    OUT version INT4,
+    OUT total_pages INT4,
+    OUT bucket_pages INT4,
+    OUT overflow_pages INT4,
+    OUT bitmap_pages INT4,
+    OUT zero_pages INT4,
+    OUT ntuples BIGINT,
+    OUT tuples_per_bucket BIGINT,
+    OUT live_items BIGINT,
+    OUT dead_items BIGINT,
+    OUT free_percent FLOAT4)
+AS 'MODULE_PATHNAME', 'pgstathashindex'
+LANGUAGE C STRICT PARALLEL SAFE;
+
 /* New stuff in 1.1 begins here */
 
 CREATE FUNCTION pgstatginindex(IN relname regclass,
diff --git a/contrib/pgstattuple/pgstattuple--1.5--1.6.sql b/contrib/pgstattuple/pgstattuple--1.5--1.6.sql
new file mode 100644
index 0000000..83eebd8
--- /dev/null
+++ b/contrib/pgstattuple/pgstattuple--1.5--1.6.sql
@@ -0,0 +1,22 @@
+/* contrib/pgstattuple/pgstattuple--1.5--1.6.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pgstattuple UPDATE TO '1.6'" to load this file. \quit
+
+
+CREATE OR REPLACE FUNCTION pgstathashindex(IN relname regclass,
+	OUT version INT4,
+	OUT total_pages INT4,
+	OUT bucket_pages INT4,
+	OUT overflow_pages INT4,
+	OUT bitmap_pages INT4,
+	OUT zero_pages INT4,
+	OUT ntuples BIGINT,
+	OUT tuples_per_bucket BIGINT,
+	OUT live_items BIGINT,
+	OUT dead_items BIGINT,
+	OUT free_percent FLOAT4)
+AS 'MODULE_PATHNAME', 'pgstathashindex_v1_6'
+LANGUAGE C STRICT PARALLEL SAFE;
+
+REVOKE EXECUTE ON FUNCTION pgstathashindex(regclass) FROM PUBLIC;
diff --git a/contrib/pgstattuple/pgstattuple.control b/contrib/pgstattuple/pgstattuple.control
index 6af4075..80d0695 100644
--- a/contrib/pgstattuple/pgstattuple.control
+++ b/contrib/pgstattuple/pgstattuple.control
@@ -1,5 +1,5 @@
 # pgstattuple extension
 comment = 'show tuple-level statistics'
-default_version = '1.5'
+default_version = '1.6'
 module_pathname = '$libdir/pgstattuple'
 relocatable = true
diff --git a/contrib/pgstattuple/sql/pgstattuple.sql b/contrib/pgstattuple/sql/pgstattuple.sql
index d22c9f1..81fd5d6 100644
--- a/contrib/pgstattuple/sql/pgstattuple.sql
+++ b/contrib/pgstattuple/sql/pgstattuple.sql
@@ -47,3 +47,7 @@ select pg_relpages(relname) from pg_class where relname = 'test_pkey';
 create index test_ginidx on test using gin (b);
 
 select * from pgstatginindex('test_ginidx');
+
+create index test_hashidx on test using hash (b);
+
+select * from pgstathashindex('test_hashidx');
diff --git a/doc/src/sgml/pgstattuple.sgml b/doc/src/sgml/pgstattuple.sgml
index d2fa524..6a5c12d 100644
--- a/doc/src/sgml/pgstattuple.sgml
+++ b/doc/src/sgml/pgstattuple.sgml
@@ -355,6 +355,112 @@ pending_tuples | 0
    <varlistentry>
     <term>
      <indexterm>
+      <primary>pgstathashindex</primary>
+     </indexterm>
+     <function>pgstathashindex(regclass) returns record</>
+    </term>
+
+    <listitem>
+     <para>
+      <function>pgstathashindex</function> returns a record showing information
+      about a HASH index.  For example:
+<programlisting>
+test=&gt; select * from pgstathashindex('con_hash_index');
+ version | total_pages | bucket_pages | overflow_pages | bitmap_pages | zero_pages | ntuples | tuples_per_bucket | live_items | dead_items | free_percent 
+---------+-------------+--------------+----------------+--------------+------------+---------+-------------------+------------+------------+--------------
+       2 |        5598 |         4096 |           1500 |            1 |          0 |  686000 |               307 |     686000 |          0 |      69.9245
+</programlisting>
+     </para>
+
+    <para>
+     The output columns are:
+
+    <informaltable>
+     <tgroup cols="3">
+      <thead>
+       <row>
+        <entry>Column</entry>
+        <entry>Type</entry>
+        <entry>Description</entry>
+       </row>
+      </thead>
+
+      <tbody>
+       <row>
+        <entry><structfield>version</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>HASH version number</entry>
+       </row>
+
+       <row>
+        <entry><structfield>total_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>bucket_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of bucket pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>overflow_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of overflow pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>bitmap_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of bitmap pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>zero_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of new or zero pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>ntuples</structfield></entry>
+        <entry><type>bigint</type></entry>
+        <entry>Total number of tuples in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>tuples_per_bucket</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Average number of tuples per bucket</entry>
+       </row>
+
+       <row>
+        <entry><structfield>live_items</structfield></entry>
+        <entry><type>bigint</type></entry>
+        <entry>Total number of alive tuples in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>dead_tuples</structfield></entry>
+        <entry><type>bigint</type></entry>
+        <entry>Total number of dead tuples in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>free_percent</structfield></entry>
+        <entry><type>float</type></entry>
+        <entry>Percentage of free space available in the hash table</entry>
+       </row>
+
+      </tbody>
+     </tgroup>
+    </informaltable>
+    </para>
+    </listitem>
+   </varlistentry>
+   <varlistentry>
+    <term>
+     <indexterm>
       <primary>pg_relpages</primary>
      </indexterm>
      <function>pg_relpages(regclass) returns bigint</>
-- 
1.8.3.1

From 20fc34791fb5836c507964d72a5eb28726801d27 Mon Sep 17 00:00:00 2001
From: Ashutosh <ashu.coek@gmail.com>
Date: Mon, 23 Jan 2017 11:30:36 +0530
Subject: [PATCH] Add pgstathashindex() to pgstattuple extension v4

This allows us to hash index table statistics.
We could have directly used pgstattuple() to see the
hash table statistics but it doesn't include some of
statisctics related to hash indexes like number of
bucket pages, overflow pages, zero pages etc. Moreover,
it can't be used if hash index contains zero page.

The test-case added here needs to be changes when the
WAL patch for hash index gets committed.

Patch by Ashutosh.
---
 contrib/pgstattuple/Makefile                  |   8 +-
 contrib/pgstattuple/expected/pgstattuple.out  |   8 +
 contrib/pgstattuple/pgstatindex.c             | 240 ++++++++++++++++++++++++++
 contrib/pgstattuple/pgstattuple--1.4.sql      |  15 ++
 contrib/pgstattuple/pgstattuple--1.5--1.6.sql |  22 +++
 contrib/pgstattuple/pgstattuple.control       |   2 +-
 contrib/pgstattuple/sql/pgstattuple.sql       |   4 +
 doc/src/sgml/pgstattuple.sgml                 | 106 ++++++++++++
 8 files changed, 400 insertions(+), 5 deletions(-)
 create mode 100644 contrib/pgstattuple/pgstattuple--1.5--1.6.sql

diff --git a/contrib/pgstattuple/Makefile b/contrib/pgstattuple/Makefile
index 294077d..a1601ec 100644
--- a/contrib/pgstattuple/Makefile
+++ b/contrib/pgstattuple/Makefile
@@ -4,10 +4,10 @@ MODULE_big	= pgstattuple
 OBJS		= pgstattuple.o pgstatindex.o pgstatapprox.o $(WIN32RES)
 
 EXTENSION = pgstattuple
-DATA = pgstattuple--1.4.sql pgstattuple--1.4--1.5.sql \
-	pgstattuple--1.3--1.4.sql pgstattuple--1.2--1.3.sql \
-	pgstattuple--1.1--1.2.sql pgstattuple--1.0--1.1.sql \
-	pgstattuple--unpackaged--1.0.sql
+DATA = pgstattuple--1.5--1.6.sql pgstattuple--1.4--1.5.sql \
+	pgstattuple--1.4.sql pgstattuple--1.3--1.4.sql \
+	pgstattuple--1.2--1.3.sql pgstattuple--1.1--1.2.sql	\
+	pgstattuple--1.0--1.1.sql pgstattuple--unpackaged--1.0.sql
 PGFILEDESC = "pgstattuple - tuple-level statistics"
 
 REGRESS = pgstattuple
diff --git a/contrib/pgstattuple/expected/pgstattuple.out b/contrib/pgstattuple/expected/pgstattuple.out
index e920234..26d43c8 100644
--- a/contrib/pgstattuple/expected/pgstattuple.out
+++ b/contrib/pgstattuple/expected/pgstattuple.out
@@ -130,3 +130,11 @@ select * from pgstatginindex('test_ginidx');
        2 |             0 |              0
 (1 row)
 
+create index test_hashidx on test using hash (b);
+WARNING:  hash indexes are not WAL-logged and their use is discouraged
+select * from pgstathashindex('test_hashidx');
+ version | total_pages | bucket_pages | overflow_pages | bitmap_pages | zero_pages | ntuples | tuples_per_bucket | live_items | dead_items | free_percent 
+---------+-------------+--------------+----------------+--------------+------------+---------+-------------------+------------+------------+--------------
+       2 |           6 |            4 |              0 |            1 |          0 |       0 |               307 |          0 |          0 |          100
+(1 row)
+
diff --git a/contrib/pgstattuple/pgstatindex.c b/contrib/pgstattuple/pgstatindex.c
index b406692..bccaac8 100644
--- a/contrib/pgstattuple/pgstatindex.c
+++ b/contrib/pgstattuple/pgstatindex.c
@@ -29,6 +29,7 @@
 
 #include "access/gin_private.h"
 #include "access/heapam.h"
+#include "access/hash.h"
 #include "access/htup_details.h"
 #include "access/nbtree.h"
 #include "catalog/namespace.h"
@@ -36,6 +37,7 @@
 #include "funcapi.h"
 #include "miscadmin.h"
 #include "storage/bufmgr.h"
+#include "storage/lmgr.h"
 #include "utils/builtins.h"
 #include "utils/rel.h"
 #include "utils/varlena.h"
@@ -54,6 +56,7 @@ PG_FUNCTION_INFO_V1(pgstatindexbyid);
 PG_FUNCTION_INFO_V1(pg_relpages);
 PG_FUNCTION_INFO_V1(pg_relpagesbyid);
 PG_FUNCTION_INFO_V1(pgstatginindex);
+PG_FUNCTION_INFO_V1(pgstathashindex);
 
 PG_FUNCTION_INFO_V1(pgstatindex_v1_5);
 PG_FUNCTION_INFO_V1(pgstatindexbyid_v1_5);
@@ -61,11 +64,23 @@ PG_FUNCTION_INFO_V1(pg_relpages_v1_5);
 PG_FUNCTION_INFO_V1(pg_relpagesbyid_v1_5);
 PG_FUNCTION_INFO_V1(pgstatginindex_v1_5);
 
+PG_FUNCTION_INFO_V1(pgstathashindex_v1_6);
+
 Datum pgstatginindex_internal(Oid relid, FunctionCallInfo fcinfo);
+Datum pgstathashindex_internal(Oid relid, FunctionCallInfo fcinfo);
 
 #define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
 #define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
 #define IS_GIN(r) ((r)->rd_rel->relam == GIN_AM_OID)
+#define IS_HASH(r) ((r)->rd_rel->relam == HASH_AM_OID)
+
+/*
+ * HASH_ALLOCATABLE_PAGE_SZ represents allocatable
+ * space on a empty hash page.
+ */
+#define HASH_ALLOCATABLE_PAGE_SZ \
+		BLCKSZ - \
+		(SizeOfPageHeaderData + sizeof(HashPageOpaqueData))
 
 /* ------------------------------------------------
  * A structure for a whole btree index statistics
@@ -102,7 +117,29 @@ typedef struct GinIndexStat
 	int64		pending_tuples;
 } GinIndexStat;
 
+/* ------------------------------------------------
+ * A structure for a whole HASH index statistics
+ * used by pgstathashindex().
+ * ------------------------------------------------
+ */
+typedef struct HashIndexStat
+{
+	uint32	version;
+	uint32	total_pages;
+	uint32	bucket_pages;
+	uint32	overflow_pages;
+	uint32	bitmap_pages;
+	uint32	zero_pages;
+	uint64	ntuples;
+	uint16	ffactor;
+	uint64	live_items;
+	uint64	dead_items;
+	uint64	free_space;
+} HashIndexStat;
+
 static Datum pgstatindex_impl(Relation rel, FunctionCallInfo fcinfo);
+static void GetHashPageStats(Page page, HashIndexStat *stats);
+
 
 /* ------------------------------------------------------
  * pgstatindex()
@@ -528,3 +565,206 @@ pgstatginindex_internal(Oid relid, FunctionCallInfo fcinfo)
 
 	return (result);
 }
+
+/* ------------------------------------------------------
+ * pgstathashindex()
+ *
+ * Usage: SELECT * FROM pgstathashindex('hashindex');
+ *
+ * Must keep superuser() check, see above.
+ * ------------------------------------------------------
+ */
+Datum
+pgstathashindex(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+
+	if (!superuser())
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 (errmsg("must be superuser to use pgstattuple functions"))));
+
+	PG_RETURN_DATUM(pgstathashindex_internal(relid, fcinfo));
+}
+
+/* No need for superuser checks from v1.5 onwards, see above */
+Datum
+pgstathashindex_v1_6(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+
+	PG_RETURN_DATUM(pgstathashindex_internal(relid, fcinfo));
+}
+
+Datum
+pgstathashindex_internal(Oid relid, FunctionCallInfo fcinfo)
+{
+	BlockNumber	nblocks;
+	BlockNumber	blkno;
+	Relation	rel;
+	HashIndexStat stats = {0};
+	BufferAccessStrategy bstrategy;
+	HeapTuple	tuple;
+	TupleDesc	tupleDesc;
+	Datum		values[11];
+	bool		nulls[11];
+	Datum		result;
+	Buffer		metabuf;
+	HashMetaPage	metap;
+	float4		free_percent;
+	uint64		table_len;
+
+	rel = index_open(relid, AccessShareLock);
+
+	if (!IS_HASH(rel))
+		elog(ERROR, "relation \"%s\" is not a HASH index",
+			 RelationGetRelationName(rel));
+
+	/*
+	 * Reject attempts to read non-local temporary relations; we would be
+	 * likely to get wrong data since we have no visibility into the owning
+	 * session's local buffers.
+	 */
+	if (RELATION_IS_OTHER_TEMP(rel))
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+			   errmsg("cannot access temporary indexes of other sessions")));
+
+	/* Get the current relation length */
+	LockRelationForExtension(rel, ExclusiveLock);
+	nblocks = RelationGetNumberOfBlocks(rel);
+	UnlockRelationForExtension(rel, ExclusiveLock);
+
+	stats.total_pages = nblocks;
+
+	/* prepare access strategy for this index */
+	bstrategy = GetAccessStrategy(BAS_BULKREAD);
+
+	/* Start from blkno 1 as 0th block is metapage */
+	for (blkno = 1; blkno < nblocks; blkno++)
+	{
+		Buffer		buf;
+		Page		page;
+		HashPageOpaque	opaque;
+
+		CHECK_FOR_INTERRUPTS();
+
+		buf = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy);
+		LockBuffer(buf, BUFFER_LOCK_SHARE);
+		page = (Page) BufferGetPage(buf);
+
+		if (PageIsNew(page))
+			stats.zero_pages++;
+		else if (PageGetSpecialSize(page) != MAXALIGN(sizeof(HashPageOpaqueData)))
+			ereport(ERROR,
+					(errcode(ERRCODE_INDEX_CORRUPTED),
+					 errmsg("index \"%s\" contains corrupted page at block %u",
+							RelationGetRelationName(rel),
+							BufferGetBlockNumber(buf)),
+			errhint("Please REINDEX it.")));
+		else
+		{
+			opaque = (HashPageOpaque) PageGetSpecialPointer(page);
+			if (opaque->hasho_flag & LH_BUCKET_PAGE)
+			{
+				stats.bucket_pages++;
+				GetHashPageStats(page, &stats);
+			}
+			else if (opaque->hasho_flag & LH_OVERFLOW_PAGE)
+			{
+				stats.overflow_pages++;
+				GetHashPageStats(page, &stats);
+			}
+			else if (opaque->hasho_flag & LH_BITMAP_PAGE)
+				stats.bitmap_pages++;
+			else
+				ereport(ERROR,
+						(errcode(ERRCODE_INDEX_CORRUPTED),
+					errmsg("unexpected page type 0x%04X in HASH index \"%s\" block %u",
+							opaque->hasho_flag, RelationGetRelationName(rel),
+							BufferGetBlockNumber(buf))));
+		}
+		UnlockReleaseBuffer(buf);
+	}
+
+	/* Read the metapage so we can determine things like ntuples, ffactor etc. */
+	metabuf = _hash_getbuf(rel, HASH_METAPAGE, HASH_READ, LH_META_PAGE);
+	metap = HashPageGetMeta(BufferGetPage(metabuf));
+
+	stats.ntuples = metap->hashm_ntuples;
+	stats.version = metap->hashm_version;
+	stats.ffactor = metap->hashm_ffactor;
+
+	stats.free_space += ((HASH_ALLOCATABLE_PAGE_SZ) * stats.zero_pages);
+
+	/*
+	 * Let us ignore metapage and bitmap page when calculating
+	 * free space percentage for tuples in a table.
+	 */
+	table_len = (stats.total_pages - (stats.bitmap_pages + 1)) *
+									(HASH_ALLOCATABLE_PAGE_SZ);
+
+	if (table_len == 0)
+		free_percent = 0.0;
+	else
+		free_percent = 100.0 * stats.free_space / table_len;
+
+	_hash_relbuf(rel, metabuf);
+
+	index_close(rel, AccessShareLock);
+
+	/*
+	 * Build a tuple descriptor for our result type
+	 */
+	if (get_call_result_type(fcinfo, NULL, &tupleDesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, "return type must be a row type");
+
+	tupleDesc = BlessTupleDesc(tupleDesc);
+
+	MemSet(nulls, 0, sizeof(nulls));
+
+	values[0] = UInt32GetDatum(stats.version);
+	values[1] = UInt32GetDatum(stats.total_pages);
+	values[2] = UInt32GetDatum(stats.bucket_pages);
+	values[3] = UInt32GetDatum(stats.overflow_pages);
+	values[4] = UInt32GetDatum(stats.bitmap_pages);
+	values[5] = UInt32GetDatum(stats.zero_pages);
+	values[6] = UInt64GetDatum(stats.ntuples);
+	values[7] = UInt16GetDatum(stats.ffactor);
+	values[8] = UInt64GetDatum(stats.live_items);
+	values[9] = UInt64GetDatum(stats.dead_items);
+	values[10] = Float4GetDatum(free_percent);
+
+	/*
+	 * Build and return the tuple
+	 */
+	tuple = heap_form_tuple(tupleDesc, values, nulls);
+	result = HeapTupleGetDatum(tuple);
+
+	return (result);
+}
+
+/* -------------------------------------------------
+ * GetHashPageStatis()
+ *
+ * Collect statistics of single hash page
+ * -------------------------------------------------
+ */
+static void
+GetHashPageStats(Page page, HashIndexStat *stats)
+{
+	OffsetNumber maxoff = PageGetMaxOffsetNumber(page);
+	int off;
+
+	/* count live and dead tuples, and free space */
+	for (off = FirstOffsetNumber; off <= maxoff; off++)
+	{
+		ItemId      id = PageGetItemId(page, off);
+
+		if (!ItemIdIsDead(id))
+			stats->live_items++;
+		else
+			stats->dead_items++;
+	}
+	stats->free_space += PageGetExactFreeSpace(page);
+}
diff --git a/contrib/pgstattuple/pgstattuple--1.4.sql b/contrib/pgstattuple/pgstattuple--1.4.sql
index 47377eb..94828bf 100644
--- a/contrib/pgstattuple/pgstattuple--1.4.sql
+++ b/contrib/pgstattuple/pgstattuple--1.4.sql
@@ -35,6 +35,21 @@ RETURNS BIGINT
 AS 'MODULE_PATHNAME', 'pg_relpages'
 LANGUAGE C STRICT PARALLEL SAFE;
 
+CREATE FUNCTION pgstathashindex(IN relname regclass,
+    OUT version INT4,
+    OUT total_pages INT4,
+    OUT bucket_pages INT4,
+    OUT overflow_pages INT4,
+    OUT bitmap_pages INT4,
+    OUT zero_pages INT4,
+    OUT ntuples BIGINT,
+    OUT tuples_per_bucket BIGINT,
+    OUT live_items BIGINT,
+    OUT dead_items BIGINT,
+    OUT free_percent FLOAT4)
+AS 'MODULE_PATHNAME', 'pgstathashindex'
+LANGUAGE C STRICT PARALLEL SAFE;
+
 /* New stuff in 1.1 begins here */
 
 CREATE FUNCTION pgstatginindex(IN relname regclass,
diff --git a/contrib/pgstattuple/pgstattuple--1.5--1.6.sql b/contrib/pgstattuple/pgstattuple--1.5--1.6.sql
new file mode 100644
index 0000000..83eebd8
--- /dev/null
+++ b/contrib/pgstattuple/pgstattuple--1.5--1.6.sql
@@ -0,0 +1,22 @@
+/* contrib/pgstattuple/pgstattuple--1.5--1.6.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pgstattuple UPDATE TO '1.6'" to load this file. \quit
+
+
+CREATE OR REPLACE FUNCTION pgstathashindex(IN relname regclass,
+	OUT version INT4,
+	OUT total_pages INT4,
+	OUT bucket_pages INT4,
+	OUT overflow_pages INT4,
+	OUT bitmap_pages INT4,
+	OUT zero_pages INT4,
+	OUT ntuples BIGINT,
+	OUT tuples_per_bucket BIGINT,
+	OUT live_items BIGINT,
+	OUT dead_items BIGINT,
+	OUT free_percent FLOAT4)
+AS 'MODULE_PATHNAME', 'pgstathashindex_v1_6'
+LANGUAGE C STRICT PARALLEL SAFE;
+
+REVOKE EXECUTE ON FUNCTION pgstathashindex(regclass) FROM PUBLIC;
diff --git a/contrib/pgstattuple/pgstattuple.control b/contrib/pgstattuple/pgstattuple.control
index 6af4075..80d0695 100644
--- a/contrib/pgstattuple/pgstattuple.control
+++ b/contrib/pgstattuple/pgstattuple.control
@@ -1,5 +1,5 @@
 # pgstattuple extension
 comment = 'show tuple-level statistics'
-default_version = '1.5'
+default_version = '1.6'
 module_pathname = '$libdir/pgstattuple'
 relocatable = true
diff --git a/contrib/pgstattuple/sql/pgstattuple.sql b/contrib/pgstattuple/sql/pgstattuple.sql
index d22c9f1..81fd5d6 100644
--- a/contrib/pgstattuple/sql/pgstattuple.sql
+++ b/contrib/pgstattuple/sql/pgstattuple.sql
@@ -47,3 +47,7 @@ select pg_relpages(relname) from pg_class where relname = 'test_pkey';
 create index test_ginidx on test using gin (b);
 
 select * from pgstatginindex('test_ginidx');
+
+create index test_hashidx on test using hash (b);
+
+select * from pgstathashindex('test_hashidx');
diff --git a/doc/src/sgml/pgstattuple.sgml b/doc/src/sgml/pgstattuple.sgml
index d2fa524..6a5c12d 100644
--- a/doc/src/sgml/pgstattuple.sgml
+++ b/doc/src/sgml/pgstattuple.sgml
@@ -355,6 +355,112 @@ pending_tuples | 0
    <varlistentry>
     <term>
      <indexterm>
+      <primary>pgstathashindex</primary>
+     </indexterm>
+     <function>pgstathashindex(regclass) returns record</>
+    </term>
+
+    <listitem>
+     <para>
+      <function>pgstathashindex</function> returns a record showing information
+      about a HASH index.  For example:
+<programlisting>
+test=&gt; select * from pgstathashindex('con_hash_index');
+ version | total_pages | bucket_pages | overflow_pages | bitmap_pages | zero_pages | ntuples | tuples_per_bucket | live_items | dead_items | free_percent 
+---------+-------------+--------------+----------------+--------------+------------+---------+-------------------+------------+------------+--------------
+       2 |        5598 |         4096 |           1500 |            1 |          0 |  686000 |               307 |     686000 |          0 |      69.9245
+</programlisting>
+     </para>
+
+    <para>
+     The output columns are:
+
+    <informaltable>
+     <tgroup cols="3">
+      <thead>
+       <row>
+        <entry>Column</entry>
+        <entry>Type</entry>
+        <entry>Description</entry>
+       </row>
+      </thead>
+
+      <tbody>
+       <row>
+        <entry><structfield>version</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>HASH version number</entry>
+       </row>
+
+       <row>
+        <entry><structfield>total_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>bucket_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of bucket pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>overflow_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of overflow pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>bitmap_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of bitmap pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>zero_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of new or zero pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>ntuples</structfield></entry>
+        <entry><type>bigint</type></entry>
+        <entry>Total number of tuples in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>tuples_per_bucket</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Average number of tuples per bucket</entry>
+       </row>
+
+       <row>
+        <entry><structfield>live_items</structfield></entry>
+        <entry><type>bigint</type></entry>
+        <entry>Total number of alive tuples in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>dead_tuples</structfield></entry>
+        <entry><type>bigint</type></entry>
+        <entry>Total number of dead tuples in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>free_percent</structfield></entry>
+        <entry><type>float</type></entry>
+        <entry>Percentage of free space available in the hash table</entry>
+       </row>
+
+      </tbody>
+     </tgroup>
+    </informaltable>
+    </para>
+    </listitem>
+   </varlistentry>
+   <varlistentry>
+    <term>
+     <indexterm>
       <primary>pg_relpages</primary>
      </indexterm>
      <function>pg_relpages(regclass) returns bigint</>
-- 
2.10.2.windows.1

From eff09179a580f634cf12ae77b21aa6b7856c9a50 Mon Sep 17 00:00:00 2001
From: ashu <ashu@localhost.localdomain>
Date: Tue, 24 Jan 2017 09:42:07 +0530
Subject: [PATCH] Add pgstathashindex() to pgstattuple extension v5

This allows us to hash index table statistics.
We could have directly used pgstattuple() to see the
hash table statistics but it doesn't include some of
statisctics related to hash indexes like number of
bucket pages, overflow pages, zero pages etc. Moreover,
it can't be used if hash index contains zero page.

The test-case added here needs to be changes when the
WAL patch for hash index gets committed.
---
 contrib/pgstattuple/Makefile                  |   8 +-
 contrib/pgstattuple/expected/pgstattuple.out  |   8 +
 contrib/pgstattuple/pgstatindex.c             | 238 ++++++++++++++++++++++++++
 contrib/pgstattuple/pgstattuple--1.4.sql      |  15 ++
 contrib/pgstattuple/pgstattuple--1.5--1.6.sql |  22 +++
 contrib/pgstattuple/pgstattuple.control       |   2 +-
 contrib/pgstattuple/sql/pgstattuple.sql       |   4 +
 doc/src/sgml/pgstattuple.sgml                 | 106 ++++++++++++
 8 files changed, 398 insertions(+), 5 deletions(-)
 create mode 100644 contrib/pgstattuple/pgstattuple--1.5--1.6.sql

diff --git a/contrib/pgstattuple/Makefile b/contrib/pgstattuple/Makefile
index 294077d..a1601ec 100644
--- a/contrib/pgstattuple/Makefile
+++ b/contrib/pgstattuple/Makefile
@@ -4,10 +4,10 @@ MODULE_big	= pgstattuple
 OBJS		= pgstattuple.o pgstatindex.o pgstatapprox.o $(WIN32RES)
 
 EXTENSION = pgstattuple
-DATA = pgstattuple--1.4.sql pgstattuple--1.4--1.5.sql \
-	pgstattuple--1.3--1.4.sql pgstattuple--1.2--1.3.sql \
-	pgstattuple--1.1--1.2.sql pgstattuple--1.0--1.1.sql \
-	pgstattuple--unpackaged--1.0.sql
+DATA = pgstattuple--1.5--1.6.sql pgstattuple--1.4--1.5.sql \
+	pgstattuple--1.4.sql pgstattuple--1.3--1.4.sql \
+	pgstattuple--1.2--1.3.sql pgstattuple--1.1--1.2.sql	\
+	pgstattuple--1.0--1.1.sql pgstattuple--unpackaged--1.0.sql
 PGFILEDESC = "pgstattuple - tuple-level statistics"
 
 REGRESS = pgstattuple
diff --git a/contrib/pgstattuple/expected/pgstattuple.out b/contrib/pgstattuple/expected/pgstattuple.out
index e920234..26d43c8 100644
--- a/contrib/pgstattuple/expected/pgstattuple.out
+++ b/contrib/pgstattuple/expected/pgstattuple.out
@@ -130,3 +130,11 @@ select * from pgstatginindex('test_ginidx');
        2 |             0 |              0
 (1 row)
 
+create index test_hashidx on test using hash (b);
+WARNING:  hash indexes are not WAL-logged and their use is discouraged
+select * from pgstathashindex('test_hashidx');
+ version | total_pages | bucket_pages | overflow_pages | bitmap_pages | zero_pages | ntuples | tuples_per_bucket | live_items | dead_items | free_percent 
+---------+-------------+--------------+----------------+--------------+------------+---------+-------------------+------------+------------+--------------
+       2 |           6 |            4 |              0 |            1 |          0 |       0 |               307 |          0 |          0 |          100
+(1 row)
+
diff --git a/contrib/pgstattuple/pgstatindex.c b/contrib/pgstattuple/pgstatindex.c
index b406692..8b621c6 100644
--- a/contrib/pgstattuple/pgstatindex.c
+++ b/contrib/pgstattuple/pgstatindex.c
@@ -29,6 +29,7 @@
 
 #include "access/gin_private.h"
 #include "access/heapam.h"
+#include "access/hash.h"
 #include "access/htup_details.h"
 #include "access/nbtree.h"
 #include "catalog/namespace.h"
@@ -36,6 +37,7 @@
 #include "funcapi.h"
 #include "miscadmin.h"
 #include "storage/bufmgr.h"
+#include "storage/lmgr.h"
 #include "utils/builtins.h"
 #include "utils/rel.h"
 #include "utils/varlena.h"
@@ -54,6 +56,7 @@ PG_FUNCTION_INFO_V1(pgstatindexbyid);
 PG_FUNCTION_INFO_V1(pg_relpages);
 PG_FUNCTION_INFO_V1(pg_relpagesbyid);
 PG_FUNCTION_INFO_V1(pgstatginindex);
+PG_FUNCTION_INFO_V1(pgstathashindex);
 
 PG_FUNCTION_INFO_V1(pgstatindex_v1_5);
 PG_FUNCTION_INFO_V1(pgstatindexbyid_v1_5);
@@ -61,11 +64,23 @@ PG_FUNCTION_INFO_V1(pg_relpages_v1_5);
 PG_FUNCTION_INFO_V1(pg_relpagesbyid_v1_5);
 PG_FUNCTION_INFO_V1(pgstatginindex_v1_5);
 
+PG_FUNCTION_INFO_V1(pgstathashindex_v1_6);
+
 Datum pgstatginindex_internal(Oid relid, FunctionCallInfo fcinfo);
+Datum pgstathashindex_internal(Oid relid, FunctionCallInfo fcinfo);
 
 #define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
 #define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
 #define IS_GIN(r) ((r)->rd_rel->relam == GIN_AM_OID)
+#define IS_HASH(r) ((r)->rd_rel->relam == HASH_AM_OID)
+
+/*
+ * HASH_ALLOCATABLE_PAGE_SZ represents allocatable
+ * space on a empty hash page.
+ */
+#define HASH_ALLOCATABLE_PAGE_SZ \
+		BLCKSZ - \
+		(SizeOfPageHeaderData + sizeof(HashPageOpaqueData))
 
 /* ------------------------------------------------
  * A structure for a whole btree index statistics
@@ -102,7 +117,29 @@ typedef struct GinIndexStat
 	int64		pending_tuples;
 } GinIndexStat;
 
+/* ------------------------------------------------
+ * A structure for a whole HASH index statistics
+ * used by pgstathashindex().
+ * ------------------------------------------------
+ */
+typedef struct HashIndexStat
+{
+	uint32	version;
+	uint32	total_pages;
+	uint32	bucket_pages;
+	uint32	overflow_pages;
+	uint32	bitmap_pages;
+	uint32	zero_pages;
+	uint64	ntuples;
+	uint16	ffactor;
+	uint64	live_items;
+	uint64	dead_items;
+	uint64	free_space;
+} HashIndexStat;
+
 static Datum pgstatindex_impl(Relation rel, FunctionCallInfo fcinfo);
+static void GetHashPageStats(Page page, HashIndexStat *stats);
+
 
 /* ------------------------------------------------------
  * pgstatindex()
@@ -528,3 +565,204 @@ pgstatginindex_internal(Oid relid, FunctionCallInfo fcinfo)
 
 	return (result);
 }
+
+/* ------------------------------------------------------
+ * pgstathashindex()
+ *
+ * Usage: SELECT * FROM pgstathashindex('hashindex');
+ *
+ * Must keep superuser() check, see above.
+ * ------------------------------------------------------
+ */
+Datum
+pgstathashindex(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+
+	if (!superuser())
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 (errmsg("must be superuser to use pgstattuple functions"))));
+
+	PG_RETURN_DATUM(pgstathashindex_internal(relid, fcinfo));
+}
+
+/* No need for superuser checks from v1.5 onwards, see above */
+Datum
+pgstathashindex_v1_6(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+
+	PG_RETURN_DATUM(pgstathashindex_internal(relid, fcinfo));
+}
+
+Datum
+pgstathashindex_internal(Oid relid, FunctionCallInfo fcinfo)
+{
+	BlockNumber	nblocks;
+	BlockNumber	blkno;
+	Relation	rel;
+	HashIndexStat stats = {0};
+	BufferAccessStrategy bstrategy;
+	HeapTuple	tuple;
+	TupleDesc	tupleDesc;
+	Datum		values[11];
+	bool		nulls[11];
+	Datum		result;
+	Buffer		metabuf;
+	HashMetaPage	metap;
+	float4		free_percent;
+	uint64		table_len;
+
+	rel = index_open(relid, AccessShareLock);
+
+	if (!IS_HASH(rel))
+		elog(ERROR, "relation \"%s\" is not a HASH index",
+			 RelationGetRelationName(rel));
+
+	/*
+	 * Reject attempts to read non-local temporary relations; we would be
+	 * likely to get wrong data since we have no visibility into the owning
+	 * session's local buffers.
+	 */
+	if (RELATION_IS_OTHER_TEMP(rel))
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+			   errmsg("cannot access temporary indexes of other sessions")));
+
+	/* Get the current relation length */
+	nblocks = RelationGetNumberOfBlocks(rel);
+
+	stats.total_pages = nblocks;
+
+	/* prepare access strategy for this index */
+	bstrategy = GetAccessStrategy(BAS_BULKREAD);
+
+	/* Start from blkno 1 as 0th block is metapage */
+	for (blkno = 1; blkno < nblocks; blkno++)
+	{
+		Buffer		buf;
+		Page		page;
+		HashPageOpaque	opaque;
+
+		CHECK_FOR_INTERRUPTS();
+
+		buf = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy);
+		LockBuffer(buf, BUFFER_LOCK_SHARE);
+		page = (Page) BufferGetPage(buf);
+
+		if (PageIsNew(page))
+			stats.zero_pages++;
+		else if (PageGetSpecialSize(page) != MAXALIGN(sizeof(HashPageOpaqueData)))
+			ereport(ERROR,
+					(errcode(ERRCODE_INDEX_CORRUPTED),
+					 errmsg("index \"%s\" contains corrupted page at block %u",
+							RelationGetRelationName(rel),
+							BufferGetBlockNumber(buf)),
+			errhint("Please REINDEX it.")));
+		else
+		{
+			opaque = (HashPageOpaque) PageGetSpecialPointer(page);
+			if (opaque->hasho_flag & LH_BUCKET_PAGE)
+			{
+				stats.bucket_pages++;
+				GetHashPageStats(page, &stats);
+			}
+			else if (opaque->hasho_flag & LH_OVERFLOW_PAGE)
+			{
+				stats.overflow_pages++;
+				GetHashPageStats(page, &stats);
+			}
+			else if (opaque->hasho_flag & LH_BITMAP_PAGE)
+				stats.bitmap_pages++;
+			else
+				ereport(ERROR,
+						(errcode(ERRCODE_INDEX_CORRUPTED),
+					errmsg("unexpected page type 0x%04X in HASH index \"%s\" block %u",
+							opaque->hasho_flag, RelationGetRelationName(rel),
+							BufferGetBlockNumber(buf))));
+		}
+		UnlockReleaseBuffer(buf);
+	}
+
+	/* Read the metapage so we can determine things like ntuples, ffactor etc. */
+	metabuf = _hash_getbuf(rel, HASH_METAPAGE, HASH_READ, LH_META_PAGE);
+	metap = HashPageGetMeta(BufferGetPage(metabuf));
+
+	stats.ntuples = metap->hashm_ntuples;
+	stats.version = metap->hashm_version;
+	stats.ffactor = metap->hashm_ffactor;
+
+	stats.free_space += ((HASH_ALLOCATABLE_PAGE_SZ) * stats.zero_pages);
+
+	/*
+	 * Let us ignore metapage and bitmap page when calculating
+	 * free space percentage for tuples in a table.
+	 */
+	table_len = (stats.total_pages - (stats.bitmap_pages + 1)) *
+									(HASH_ALLOCATABLE_PAGE_SZ);
+
+	if (table_len == 0)
+		free_percent = 0.0;
+	else
+		free_percent = 100.0 * stats.free_space / table_len;
+
+	_hash_relbuf(rel, metabuf);
+
+	index_close(rel, AccessShareLock);
+
+	/*
+	 * Build a tuple descriptor for our result type
+	 */
+	if (get_call_result_type(fcinfo, NULL, &tupleDesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, "return type must be a row type");
+
+	tupleDesc = BlessTupleDesc(tupleDesc);
+
+	MemSet(nulls, 0, sizeof(nulls));
+
+	values[0] = UInt32GetDatum(stats.version);
+	values[1] = UInt32GetDatum(stats.total_pages);
+	values[2] = UInt32GetDatum(stats.bucket_pages);
+	values[3] = UInt32GetDatum(stats.overflow_pages);
+	values[4] = UInt32GetDatum(stats.bitmap_pages);
+	values[5] = UInt32GetDatum(stats.zero_pages);
+	values[6] = UInt64GetDatum(stats.ntuples);
+	values[7] = UInt16GetDatum(stats.ffactor);
+	values[8] = UInt64GetDatum(stats.live_items);
+	values[9] = UInt64GetDatum(stats.dead_items);
+	values[10] = Float4GetDatum(free_percent);
+
+	/*
+	 * Build and return the tuple
+	 */
+	tuple = heap_form_tuple(tupleDesc, values, nulls);
+	result = HeapTupleGetDatum(tuple);
+
+	return (result);
+}
+
+/* -------------------------------------------------
+ * GetHashPageStatis()
+ *
+ * Collect statistics of single hash page
+ * -------------------------------------------------
+ */
+static void
+GetHashPageStats(Page page, HashIndexStat *stats)
+{
+	OffsetNumber maxoff = PageGetMaxOffsetNumber(page);
+	int off;
+
+	/* count live and dead tuples, and free space */
+	for (off = FirstOffsetNumber; off <= maxoff; off++)
+	{
+		ItemId      id = PageGetItemId(page, off);
+
+		if (!ItemIdIsDead(id))
+			stats->live_items++;
+		else
+			stats->dead_items++;
+	}
+	stats->free_space += PageGetExactFreeSpace(page);
+}
diff --git a/contrib/pgstattuple/pgstattuple--1.4.sql b/contrib/pgstattuple/pgstattuple--1.4.sql
index 47377eb..94828bf 100644
--- a/contrib/pgstattuple/pgstattuple--1.4.sql
+++ b/contrib/pgstattuple/pgstattuple--1.4.sql
@@ -35,6 +35,21 @@ RETURNS BIGINT
 AS 'MODULE_PATHNAME', 'pg_relpages'
 LANGUAGE C STRICT PARALLEL SAFE;
 
+CREATE FUNCTION pgstathashindex(IN relname regclass,
+    OUT version INT4,
+    OUT total_pages INT4,
+    OUT bucket_pages INT4,
+    OUT overflow_pages INT4,
+    OUT bitmap_pages INT4,
+    OUT zero_pages INT4,
+    OUT ntuples BIGINT,
+    OUT tuples_per_bucket BIGINT,
+    OUT live_items BIGINT,
+    OUT dead_items BIGINT,
+    OUT free_percent FLOAT4)
+AS 'MODULE_PATHNAME', 'pgstathashindex'
+LANGUAGE C STRICT PARALLEL SAFE;
+
 /* New stuff in 1.1 begins here */
 
 CREATE FUNCTION pgstatginindex(IN relname regclass,
diff --git a/contrib/pgstattuple/pgstattuple--1.5--1.6.sql b/contrib/pgstattuple/pgstattuple--1.5--1.6.sql
new file mode 100644
index 0000000..83eebd8
--- /dev/null
+++ b/contrib/pgstattuple/pgstattuple--1.5--1.6.sql
@@ -0,0 +1,22 @@
+/* contrib/pgstattuple/pgstattuple--1.5--1.6.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pgstattuple UPDATE TO '1.6'" to load this file. \quit
+
+
+CREATE OR REPLACE FUNCTION pgstathashindex(IN relname regclass,
+	OUT version INT4,
+	OUT total_pages INT4,
+	OUT bucket_pages INT4,
+	OUT overflow_pages INT4,
+	OUT bitmap_pages INT4,
+	OUT zero_pages INT4,
+	OUT ntuples BIGINT,
+	OUT tuples_per_bucket BIGINT,
+	OUT live_items BIGINT,
+	OUT dead_items BIGINT,
+	OUT free_percent FLOAT4)
+AS 'MODULE_PATHNAME', 'pgstathashindex_v1_6'
+LANGUAGE C STRICT PARALLEL SAFE;
+
+REVOKE EXECUTE ON FUNCTION pgstathashindex(regclass) FROM PUBLIC;
diff --git a/contrib/pgstattuple/pgstattuple.control b/contrib/pgstattuple/pgstattuple.control
index 6af4075..80d0695 100644
--- a/contrib/pgstattuple/pgstattuple.control
+++ b/contrib/pgstattuple/pgstattuple.control
@@ -1,5 +1,5 @@
 # pgstattuple extension
 comment = 'show tuple-level statistics'
-default_version = '1.5'
+default_version = '1.6'
 module_pathname = '$libdir/pgstattuple'
 relocatable = true
diff --git a/contrib/pgstattuple/sql/pgstattuple.sql b/contrib/pgstattuple/sql/pgstattuple.sql
index d22c9f1..81fd5d6 100644
--- a/contrib/pgstattuple/sql/pgstattuple.sql
+++ b/contrib/pgstattuple/sql/pgstattuple.sql
@@ -47,3 +47,7 @@ select pg_relpages(relname) from pg_class where relname = 'test_pkey';
 create index test_ginidx on test using gin (b);
 
 select * from pgstatginindex('test_ginidx');
+
+create index test_hashidx on test using hash (b);
+
+select * from pgstathashindex('test_hashidx');
diff --git a/doc/src/sgml/pgstattuple.sgml b/doc/src/sgml/pgstattuple.sgml
index d2fa524..6a5c12d 100644
--- a/doc/src/sgml/pgstattuple.sgml
+++ b/doc/src/sgml/pgstattuple.sgml
@@ -355,6 +355,112 @@ pending_tuples | 0
    <varlistentry>
     <term>
      <indexterm>
+      <primary>pgstathashindex</primary>
+     </indexterm>
+     <function>pgstathashindex(regclass) returns record</>
+    </term>
+
+    <listitem>
+     <para>
+      <function>pgstathashindex</function> returns a record showing information
+      about a HASH index.  For example:
+<programlisting>
+test=&gt; select * from pgstathashindex('con_hash_index');
+ version | total_pages | bucket_pages | overflow_pages | bitmap_pages | zero_pages | ntuples | tuples_per_bucket | live_items | dead_items | free_percent 
+---------+-------------+--------------+----------------+--------------+------------+---------+-------------------+------------+------------+--------------
+       2 |        5598 |         4096 |           1500 |            1 |          0 |  686000 |               307 |     686000 |          0 |      69.9245
+</programlisting>
+     </para>
+
+    <para>
+     The output columns are:
+
+    <informaltable>
+     <tgroup cols="3">
+      <thead>
+       <row>
+        <entry>Column</entry>
+        <entry>Type</entry>
+        <entry>Description</entry>
+       </row>
+      </thead>
+
+      <tbody>
+       <row>
+        <entry><structfield>version</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>HASH version number</entry>
+       </row>
+
+       <row>
+        <entry><structfield>total_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>bucket_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of bucket pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>overflow_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of overflow pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>bitmap_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of bitmap pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>zero_pages</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Total number of new or zero pages in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>ntuples</structfield></entry>
+        <entry><type>bigint</type></entry>
+        <entry>Total number of tuples in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>tuples_per_bucket</structfield></entry>
+        <entry><type>integer</type></entry>
+        <entry>Average number of tuples per bucket</entry>
+       </row>
+
+       <row>
+        <entry><structfield>live_items</structfield></entry>
+        <entry><type>bigint</type></entry>
+        <entry>Total number of alive tuples in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>dead_tuples</structfield></entry>
+        <entry><type>bigint</type></entry>
+        <entry>Total number of dead tuples in the hash table</entry>
+       </row>
+
+       <row>
+        <entry><structfield>free_percent</structfield></entry>
+        <entry><type>float</type></entry>
+        <entry>Percentage of free space available in the hash table</entry>
+       </row>
+
+      </tbody>
+     </tgroup>
+    </informaltable>
+    </para>
+    </listitem>
+   </varlistentry>
+   <varlistentry>
+    <term>
+     <indexterm>
       <primary>pg_relpages</primary>
      </indexterm>
      <function>pg_relpages(regclass) returns bigint</>
-- 
1.8.3.1

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

diff --git a/contrib/pgstattuple/expected/pgstattuple.out b/contrib/pgstattuple/expected/pgstattuple.out
index 86a7c78..c7c1732 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 
----------+--------------+----------------+--------------+------------+------------+------------+--------------
-       3 |            4 |              0 |            1 |          0 |          0 |          0 |          100
+ version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items | free_percent 
+---------+--------------+----------------+--------------+--------------+------------+------------+--------------
+       3 |            4 |              0 |            1 |            0 |          0 |          0 |          100
 (1 row)
 
 -- these should error with the wrong type
diff --git a/contrib/pgstattuple/pgstatindex.c b/contrib/pgstattuple/pgstatindex.c
index d448e9e..15aedec 100644
--- a/contrib/pgstattuple/pgstatindex.c
+++ b/contrib/pgstattuple/pgstatindex.c
@@ -119,7 +119,7 @@ typedef struct HashIndexStat
 	BlockNumber	bucket_pages;
 	BlockNumber overflow_pages;
 	BlockNumber bitmap_pages;
-	BlockNumber zero_pages;
+	BlockNumber unused_pages;
 
 	int64	live_items;
 	int64	dead_items;
@@ -634,7 +634,6 @@ pgstathashindex(PG_FUNCTION_ARGS)
 	{
 		Buffer		buf;
 		Page		page;
-		HashPageOpaque	opaque;
 
 		CHECK_FOR_INTERRUPTS();
 
@@ -644,7 +643,7 @@ pgstathashindex(PG_FUNCTION_ARGS)
 		page = (Page) BufferGetPage(buf);
 
 		if (PageIsNew(page))
-			stats.zero_pages++;
+			stats.unused_pages++;
 		else if (PageGetSpecialSize(page) !=
 				 MAXALIGN(sizeof(HashPageOpaqueData)))
 			ereport(ERROR,
@@ -654,19 +653,26 @@ pgstathashindex(PG_FUNCTION_ARGS)
 							BufferGetBlockNumber(buf))));
 		else
 		{
+			HashPageOpaque	opaque;
+			int		pagetype;
+
 			opaque = (HashPageOpaque) PageGetSpecialPointer(page);
-			if (opaque->hasho_flag & LH_BUCKET_PAGE)
+			pagetype = opaque->hasho_flag & LH_PAGE_TYPE;
+
+			if (pagetype == LH_BUCKET_PAGE)
 			{
 				stats.bucket_pages++;
 				GetHashPageStats(page, &stats);
 			}
-			else if (opaque->hasho_flag & LH_OVERFLOW_PAGE)
+			else if (pagetype == LH_OVERFLOW_PAGE)
 			{
 				stats.overflow_pages++;
 				GetHashPageStats(page, &stats);
 			}
-			else if (opaque->hasho_flag & LH_BITMAP_PAGE)
+			else if (pagetype == LH_BITMAP_PAGE)
 				stats.bitmap_pages++;
+			else if (pagetype == LH_UNUSED_PAGE)
+				stats.unused_pages++;
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_INDEX_CORRUPTED),
@@ -680,8 +686,8 @@ 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 unused pages as free space. */
+	stats.free_space += stats.unused_pages * stats.space_per_page;
 
 	/*
 	 * Total space available for tuples excludes the metapage and the bitmap
@@ -710,7 +716,7 @@ pgstathashindex(PG_FUNCTION_ARGS)
 	values[1] = Int64GetDatum((int64) stats.bucket_pages);
 	values[2] = Int64GetDatum((int64) stats.overflow_pages);
 	values[3] = Int64GetDatum((int64) stats.bitmap_pages);
-	values[4] = Int64GetDatum((int64) stats.zero_pages);
+	values[4] = Int64GetDatum((int64) stats.unused_pages);
 	values[5] = Int64GetDatum(stats.live_items);
 	values[6] = Int64GetDatum(stats.dead_items);
 	values[7] = Float8GetDatum(free_percent);
diff --git a/contrib/pgstattuple/pgstattuple--1.4--1.5.sql b/contrib/pgstattuple/pgstattuple--1.4--1.5.sql
index 05ae51f..5d03544 100644
--- a/contrib/pgstattuple/pgstattuple--1.4--1.5.sql
+++ b/contrib/pgstattuple/pgstattuple--1.4--1.5.sql
@@ -125,7 +125,7 @@ CREATE OR REPLACE FUNCTION pgstathashindex(IN relname regclass,
 	OUT bucket_pages BIGINT,
 	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 141d8e2..b6a5f19 100644
--- a/doc/src/sgml/pgstattuple.sgml
+++ b/doc/src/sgml/pgstattuple.sgml
@@ -372,7 +372,7 @@ version        | 2
 bucket_pages   | 33081
 overflow_pages | 0
 bitmap_pages   | 1
-zero_pages     | 32455
+unused_pages   | 32455
 live_items     | 10204006
 dead_items     | 0
 free_percent   | 61.8005949100872
@@ -418,9 +418,9 @@ free_percent   | 61.8005949100872
        </row>
 
        <row>
-        <entry><structfield>zero_pages</structfield></entry>
+        <entry><structfield>unused_pages</structfield></entry>
         <entry><type>bigint</type></entry>
-        <entry>Number of new or zero pages</entry>
+        <entry>Number of unused pages</entry>
        </row>
 
        <row>