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
