On Thursday January 27 2005 12:08, Neil Conway wrote:
> On Thu, 2005-01-27 at 08:05 +0100, Michael Paesold wrote:
> > Perhaps you could rename indices_size to indexes_size.
>
> Yeah, sorry -- forgot to mention that. I believe we decided to
> standardize on "indexes" as the plural of "index" (at least in
> user-visible stuff) a few releases go.

Attached patch identical except for s/indices/indexes/g.

Ed
Index: contrib/dbsize/README.dbsize
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/README.dbsize,v
retrieving revision 1.4
diff -r1.4 README.dbsize
1,2c1,2
< This module contains several functions that report the size of a given
< database object:
---
> This module contains several functions that report the amount of diskspace
> occupied by a given database object according to the stat function:
5a6,7
> 	int8 aggregate_relation_size(text)
> 	int8 indexes_size(text)
13c15
< The first two functions:
---
> The first four functions:
16a19,20
> 	SELECT aggregate_relation_size('pg_class');
> 	SELECT indexes_size('pg_class');
18,19c22,23
< take the name of the object (possibly schema-qualified, for relation_size),
< while these functions take object OIDs:
---
> take the name of the object (possibly schema-qualified, for relation_size
> and aggregate_relation_size), while these functions take object OIDs:
25,28c29,64
< 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:
---
> The function relation_size() returns the size of a relation including the
> size of any toast tables and toast indexes.  It does not include the 
> size of dependent indexes.
> 
> The function aggregate_relation_size() returns the size of a relation 
> including the size of any toast tables, toast indexes, and dependent 
> indexes.  
> 
> The function indexes_size() returns the size of all user-defined indexes 
> 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 indexes_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:
45a82,83
> Alternatively, just use the aggregate_relation_size() function.
> 
51a90,95
> 
> Wishlist:
>     - include size of serial sequence objects
>     - make pg_* functions include toast, indexes, 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 -r1.16 dbsize.c
24a25
> #include "utils/relcache.h"
36a38,39
> Datum aggregate_relation_size(PG_FUNCTION_ARGS);
> Datum indexes_size(PG_FUNCTION_ARGS);
44a48,49
> PG_FUNCTION_INFO_V1(aggregate_relation_size);
> PG_FUNCTION_INFO_V1(indexes_size);
283a289,387
> /*
>  *  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 indexes ... */
> 		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 indexes for 'relation'.
>  */
> Datum
> indexes_size(PG_FUNCTION_ARGS)
> {
> 	text       *relname = PG_GETARG_TEXT_P(0);
> 	RangeVar   *relrv;
> 	Relation    relation;
> 	int64       size = 0;
> 
> 	relrv = makeRangeVarFromNameList(
> 			textToQualifiedNameList(relname, "indexes_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 indexes.
>  */
290,291c394
< 	Oid			relnodeOid;
< 	Oid         tblspcOid;
---
> 	int64       size = 0;
296,299c399
< 
< 	tblspcOid  = relation->rd_rel->reltablespace;
< 	relnodeOid = relation->rd_rel->relfilenode;
< 
---
> 	size = calculate_size(relation, TRUE, TRUE, FALSE);
302c402
< 	PG_RETURN_INT64(calculate_relation_size(tblspcOid, relnodeOid));
---
> 	PG_RETURN_INT64(size);
304a405
> 
Index: contrib/dbsize/dbsize.sql.in
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/dbsize.sql.in,v
retrieving revision 1.4
diff -r1.4 dbsize.sql.in
8a9,16
> CREATE FUNCTION aggregate_relation_size (text) RETURNS bigint
>     AS 'MODULE_PATHNAME', 'aggregate_relation_size'
>     LANGUAGE C STRICT;
> 
> CREATE FUNCTION indexes_size (text) RETURNS bigint
>     AS 'MODULE_PATHNAME', 'indexes_size'
>     LANGUAGE C STRICT;
> 
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

Reply via email to