If the C code for the prior dbsize patch is not acceptable for whatever
reason, here's a SQL-based patch to replace it. It's not a drop-in for
7.3/7.4 as I'd hoped, only an 8.1 patch. I believe it is functionally
equivalent to the C patch, but simpler, shorter, and probably a tad slower.
I also removed the README section on how to aggregate since it was
incomplete/incorrect (it didn't count toasted indices) and added a SQL
function that itemizes the size for a relation's table and index data
(helpful to us in identifying bloat, measuring performance, capacity
estimation, etc).
Ed
? contrib/dbsize/README.dbsize.aggregate_relation_components
? contrib/dbsize/dbsize.sql
? contrib/dbsize/dbsize.sql.in.aggregate_relation_components
? contrib/dbsize/dbsize.sql.in.using_pg_relation_size
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 28 Jan 2005 17:10:31 -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 on-disk size of a
! given database object in bytes:
***************
*** 5,6 ****
--- 5,10 ----
int8 relation_size(text)
+ int8 indexes_size(text)
+ int8 aggregate_relation_size(text)
+
+ setof record relation_size_components(text)
***************
*** 12,14 ****
! The first two functions:
--- 16,20 ----
! The first four functions take the name of the object (possibly
! schema-qualified for the latter three) and returns the size of the
! on-disk files in bytes.
***************
*** 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:
--- 22,32 ----
SELECT relation_size('pg_class');
+ SELECT indexes_size('pg_class');
+ SELECT aggregate_relation_size('pg_class');
+
+ The next function, relation_size_components(), returns a set of rows
+ showing the sizes of the relations constituting the input relation.
+
+ SELECT relation_size_components('pg_class');
! These functions take object OIDs:
***************
*** 24,49 ****
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:
!
! SELECT *,
! pg_size_pretty(tablesize+indexsize+toastsize+toastindexsize) AS totalsize
! FROM
! (SELECT pg_relation_size(cl.oid) AS tablesize,
! COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint
! FROM pg_index WHERE cl.oid=indrelid), 0) AS indexsize,
! CASE WHEN reltoastrelid=0 THEN 0
! ELSE pg_relation_size(reltoastrelid)
! END AS toastsize,
! CASE WHEN reltoastrelid=0 THEN 0
! ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct
! WHERE ct.oid = cl.reltoastrelid))
! END AS toastindexsize
! FROM pg_class cl
! WHERE relname = 'foo') ss;
!
! This sample query utilizes the helper function pg_size_pretty(int8),
! which formats the number of bytes into a convenient string using KB, MB,
! GB. It is also contained in this module.
--- 36,47 ----
+ The indexes_size() function returns the total size of the indices for a
+ relation, including any toasted indices.
+
+ The aggregate_relation_size() function returns the total size of the relation,
+ all its indices, and any toasted data.
+
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,
! use aggregate_relation_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 28 Jan 2005 17:10:32 -0000
***************
*** 1,2 ****
! CREATE FUNCTION database_size (name) RETURNS bigint
AS 'MODULE_PATHNAME', 'database_size'
--- 1,2 ----
! CREATE OR REPLACE FUNCTION database_size (name) RETURNS bigint
AS 'MODULE_PATHNAME', 'database_size'
***************
*** 4,10 ****
! CREATE FUNCTION relation_size (text) RETURNS bigint
! AS 'MODULE_PATHNAME', 'relation_size'
! LANGUAGE C STRICT;
!
! CREATE FUNCTION pg_tablespace_size(oid) RETURNS bigint
AS 'MODULE_PATHNAME', 'pg_tablespace_size'
--- 4,6 ----
! CREATE OR REPLACE FUNCTION pg_tablespace_size(oid) RETURNS bigint
AS 'MODULE_PATHNAME', 'pg_tablespace_size'
***************
*** 12,14 ****
! CREATE FUNCTION pg_database_size(oid) RETURNS bigint
AS 'MODULE_PATHNAME', 'pg_database_size'
--- 8,10 ----
! CREATE OR REPLACE FUNCTION pg_database_size(oid) RETURNS bigint
AS 'MODULE_PATHNAME', 'pg_database_size'
***************
*** 16,18 ****
! CREATE FUNCTION pg_relation_size(oid) RETURNS bigint
AS 'MODULE_PATHNAME', 'pg_relation_size'
--- 12,14 ----
! CREATE OR REPLACE FUNCTION pg_relation_size(oid) RETURNS bigint
AS 'MODULE_PATHNAME', 'pg_relation_size'
***************
*** 20,23 ****
! CREATE FUNCTION pg_size_pretty(bigint) RETURNS text
AS 'MODULE_PATHNAME', 'pg_size_pretty'
LANGUAGE C STRICT;
--- 16,132 ----
! CREATE OR REPLACE FUNCTION pg_size_pretty(bigint) RETURNS text
AS 'MODULE_PATHNAME', 'pg_size_pretty'
LANGUAGE C STRICT;
+
+ CREATE OR REPLACE FUNCTION relation_size (text) RETURNS bigint
+ AS 'MODULE_PATHNAME', 'relation_size'
+ LANGUAGE C STRICT;
+
+ CREATE OR REPLACE FUNCTION aggregate_relation_size (text) RETURNS bigint AS '
+ SELECT pg_relation_size(r.oid)
+ + COALESCE(pg_relation_size(t.oid), 0)::bigint
+ + COALESCE(pg_relation_size(ti.oid), 0)::bigint
+ + COALESCE(SUM(pg_relation_size(i.indexrelid)), 0)::bigint
+ + COALESCE(SUM(pg_relation_size(it.oid)), 0)::bigint
+ + COALESCE(SUM(pg_relation_size(iti.oid)), 0)::bigint AS bytes
+ FROM pg_class r
+ LEFT JOIN pg_class t ON (r.reltoastrelid = t.oid)
+ LEFT JOIN pg_class ti ON (t.reltoastidxid = ti.oid)
+ LEFT JOIN pg_index i ON (r.oid = i.indrelid)
+ LEFT JOIN pg_class ir ON (ir.oid = i.indexrelid)
+ LEFT JOIN pg_class it ON (ir.reltoastrelid = it.oid)
+ LEFT JOIN pg_class iti ON (it.reltoastidxid = iti.oid)
+ WHERE r.relname = \$1
+ GROUP BY r.oid, t.oid, ti.oid
+ ' LANGUAGE SQL;
+
+ CREATE OR REPLACE FUNCTION indexes_size (text) RETURNS bigint
+ AS '
+ SELECT COALESCE(SUM(pg_relation_size(ir.oid)), 0)::bigint
+ + COALESCE(SUM(pg_relation_size(it.oid)), 0)::bigint
+ + COALESCE(SUM(pg_relation_size(iti.oid)), 0)::bigint AS bytes
+ FROM pg_class r
+ LEFT JOIN pg_index i ON (r.oid = i.indrelid)
+ LEFT JOIN pg_class ir ON (ir.oid = i.indexrelid)
+ LEFT JOIN pg_class it ON (ir.reltoastrelid = it.oid)
+ LEFT JOIN pg_class iti ON (it.reltoastidxid = iti.oid)
+ WHERE r.relname = \$1
+ ' LANGUAGE SQL;
+
+ CREATE OR REPLACE FUNCTION relation_size_components (text) RETURNS SETOF RECORD
+ AS '
+ -- relation size
+ SELECT indexes_size(r.relname) AS indexes_size,
+ relation_size(r.relname) AS data_size,
+ aggregate_relation_size(r.relname) AS total_size,
+ r.relname, r.relkind, r.oid AS relid, r.relfilenode
+ FROM pg_class r
+ WHERE r.relname = \$1
+
+ UNION ALL
+
+ -- relation toast size
+ SELECT indexes_size(toast.relname) AS indexes_size,
+ relation_size(''pg_toast.''||toast.relname) AS data_size,
+ aggregate_relation_size(toast.relname) AS total_size,
+ toast.relname, toast.relkind, toast.oid AS relid, toast.relfilenode
+ FROM pg_class r, pg_class toast
+ WHERE r.reltoastrelid = toast.oid
+ AND r.relname = \$1
+
+ UNION ALL
+
+ -- relation toast index size
+ SELECT indexes_size(toastidxr.relname) AS indexes_size,
+ relation_size(''pg_toast.''||toastidxr.relname) AS data_size,
+ aggregate_relation_size(toastidxr.relname) AS total_size,
+ toastidxr.relname, toastidxr.relkind,
+ toastidxr.oid AS relid, toastidxr.relfilenode
+ FROM pg_class r, pg_index toastidx, pg_class toastidxr
+ WHERE r.relname = \$1
+ AND r.reltoastrelid = toastidx.indrelid
+ AND toastidx.indexrelid = toastidxr.oid
+
+ UNION ALL
+
+ -- relation indices size
+ SELECT indexes_size(idxr.relname) AS indexes_size,
+ relation_size(idxr.relname) AS data_size,
+ aggregate_relation_size(idxr.relname) AS total_size,
+ idxr.relname, idxr.relkind, idxr.oid AS relid, idxr.relfilenode
+ FROM pg_class r, pg_class idxr, pg_index idx
+ WHERE r.relname = \$1
+ AND r.oid = idx.indrelid
+ AND idx.indexrelid = idxr.oid
+
+ UNION ALL
+
+ -- relation indices toast size
+ SELECT indexes_size(idxtoastr.relname) AS indexes_size,
+ relation_size(''pg_toast.''||idxtoastr.relname) AS data_size,
+ aggregate_relation_size(idxtoastr.relname) AS total_size,
+ idxtoastr.relname, idxtoastr.relkind, idxtoastr.oid AS relid,
+ idxtoastr.relfilenode
+ FROM pg_class r, pg_class idxr, pg_index idx, pg_class idxtoastr
+ WHERE r.relname = \$1
+ AND r.oid = idx.indrelid
+ AND idx.indexrelid = idxr.oid
+ AND idxr.reltoastrelid = idxtoastr.oid
+
+ UNION ALL
+
+ -- relation indices toast index size
+ SELECT indexes_size(idxtoastidxr.relname) AS indexes_size,
+ relation_size(''pg_toast.''||idxtoastidxr.relname) AS data_size,
+ aggregate_relation_size(idxtoastidxr.relname) AS total_size,
+ idxtoastidxr.relname, idxtoastidxr.relkind,
+ idxtoastidxr.oid AS relid, idxtoastidxr.relfilenode
+ FROM pg_class r, pg_class idxr, pg_index idx, pg_class idxtoast,
+ pg_class idxtoastidxr
+ WHERE r.relname = \$1
+ AND r.oid = idx.indrelid
+ AND idx.indexrelid = idxr.oid
+ AND idxr.reltoastrelid = idxtoast.oid
+ AND idxtoast.reltoastrelid = idxtoastidxr.oid
+ ' LANGUAGE SQL;
+
---------------------------(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
