> > 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. 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 4: Don't 'kill -9' the postmaster