Hi,
On Tue, Feb 7, 2017 at 9:23 AM, Robert Haas <robertmh...@gmail.com> wrote:
> On Mon, Feb 6, 2017 at 10:40 PM, Amit Kapila <amit.kapil...@gmail.com> wrote:
>>> Maybe we should call them "unused pages".
>>
>> +1. If we consider some more names for that column then probably one
>> alternative could be "empty pages".
>
> Yeah, but I think "unused" might be better. Because a page could be
> in use (as an overflow page or primary bucket page) and still be
> empty.
>
Based on the earlier discussions, I have prepared a patch that would
allow pgstathashindex() to show the number of unused pages in hash
index. Please find the attached patch for the same. Thanks.
--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com
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=> 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
