On Mon, Jan 23, 2012 at 7:18 PM, Noah Misch <n...@leadboat.com> wrote:
> On Mon, Jan 23, 2012 at 04:56:24PM -0300, Alvaro Herrera wrote:
>>
>> Hm. Leaf pages hold as much tuples as non-leaf pages, no? I mean
>> for each page element there's a value and a CTID. In non-leaf those
>> CTIDs point to other index pages, one level down the tree; in leaf pages
>> they point to the heap.
>
> That distinction seemed important when I sent my last message, but now I agree
> that it's largely irrelevant for free space purposes. If someone feels like
> doing it, +1 for making pgstattuple() count non-leaf free space.
>
actually i agreed that non-leaf pages are irrelevant... i just
confirmed that in a production system with 300GB none of the indexes
in an 84M rows table nor in a heavily updated one has more than 1 root
page, all the rest are deleted, half_dead or leaf. so the posibility
of bloat coming from non-leaf pages seems very odd
but the possibility of bloat coming from the meta page doesn't exist,
AFAIUI at least
we need the most accurate value about usable free space, because the
idea is to add a sampler mode to the function so we don't scan the
whole relation. that's why we still need the function.
btw... pgstattuple also has the problem that it's not using a ring buffer
attached are two patches:
- v5: is the same original patch but only track space in leaf, deleted
and half_dead pages
- v5.1: adds the same for all kind of indexes (problem is that this is
inconsistent with the fact that pageinspect only manages btree indexes
for everything else)
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile
new file mode 100644
index 13ba6d3..63fab95
*** a/contrib/pageinspect/Makefile
--- b/contrib/pageinspect/Makefile
*************** MODULE_big = pageinspect
*** 4,10 ****
OBJS = rawpage.o heapfuncs.o btreefuncs.o fsmfuncs.o
EXTENSION = pageinspect
! DATA = pageinspect--1.0.sql pageinspect--unpackaged--1.0.sql
ifdef USE_PGXS
PG_CONFIG = pg_config
--- 4,12 ----
OBJS = rawpage.o heapfuncs.o btreefuncs.o fsmfuncs.o
EXTENSION = pageinspect
! DATA = pageinspect--1.0.sql pageinspect--1.1.sql \
! pageinspect--1.0--1.1.sql \
! pageinspect--unpackaged--1.0.sql
ifdef USE_PGXS
PG_CONFIG = pg_config
diff --git a/contrib/pageinspect/btreefuncs.c b/contrib/pageinspect/btreefuncs.c
new file mode 100644
index dbb2158..9c0b0fb
*** a/contrib/pageinspect/btreefuncs.c
--- b/contrib/pageinspect/btreefuncs.c
***************
*** 34,39 ****
--- 34,40 ----
#include "utils/builtins.h"
#include "utils/rel.h"
+ #include "btreefuncs.h"
extern Datum bt_metap(PG_FUNCTION_ARGS);
extern Datum bt_page_items(PG_FUNCTION_ARGS);
*************** GetBTPageStatistics(BlockNumber blkno, B
*** 155,160 ****
--- 156,216 ----
stat->avg_item_size = 0;
}
+ /*------------------------------------------------
+ * GetBTRelationFreeSpace
+ *
+ * Get the free space for a btree index.
+ * This is a helper function for relation_free_space()
+ *------------------------------------------------
+ */
+ float4
+ GetBTRelationFreeSpace(Relation rel)
+ {
+ BTPageStat stat;
+
+ Buffer buffer;
+ BlockNumber blkno;
+ BlockNumber totalBlcksInRelation = RelationGetNumberOfBlocks(rel);
+ BlockNumber totalBlcksCounted = 0;
+ Size free_space = 0;
+ double free_percent = 0;
+
+ BufferAccessStrategy bstrategy = GetAccessStrategy(BAS_BULKREAD);
+
+ /* Skip page 0 because it is a metapage */
+ for (blkno = 1; blkno < totalBlcksInRelation; blkno++)
+ {
+ buffer = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy);
+ /*
+ * get the statistics of the indexes and use that info
+ * to determine free space on the page
+ */
+ GetBTPageStatistics(blkno, buffer, &stat);
+ /*
+ * Consider pages DELETED and HALF_DEAD as empty,
+ * besides those only consider LEAF pages
+ */
+ if (stat.type == 'd' || stat.type == 'e')
+ {
+ free_space += stat.page_size;
+ totalBlcksCounted++;
+ }
+ else if (stat.type == 'l')
+ {
+ free_space += stat.free_size;
+ totalBlcksCounted++;
+ }
+
+ ReleaseBuffer(buffer);
+ }
+
+ if (totalBlcksCounted > 0)
+ free_percent = ((float4) free_space) / (totalBlcksCounted * BLCKSZ);
+
+ return free_percent;
+ }
+
+
/* -----------------------------------------------
* bt_page()
*
diff --git a/contrib/pageinspect/btreefuncs.h b/contrib/pageinspect/btreefuncs.h
new file mode 100644
index ...549f878
*** a/contrib/pageinspect/btreefuncs.h
--- b/contrib/pageinspect/btreefuncs.h
***************
*** 0 ****
--- 1,5 ----
+ /*
+ * contrib/pageinspect/btreefuncs.h
+ */
+
+ float4 GetBTRelationFreeSpace(Relation);
diff --git a/contrib/pageinspect/heapfuncs.c b/contrib/pageinspect/heapfuncs.c
new file mode 100644
index 260ccff..8e3961d
*** a/contrib/pageinspect/heapfuncs.c
--- b/contrib/pageinspect/heapfuncs.c
***************
*** 28,33 ****
--- 28,36 ----
#include "funcapi.h"
#include "utils/builtins.h"
#include "miscadmin.h"
+ #include "storage/bufmgr.h"
+
+ #include "heapfuncs.h"
Datum heap_page_items(PG_FUNCTION_ARGS);
*************** bits_to_text(bits8 *bits, int len)
*** 55,60 ****
--- 58,96 ----
}
+ /*
+ * GetHeapRelationFreeSpace()
+ *
+ * Get the free space for a heap relation.
+ * This is a helper function for relation_free_space()
+ */
+ float4
+ GetHeapRelationFreeSpace(Relation rel)
+ {
+ Buffer buffer;
+ Page page;
+ BlockNumber blkno;
+ BlockNumber totalBlcksInRelation = RelationGetNumberOfBlocks(rel);
+ Size free_space = 0;
+ double free_percent = 0;
+
+ BufferAccessStrategy bstrategy = GetAccessStrategy(BAS_BULKREAD);
+
+ for (blkno = 0; blkno < totalBlcksInRelation; blkno++)
+ {
+ buffer = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy);
+ page = BufferGetPage(buffer);
+ free_space += PageGetHeapFreeSpace(page);
+
+ ReleaseBuffer(buffer);
+ }
+
+ if (totalBlcksInRelation > 0)
+ free_percent = ((float4) free_space) / (totalBlcksInRelation * BLCKSZ);
+ return free_percent;
+ }
+
+
/*
* heap_page_items
*
diff --git a/contrib/pageinspect/heapfuncs.h b/contrib/pageinspect/heapfuncs.h
new file mode 100644
index ...17b7cb3
*** a/contrib/pageinspect/heapfuncs.h
--- b/contrib/pageinspect/heapfuncs.h
***************
*** 0 ****
--- 1,5 ----
+ /*
+ * contrib/pageinspect/heapfuncs.h
+ */
+
+ float4 GetHeapRelationFreeSpace(Relation);
diff --git a/contrib/pageinspect/pageinspect--1.0--1.1.sql b/contrib/pageinspect/pageinspect--1.0--1.1.sql
new file mode 100644
index ...c97aeba
*** a/contrib/pageinspect/pageinspect--1.0--1.1.sql
--- b/contrib/pageinspect/pageinspect--1.0--1.1.sql
***************
*** 0 ****
--- 1,12 ----
+ /* contrib/pageinspect/pageinspect--1.0--1.1.sql */
+
+ -- complain if script is sourced in psql, rather than via CREATE EXTENSION
+ \echo Use "ALTER EXTENSION pageinspect UPDATE TO '1.1'" to load this file. \quit
+
+ --
+ -- relation_free_space()
+ --
+ CREATE FUNCTION relation_free_space(IN relname text)
+ RETURNS real
+ AS 'MODULE_PATHNAME', 'relation_free_space'
+ LANGUAGE C STRICT;
diff --git a/contrib/pageinspect/pageinspect--1.1.sql b/contrib/pageinspect/pageinspect--1.1.sql
new file mode 100644
index ...035195b
*** a/contrib/pageinspect/pageinspect--1.1.sql
--- b/contrib/pageinspect/pageinspect--1.1.sql
***************
*** 0 ****
--- 1,115 ----
+ /* contrib/pageinspect/pageinspect--1.1.sql */
+
+ -- complain if script is sourced in psql, rather than via CREATE EXTENSION
+ \echo Use "CREATE EXTENSION pageinspect" to load this file. \quit
+
+ --
+ -- get_raw_page()
+ --
+ CREATE FUNCTION get_raw_page(text, int4)
+ RETURNS bytea
+ AS 'MODULE_PATHNAME', 'get_raw_page'
+ LANGUAGE C STRICT;
+
+ CREATE FUNCTION get_raw_page(text, text, int4)
+ RETURNS bytea
+ AS 'MODULE_PATHNAME', 'get_raw_page_fork'
+ LANGUAGE C STRICT;
+
+ --
+ -- page_header()
+ --
+ CREATE FUNCTION page_header(IN page bytea,
+ OUT lsn text,
+ OUT tli smallint,
+ OUT flags smallint,
+ OUT lower smallint,
+ OUT upper smallint,
+ OUT special smallint,
+ OUT pagesize smallint,
+ OUT version smallint,
+ OUT prune_xid xid)
+ AS 'MODULE_PATHNAME', 'page_header'
+ LANGUAGE C STRICT;
+
+ --
+ -- heap_page_items()
+ --
+ CREATE FUNCTION heap_page_items(IN page bytea,
+ OUT lp smallint,
+ OUT lp_off smallint,
+ OUT lp_flags smallint,
+ OUT lp_len smallint,
+ OUT t_xmin xid,
+ OUT t_xmax xid,
+ OUT t_field3 int4,
+ OUT t_ctid tid,
+ OUT t_infomask2 integer,
+ OUT t_infomask integer,
+ OUT t_hoff smallint,
+ OUT t_bits text,
+ OUT t_oid oid)
+ RETURNS SETOF record
+ AS 'MODULE_PATHNAME', 'heap_page_items'
+ LANGUAGE C STRICT;
+
+ --
+ -- bt_metap()
+ --
+ CREATE FUNCTION bt_metap(IN relname text,
+ OUT magic int4,
+ OUT version int4,
+ OUT root int4,
+ OUT level int4,
+ OUT fastroot int4,
+ OUT fastlevel int4)
+ AS 'MODULE_PATHNAME', 'bt_metap'
+ LANGUAGE C STRICT;
+
+ --
+ -- bt_page_stats()
+ --
+ CREATE FUNCTION bt_page_stats(IN relname text, IN blkno int4,
+ OUT blkno int4,
+ OUT type "char",
+ OUT live_items int4,
+ OUT dead_items int4,
+ OUT avg_item_size int4,
+ OUT page_size int4,
+ OUT free_size int4,
+ OUT btpo_prev int4,
+ OUT btpo_next int4,
+ OUT btpo int4,
+ OUT btpo_flags int4)
+ AS 'MODULE_PATHNAME', 'bt_page_stats'
+ LANGUAGE C STRICT;
+
+ --
+ -- bt_page_items()
+ --
+ CREATE FUNCTION bt_page_items(IN relname text, IN blkno int4,
+ OUT itemoffset smallint,
+ OUT ctid tid,
+ OUT itemlen smallint,
+ OUT nulls bool,
+ OUT vars bool,
+ OUT data text)
+ RETURNS SETOF record
+ AS 'MODULE_PATHNAME', 'bt_page_items'
+ LANGUAGE C STRICT;
+
+ --
+ -- fsm_page_contents()
+ --
+ CREATE FUNCTION fsm_page_contents(IN page bytea)
+ RETURNS text
+ AS 'MODULE_PATHNAME', 'fsm_page_contents'
+ LANGUAGE C STRICT;
+
+ --
+ -- relation_free_space()
+ --
+ CREATE FUNCTION relation_free_space(IN relname text)
+ RETURNS real
+ AS 'MODULE_PATHNAME', 'relation_free_space'
+ LANGUAGE C STRICT;
diff --git a/contrib/pageinspect/pageinspect.control b/contrib/pageinspect/pageinspect.control
new file mode 100644
index f9da0e8..a412cf1
*** a/contrib/pageinspect/pageinspect.control
--- b/contrib/pageinspect/pageinspect.control
***************
*** 1,5 ****
# pageinspect extension
comment = 'inspect the contents of database pages at a low level'
! default_version = '1.0'
module_pathname = '$libdir/pageinspect'
relocatable = true
--- 1,5 ----
# pageinspect extension
comment = 'inspect the contents of database pages at a low level'
! default_version = '1.1'
module_pathname = '$libdir/pageinspect'
relocatable = true
diff --git a/contrib/pageinspect/rawpage.c b/contrib/pageinspect/rawpage.c
new file mode 100644
index f51a4e3..9a54744
*** a/contrib/pageinspect/rawpage.c
--- b/contrib/pageinspect/rawpage.c
***************
*** 23,33 ****
--- 23,37 ----
#include "utils/builtins.h"
#include "utils/rel.h"
+ #include "btreefuncs.h"
+ #include "heapfuncs.h"
+
PG_MODULE_MAGIC;
Datum get_raw_page(PG_FUNCTION_ARGS);
Datum get_raw_page_fork(PG_FUNCTION_ARGS);
Datum page_header(PG_FUNCTION_ARGS);
+ Datum relation_free_space(PG_FUNCTION_ARGS);
static bytea *get_raw_page_internal(text *relname, ForkNumber forknum,
BlockNumber blkno);
*************** page_header(PG_FUNCTION_ARGS)
*** 227,229 ****
--- 231,285 ----
PG_RETURN_DATUM(result);
}
+
+
+ /*
+ * relation_free_space
+ *
+ * Returns the percentage of free space for a given relation.
+ * Supported relation types are tables and indexes. TOAST is not
+ * considered/handled.
+ */
+ PG_FUNCTION_INFO_V1(relation_free_space);
+
+ Datum
+ relation_free_space(PG_FUNCTION_ARGS)
+ {
+ text *relname = PG_GETARG_TEXT_P(0);
+ RangeVar *relrv;
+ Relation rel;
+ float4 free_space = 0;
+
+ relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
+ rel = relation_openrv(relrv, AccessShareLock);
+
+ /* Only allow tables or indexes */
+ if (rel->rd_rel->relkind != RELKIND_INDEX && rel->rd_rel->relkind != RELKIND_RELATION)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot get free space from object \"%s\"",
+ 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 tables of other sessions")));
+
+ switch (rel->rd_rel->relkind)
+ {
+ case RELKIND_RELATION:
+ free_space = GetHeapRelationFreeSpace(rel);
+ break;
+ case RELKIND_INDEX:
+ free_space = GetBTRelationFreeSpace(rel);
+ break;
+ }
+ relation_close(rel, AccessShareLock);
+
+ PG_RETURN_FLOAT4(free_space);
+ }
diff --git a/doc/src/sgml/pageinspect.sgml b/doc/src/sgml/pageinspect.sgml
new file mode 100644
index acbb05b..49708c3
*** a/doc/src/sgml/pageinspect.sgml
--- b/doc/src/sgml/pageinspect.sgml
*************** test=# SELECT * FROM bt_page_items('pg_c
*** 196,201 ****
--- 196,216 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>relation_free_space(relname text) returns float</function>
+ </term>
+
+ <listitem>
+ <para>
+ <function>relation_free_space</function> returns the total amount of free
+ free space in a table or index. The entire relation is scanned in order
+ to compute this amount, which may generate a large amount of disk reads.
+ Information stored in <acronym>TOAST</> tables is not included.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</sect2>
diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile
new file mode 100644
index 63fab95..f63b92f
*** a/contrib/pageinspect/Makefile
--- b/contrib/pageinspect/Makefile
***************
*** 1,7 ****
# contrib/pageinspect/Makefile
MODULE_big = pageinspect
! OBJS = rawpage.o heapfuncs.o btreefuncs.o fsmfuncs.o
EXTENSION = pageinspect
DATA = pageinspect--1.0.sql pageinspect--1.1.sql \
--- 1,7 ----
# contrib/pageinspect/Makefile
MODULE_big = pageinspect
! OBJS = rawpage.o heapfuncs.o btreefuncs.o idxfuncs.o fsmfuncs.o
EXTENSION = pageinspect
DATA = pageinspect--1.0.sql pageinspect--1.1.sql \
diff --git a/contrib/pageinspect/btreefuncs.c b/contrib/pageinspect/btreefuncs.c
new file mode 100644
index 9c0b0fb..b399b85
*** a/contrib/pageinspect/btreefuncs.c
--- b/contrib/pageinspect/btreefuncs.c
***************
*** 34,41 ****
#include "utils/builtins.h"
#include "utils/rel.h"
- #include "btreefuncs.h"
-
extern Datum bt_metap(PG_FUNCTION_ARGS);
extern Datum bt_page_items(PG_FUNCTION_ARGS);
extern Datum bt_page_stats(PG_FUNCTION_ARGS);
--- 34,39 ----
*************** GetBTPageStatistics(BlockNumber blkno, B
*** 156,216 ****
stat->avg_item_size = 0;
}
- /*------------------------------------------------
- * GetBTRelationFreeSpace
- *
- * Get the free space for a btree index.
- * This is a helper function for relation_free_space()
- *------------------------------------------------
- */
- float4
- GetBTRelationFreeSpace(Relation rel)
- {
- BTPageStat stat;
-
- Buffer buffer;
- BlockNumber blkno;
- BlockNumber totalBlcksInRelation = RelationGetNumberOfBlocks(rel);
- BlockNumber totalBlcksCounted = 0;
- Size free_space = 0;
- double free_percent = 0;
-
- BufferAccessStrategy bstrategy = GetAccessStrategy(BAS_BULKREAD);
-
- /* Skip page 0 because it is a metapage */
- for (blkno = 1; blkno < totalBlcksInRelation; blkno++)
- {
- buffer = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy);
- /*
- * get the statistics of the indexes and use that info
- * to determine free space on the page
- */
- GetBTPageStatistics(blkno, buffer, &stat);
- /*
- * Consider pages DELETED and HALF_DEAD as empty,
- * besides those only consider LEAF pages
- */
- if (stat.type == 'd' || stat.type == 'e')
- {
- free_space += stat.page_size;
- totalBlcksCounted++;
- }
- else if (stat.type == 'l')
- {
- free_space += stat.free_size;
- totalBlcksCounted++;
- }
-
- ReleaseBuffer(buffer);
- }
-
- if (totalBlcksCounted > 0)
- free_percent = ((float4) free_space) / (totalBlcksCounted * BLCKSZ);
-
- return free_percent;
- }
-
-
/* -----------------------------------------------
* bt_page()
*
--- 154,159 ----
diff --git a/contrib/pageinspect/btreefuncs.h b/contrib/pageinspect/btreefuncs.h
new file mode .
index 549f878..e69de29
*** a/contrib/pageinspect/btreefuncs.h
--- b/contrib/pageinspect/btreefuncs.h
***************
*** 1,5 ****
- /*
- * contrib/pageinspect/btreefuncs.h
- */
-
- float4 GetBTRelationFreeSpace(Relation);
--- 0 ----
diff --git a/contrib/pageinspect/idxfuncs.c b/contrib/pageinspect/idxfuncs.c
new file mode 100644
index ...6533429
*** a/contrib/pageinspect/idxfuncs.c
--- b/contrib/pageinspect/idxfuncs.c
***************
*** 0 ****
--- 1,313 ----
+ /*
+ * contrib/pageinspect/idxfuncs.c
+ *
+ *
+ * idxfuncs.c
+ *
+ * Permission to use, copy, modify, and distribute this software and
+ * its documentation for any purpose, without fee, and without a
+ * written agreement is hereby granted, provided that the above
+ * copyright notice and this paragraph and the following two
+ * paragraphs appear in all copies.
+ *
+ * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT,
+ * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
+ * LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
+ * DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED
+ * OF THE POSSIBILITY OF SUCH DAMAGE.
+ *
+ * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT
+ * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
+ * A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS
+ * IS" BASIS, AND THE AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE,
+ * SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
+ */
+
+ #include "postgres.h"
+
+ #include "access/gin_private.h"
+ #include "access/gist_private.h"
+ #include "access/hash.h"
+ #include "access/nbtree.h"
+ #include "access/spgist_private.h"
+ #include "funcapi.h"
+ #include "utils/rel.h"
+
+ #include "idxfuncs.h"
+
+ /*------------------------------------------------
+ * GetIndexRelationFreeSpace
+ *
+ * Look the type of the index and call the appropiate
+ * function to get the free space
+ *------------------------------------------------
+ */
+ float4
+ GetIndexRelationFreeSpace(Relation rel)
+ {
+ switch (rel->rd_rel->relam)
+ {
+ case BTREE_AM_OID:
+ return GetBTRelationFreeSpace(rel);
+ case HASH_AM_OID:
+ return GetHashRelationFreeSpace(rel);
+ case GIST_AM_OID:
+ return GetGistRelationFreeSpace(rel);
+ case GIN_AM_OID:
+ return GetGinRelationFreeSpace(rel);
+ case SPGIST_AM_OID:
+ return GetSpGistRelationFreeSpace(rel);
+ default:
+ elog(ERROR, "Unknown index type %d", rel->rd_rel->relam);
+ }
+
+ return 0;
+ }
+
+ /*------------------------------------------------
+ * GetBTRelationFreeSpace
+ *
+ * Get the free space for a btree index.
+ * This is a helper function for relation_free_space()
+ *------------------------------------------------
+ */
+ float4
+ GetBTRelationFreeSpace(Relation rel)
+ {
+ BlockNumber blkno;
+ BlockNumber totalBlcksInRelation = RelationGetNumberOfBlocks(rel);
+ BlockNumber totalBlcksCounted = 0;
+ Size free_space = 0;
+ double free_percent = 0;
+
+ BufferAccessStrategy bstrategy = GetAccessStrategy(BAS_BULKREAD);
+
+ /* Start just after the metapage */
+ for (blkno = BTREE_METAPAGE + 1; blkno < totalBlcksInRelation; blkno++)
+ {
+ Buffer buffer = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy);
+ Page page = BufferGetPage(buffer);
+ BTPageOpaque opaque = (BTPageOpaque) PageGetSpecialPointer(page);
+
+ /*
+ * Consider pages DELETED and HALF_DEAD as empty,
+ * besides those only consider LEAF pages
+ */
+ if (P_ISDELETED(opaque) || P_ISHALFDEAD(opaque))
+ {
+ free_space += PageGetPageSize(page);
+ totalBlcksCounted++;
+ }
+ else if (P_ISLEAF(opaque))
+ {
+ free_space += PageGetFreeSpace(page);
+ totalBlcksCounted++;
+ }
+
+ ReleaseBuffer(buffer);
+ }
+
+ if (totalBlcksCounted > 0)
+ free_percent = ((float4) free_space) / (totalBlcksCounted * BLCKSZ);
+
+ return free_percent;
+ }
+
+
+ /*------------------------------------------------
+ * GetHashRelationFreeSpace
+ *
+ * Get the free space for a Hash index.
+ * This is a helper function for relation_free_space()
+ *------------------------------------------------
+ */
+ float4
+ GetHashRelationFreeSpace(Relation rel)
+ {
+ BlockNumber blkno;
+ BlockNumber totalBlcksInRelation = RelationGetNumberOfBlocks(rel);
+ BlockNumber totalBlcksCounted = 0;
+ Size free_space = 0;
+ double free_percent = 0;
+
+ BufferAccessStrategy bstrategy = GetAccessStrategy(BAS_BULKREAD);
+
+ /* Start just after the metapage */
+ for (blkno = HASH_METAPAGE + 1; blkno < totalBlcksInRelation; blkno++)
+ {
+ Buffer buffer = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy);
+ Page page = BufferGetPage(buffer);
+ HashPageOpaque opaque = (HashPageOpaque) PageGetSpecialPointer(page);
+
+ /*
+ * Consider pages LH_UNUSED_PAGE as empty,
+ * besides that consider the free space in LH_OVERFLOW_PAGE and LH_BUCKET_PAGE pages
+ */
+ if (opaque->hasho_flag == LH_UNUSED_PAGE)
+ {
+ free_space += PageGetPageSize(page);
+ totalBlcksCounted++;
+ }
+ else if (opaque->hasho_flag == LH_OVERFLOW_PAGE || opaque->hasho_flag == LH_BUCKET_PAGE)
+ {
+ free_space += PageGetFreeSpace(page);
+ totalBlcksCounted++;
+ }
+
+ ReleaseBuffer(buffer);
+ }
+
+ if (totalBlcksCounted > 0)
+ free_percent = ((float4) free_space) / (totalBlcksCounted * BLCKSZ);
+
+ return free_percent;
+ }
+
+ /*------------------------------------------------
+ * GetGistRelationFreeSpace
+ *
+ * Get the free space for a Gist index.
+ * This is a helper function for relation_free_space()
+ *------------------------------------------------
+ */
+ float4
+ GetGistRelationFreeSpace(Relation rel)
+ {
+ BlockNumber blkno;
+ BlockNumber totalBlcksInRelation = RelationGetNumberOfBlocks(rel);
+ BlockNumber totalBlcksCounted = 0;
+ Size free_space = 0;
+ double free_percent = 0;
+
+ BufferAccessStrategy bstrategy = GetAccessStrategy(BAS_BULKREAD);
+
+ /* Start from the root */
+ for (blkno = GIST_ROOT_BLKNO; blkno < totalBlcksInRelation; blkno++)
+ {
+ Buffer buffer = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy);
+ Page page = BufferGetPage(buffer);
+
+ /*
+ * Consider pages LH_UNUSED_PAGE as empty,
+ * besides that consider the free space in LH_OVERFLOW_PAGE and LH_BUCKET_PAGE pages
+ */
+ if (GistPageIsDeleted(page))
+ {
+ free_space += PageGetPageSize(page);
+ totalBlcksCounted++;
+ }
+ else if (GistPageIsLeaf(page))
+ {
+ free_space += PageGetFreeSpace(page);
+ totalBlcksCounted++;
+ }
+
+ ReleaseBuffer(buffer);
+ }
+
+ if (totalBlcksCounted > 0)
+ free_percent = ((float4) free_space) / (totalBlcksCounted * BLCKSZ);
+
+ return free_percent;
+ }
+
+ /*------------------------------------------------
+ * GetGinRelationFreeSpace
+ *
+ * Get the free space for a Gin index.
+ * This is a helper function for relation_free_space()
+ *------------------------------------------------
+ */
+ float4
+ GetGinRelationFreeSpace(Relation rel)
+ {
+ BlockNumber blkno;
+ BlockNumber totalBlcksInRelation = RelationGetNumberOfBlocks(rel);
+ BlockNumber totalBlcksCounted = 0;
+ Size free_space = 0;
+ double free_percent = 0;
+
+ BufferAccessStrategy bstrategy = GetAccessStrategy(BAS_BULKREAD);
+
+ /* Start just after the first root */
+ for (blkno = GIN_METAPAGE_BLKNO + 1; blkno < totalBlcksInRelation; blkno++)
+ {
+ Buffer buffer = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy);
+ Page page = BufferGetPage(buffer);
+
+ /*
+ * Consider pages GIN_DELETED as empty,
+ * besides that consider the free space in GIN_DATA and GIN_LEAF pages
+ */
+ if (GinPageIsDeleted(page))
+ {
+ free_space += PageGetPageSize(page);
+ totalBlcksCounted++;
+ }
+ else if (GinPageIsLeaf(page))
+ {
+ free_space += PageGetFreeSpace(page);
+ totalBlcksCounted++;
+ }
+ else if (GinPageIsData(page))
+ {
+ free_space += GinDataPageGetFreeSpace(page);
+ totalBlcksCounted++;
+ }
+
+ ReleaseBuffer(buffer);
+ }
+
+ if (totalBlcksCounted > 0)
+ free_percent = ((float4) free_space) / (totalBlcksCounted * BLCKSZ);
+
+ return free_percent;
+ }
+
+ /*------------------------------------------------
+ * GetSPGistRelationFreeSpace
+ *
+ * Get the free space for a SPGist index.
+ * This is a helper function for relation_free_space()
+ *------------------------------------------------
+ */
+ float4
+ GetSpGistRelationFreeSpace(Relation rel)
+ {
+ BlockNumber blkno;
+ BlockNumber totalBlcksInRelation = RelationGetNumberOfBlocks(rel);
+ BlockNumber totalBlcksCounted = 0;
+ Size free_space = 0;
+ double free_percent = 0;
+
+ BufferAccessStrategy bstrategy = GetAccessStrategy(BAS_BULKREAD);
+
+ /* Start just after the first root */
+ for (blkno = SPGIST_METAPAGE_BLKNO + 1; blkno < totalBlcksInRelation; blkno++)
+ {
+ Buffer buffer = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy);
+ Page page = BufferGetPage(buffer);
+
+ /*
+ * Consider pages GIN_DELETED as empty,
+ * besides that consider the free space in GIN_DATA and GIN_LEAF pages
+ */
+ if (SpGistPageIsDeleted(page))
+ {
+ free_space += PageGetPageSize(page);
+ totalBlcksCounted++;
+ }
+ else if (SpGistPageIsLeaf(page))
+ {
+ free_space += SpGistPageGetFreeSpace(page, 1);
+ totalBlcksCounted++;
+ }
+
+ ReleaseBuffer(buffer);
+ }
+
+ if (totalBlcksCounted > 0)
+ free_percent = ((float4) free_space) / (totalBlcksCounted * BLCKSZ);
+
+ return free_percent;
+ }
diff --git a/contrib/pageinspect/idxfuncs.h b/contrib/pageinspect/idxfuncs.h
new file mode 100644
index ...788d920
*** a/contrib/pageinspect/idxfuncs.h
--- b/contrib/pageinspect/idxfuncs.h
***************
*** 0 ****
--- 1,10 ----
+ /*
+ * contrib/pageinspect/idxfuncs.h
+ */
+
+ float4 GetIndexRelationFreeSpace(Relation);
+ float4 GetBTRelationFreeSpace(Relation);
+ float4 GetHashRelationFreeSpace(Relation);
+ float4 GetGistRelationFreeSpace(Relation);
+ float4 GetGinRelationFreeSpace(Relation);
+ float4 GetSpGistRelationFreeSpace(Relation);
diff --git a/contrib/pageinspect/rawpage.c b/contrib/pageinspect/rawpage.c
new file mode 100644
index 9a54744..99fccab
*** a/contrib/pageinspect/rawpage.c
--- b/contrib/pageinspect/rawpage.c
***************
*** 23,30 ****
#include "utils/builtins.h"
#include "utils/rel.h"
- #include "btreefuncs.h"
#include "heapfuncs.h"
PG_MODULE_MAGIC;
--- 23,30 ----
#include "utils/builtins.h"
#include "utils/rel.h"
#include "heapfuncs.h"
+ #include "idxfuncs.h"
PG_MODULE_MAGIC;
*************** relation_free_space(PG_FUNCTION_ARGS)
*** 276,282 ****
free_space = GetHeapRelationFreeSpace(rel);
break;
case RELKIND_INDEX:
! free_space = GetBTRelationFreeSpace(rel);
break;
}
relation_close(rel, AccessShareLock);
--- 276,282 ----
free_space = GetHeapRelationFreeSpace(rel);
break;
case RELKIND_INDEX:
! free_space = GetIndexRelationFreeSpace(rel);
break;
}
relation_close(rel, AccessShareLock);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
