The attached dbsize patch:

        + makes relation_size(relname) include toast tables;
        + adds aggregate_relation_size(relname) to count table data and indices;
        + adds indices_size(relname) to report the size of indices for a 
relation;

I've minimally tested it against PostgreSQL 8.1devel on i686-pc-linux-gnu, 
compiled by GCC gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5).

Ed
Index: contrib/dbsize/README.dbsize
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/README.dbsize,v
retrieving revision 1.4
diff -C1 -r1.4 README.dbsize
*** contrib/dbsize/README.dbsize	28 Sep 2004 19:35:43 -0000	1.4
--- contrib/dbsize/README.dbsize	25 Jan 2005 23:38:16 -0000
***************
*** 1,3 ****
! This module contains several functions that report the size of a given
! database object:
  
--- 1,3 ----
! This module contains several functions that report the amount of diskspace
! occupied by a given database object according to the stat function:
  
***************
*** 5,6 ****
--- 5,8 ----
  	int8 relation_size(text)
+ 	int8 aggregate_relation_size(text)
+ 	int8 indices_size(text)
  
***************
*** 12,14 ****
  
! The first two functions:
  
--- 14,16 ----
  
! The first four functions:
  
***************
*** 16,20 ****
  	SELECT relation_size('pg_class');
  
! take the name of the object (possibly schema-qualified, for relation_size),
! while these functions take object OIDs:
  	
--- 18,24 ----
  	SELECT relation_size('pg_class');
+ 	SELECT aggregate_relation_size('pg_class');
+ 	SELECT indices_size('pg_class');
  
! take the name of the object (possibly schema-qualified, for relation_size
! and aggregate_relation_size), while these functions take object OIDs:
  	
***************
*** 24,29 ****
  
! Please note that relation_size and pg_relation_size report only the size of
! the selected relation itself; any subsidiary indexes or toast tables are not
! counted.  To obtain the total size of a table including all helper files
! you'd have to do something like:
  
--- 28,65 ----
  
! The function relation_size() returns the size of a relation including the
! size of any toast tables and toast indices.  It does not include the 
! size of dependent indices.
! 
! The function aggregate_relation_size() returns the size of a relation 
! including the size of any toast tables, toast indices, and dependent 
! indices.  
! 
! The function indices_size() returns the size of all user-defined indices 
! for the given relation.  It does not include the size of the relation
! data nor does it include the size of any relation toast data.
! 
! Here's an example with a table called 'fat' that illustrates
! the differences between relation_size and aggregate_relation_size:
! 
! select indices_size(n.nspname||'.'||c.relname) as idx, 
!        relation_size(n.nspname||'.'||c.relname) as rel, 
!        aggregate_relation_size(n.nspname||'.'||c.relname) as total, 
!        c.relname, c.relkind as kind, c.oid, c.relfilenode as node
! from pg_class c, pg_namespace n 
! where c.relnamespace = n.oid 
!   and (c.relname like 'fat%' or c.relname like 'pg_toast%') 
! order by total, c.relname
! 
! (snipped)
!    idx   |   rel   |  total  |       relname        | kind |  oid  | node  
! ---------+---------+---------+----------------------+------+-------+-------
!        0 |   32768 |   32768 | pg_toast_59383_index | i    | 59388 | 59388
!    32768 |  704512 |  737280 | pg_toast_59383       | t    | 59386 | 59386
!        0 | 1818624 | 1818624 | fat_idx              | i    | 59389 | 59389
!  1818624 |  761856 | 2580480 | fat                  | r    | 59383 | 59383
! 
! Please note that pg_relation_size reports only the size of the selected 
! relation itself; any subsidiary indexes or toast tables are not counted.  
! To obtain the total size of a table including all helper files you'd 
! have to do something like:
  
***************
*** 45,46 ****
--- 81,84 ----
  
+ Alternatively, just use the aggregate_relation_size() function.
+ 
  This sample query utilizes the helper function pg_size_pretty(int8),
***************
*** 51 ****
--- 89,95 ----
  into any database using dbsize.sql.
+ 
+ Wishlist:
+     - include size of serial sequence objects
+     - make pg_* functions include toast, indices, and sequences;
+         - maybe other dependent objects as well?  triggers, procs, etc
+ 
Index: contrib/dbsize/dbsize.c
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/dbsize.c,v
retrieving revision 1.16
diff -C1 -r1.16 dbsize.c
*** contrib/dbsize/dbsize.c	1 Jan 2005 05:43:05 -0000	1.16
--- contrib/dbsize/dbsize.c	25 Jan 2005 23:38:17 -0000
***************
*** 24,25 ****
--- 24,26 ----
  #include "utils/syscache.h"
+ #include "utils/relcache.h"
  
***************
*** 36,37 ****
--- 37,40 ----
  Datum relation_size(PG_FUNCTION_ARGS);
+ Datum aggregate_relation_size(PG_FUNCTION_ARGS);
+ Datum indices_size(PG_FUNCTION_ARGS);
  
***************
*** 44,45 ****
--- 47,50 ----
  PG_FUNCTION_INFO_V1(relation_size);
+ PG_FUNCTION_INFO_V1(aggregate_relation_size);
+ PG_FUNCTION_INFO_V1(indices_size);
  
***************
*** 283,284 ****
--- 288,388 ----
  
