> > On Thu, 2005-01-27 at 08:05 +0100, Michael Paesold wrote:
> > > Perhaps you could rename indices_size to indexes_size.
>
> Attached patch identical except for s/indices/indexes/g.
Attached is the same patch as context diff. (prior send from unregistered
email address)
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 27 Jan 2005 08:49:25 -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 indexes_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 indexes_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 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:
***************
*** 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, 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 -C1 -r1.16 dbsize.c
*** contrib/dbsize/dbsize.c 1 Jan 2005 05:43:05 -0000 1.16
--- contrib/dbsize/dbsize.c 27 Jan 2005 08:49:26 -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 indexes_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(indexes_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 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.
+ */
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 27 Jan 2005 08:49:26 -0000
***************
*** 8,9 ****
--- 8,17 ----
+ 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;
+
CREATE FUNCTION pg_tablespace_size(oid) RETURNS bigint
---------------------------(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