+ /*
+  *  Compute on-disk size of files for 'relation' according to the stat function, 
+  *  optionally including heap data, index data, and/or toast data.
+  */
+ static int64
+ calculate_size(Relation relation, 
+                bool countData, 
+                bool countToast, 
+                bool countIndices)
+ {
+ 	Relation        idxrelation;
+ 	Relation        toastrelation;
+ 	Oid             relnodeOid;
+ 	Oid             tblspcOid;
+ 	Oid             toastOid;
+ 	bool            hasIndices;
+ 	int64           size = 0;
+ 	List            *indexoidlist;
+ 	ListCell        *idx;
+ 
+ 	tblspcOid  = relation->rd_rel->reltablespace;
+ 	relnodeOid = relation->rd_rel->relfilenode;
+ 	toastOid = relation->rd_rel->reltoastrelid;
+ 	hasIndices = relation->rd_rel->relhasindex;
+ 
+ 	if ( countData ) {
+ 		size += calculate_relation_size(tblspcOid, relnodeOid);
+ 	}
+ 
+ 	if ( hasIndices && countIndices ) {
+ 		/* recursively include any dependent indices ... */
+ 		indexoidlist = RelationGetIndexList(relation);
+ 		foreach(idx, indexoidlist) {
+ 			idxrelation = relation_open(lfirst_oid(idx), AccessShareLock);
+ 			size += calculate_size(idxrelation, TRUE, TRUE, TRUE);
+ 			relation_close(idxrelation, AccessShareLock);
+ 		}
+ 		list_free(indexoidlist);
+ 	}
+ 
+ 	if ( toastOid != 0 && countToast ) {
+ 
+ 		/* recursively include any toast relations ... */
+ 		toastrelation = relation_open(toastOid, AccessShareLock);
+ 		size += calculate_size(toastrelation, TRUE, TRUE, TRUE);
+ 		relation_close(toastrelation, AccessShareLock);
+ 	}
+ 
+ 	return size;
+ }
+ 
+ /*
+  *  Compute on-disk size of files for 'relation' including 
+  *  heap data, index data, and toasted data.
+  */
+ Datum
+ aggregate_relation_size(PG_FUNCTION_ARGS)
+ {
+ 	text       *relname = PG_GETARG_TEXT_P(0);
+ 	RangeVar   *relrv;
+ 	Relation    relation;
+ 	int64       size = 0;
+ 
+ 	relrv = makeRangeVarFromNameList(
+ 			textToQualifiedNameList(relname, "aggregate_relation_size"));
+ 
+ 	relation = relation_openrv(relrv, AccessShareLock);
+ 	size = calculate_size(relation, TRUE, TRUE, TRUE);
+ 	relation_close(relation, AccessShareLock);
+ 
+ 	PG_RETURN_INT64(size);
+ }
+ 
+ /*
+  *  Compute on-disk size of indices for 'relation'.
+  */
+ Datum
+ indices_size(PG_FUNCTION_ARGS)
+ {
+ 	text       *relname = PG_GETARG_TEXT_P(0);
+ 	RangeVar   *relrv;
+ 	Relation    relation;
+ 	int64       size = 0;
+ 
+ 	relrv = makeRangeVarFromNameList(
+ 			textToQualifiedNameList(relname, "indices_size"));
+ 
+ 	relation = relation_openrv(relrv, AccessShareLock);
+ 	size = calculate_size(relation, FALSE, FALSE, TRUE);
+ 	relation_close(relation, AccessShareLock);
+ 
+ 	PG_RETURN_INT64(size);
+ }
+ 
+ 
+ /*
+  *  Compute on-disk size of files for 'relation' including 
+  *  heap data and toasted data, but excluding indices.
+  */
  Datum
***************
*** 289,292 ****
  	Relation	relation;
! 	Oid			relnodeOid;
! 	Oid         tblspcOid;
  
--- 393,395 ----
  	Relation	relation;
! 	int64       size = 0;
  
***************
*** 295,305 ****
  	relation = relation_openrv(relrv, AccessShareLock);
! 
! 	tblspcOid  = relation->rd_rel->reltablespace;
! 	relnodeOid = relation->rd_rel->relfilenode;
! 
  	relation_close(relation, AccessShareLock);
  
! 	PG_RETURN_INT64(calculate_relation_size(tblspcOid, relnodeOid));
  }
  
  /*
--- 398,406 ----
  	relation = relation_openrv(relrv, AccessShareLock);
! 	size = calculate_size(relation, TRUE, TRUE, FALSE);
  	relation_close(relation, AccessShareLock);
  
! 	PG_RETURN_INT64(size);
  }
  
+ 
  /*
Index: contrib/dbsize/dbsize.sql.in
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/dbsize.sql.in,v
retrieving revision 1.4
diff -C1 -r1.4 dbsize.sql.in
*** contrib/dbsize/dbsize.sql.in	28 Sep 2004 19:35:43 -0000	1.4
--- contrib/dbsize/dbsize.sql.in	25 Jan 2005 23:38:17 -0000
***************
*** 8,9 ****
--- 8,17 ----
  
+ CREATE FUNCTION aggregate_relation_size (text) RETURNS bigint
+     AS 'MODULE_PATHNAME', 'aggregate_relation_size'
+     LANGUAGE C STRICT;
+ 
+ CREATE FUNCTION indices_size (text) RETURNS bigint
+     AS 'MODULE_PATHNAME', 'indices_size'
+     LANGUAGE C STRICT;
+ 
  CREATE FUNCTION pg_tablespace_size(oid) RETURNS bigint
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Reply via email to