On Thursday February 3 2005 9:23, Ed L. wrote:
> Neil, do you have a verdict on this patch?
>
> On Friday January 28 2005 10:30, Ed L. wrote:
> > If the C code for the prior dbsize patch is not acceptable
> > for whatever reason, here's a SQL-based patch to replace it.
I submitted a dbsize patch on Jan 25, revised it twice per
concerns raised by Michael Paesold and Neil Conway ("indexes"
instead of "indices") and Andreas Pflug and Tom Lane (implement
in SQL instead of C) and resubmitted Jan 28. I've not received
any further communication regarding the patch. Please advise if
there are concerns. I've attached the patch again, slightly
cleaned up, in case it has fallen through the cracks.
Ed
Index: README.dbsize
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/dbsize/README.dbsize,v
retrieving revision 1.4
diff -C1 -r1.4 README.dbsize
*** README.dbsize 28 Sep 2004 19:35:43 -0000 1.4
--- README.dbsize 6 Feb 2005 15:06:19 -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,8 ----
int8 relation_size(text)
+ int8 indexes_size(text)
+ int8 aggregate_relation_size(text)
***************
*** 12,14 ****
! The first two functions:
--- 14,20 ----
! setof record relation_size_components(text)
!
! 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,27 ----
SELECT relation_size('pg_class');
+ SELECT indexes_size('pg_class');
+ SELECT aggregate_relation_size('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.
--- 31,113 ----
+ 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 related indexes or toast tables are not
! counted. To obtain the total size of a table including all indices and
! toasted data, use aggregate_relation_size().
!
! The last function, relation_size_components(), returns a set of rows
! showing the sizes of the component relations constituting the input
! relation.
!
! Examples
! ========
!
! I've loaded the following table with a little less than 3 MB of data for
! illustration:
!
! create table fat ( id serial, data varchar );
! create index fat_uidx on fat (id);
! create index fat_idx on fat (data);
!
! You can retrieve a rowset containing constituent sizes as follows:
!
! # SELECT relation_size_components('fat');
! relation_size_components
! ----------------------------------------------------
! (2088960,65536,2891776,fat,r,59383,59383)
! (32768,704512,737280,pg_toast_59383,t,59386,59386)
! (0,32768,32768,pg_toast_59383_index,i,59388,59388)
! (0,2039808,2039808,fat_idx,i,59389,59389)
! (0,49152,49152,fat_uidx,i,59911,59911)
! (5 rows)
!
! To see a more readable output of the rowset:
!
! SELECT *
! FROM relation_size_components('fat') AS (idxsize BIGINT,
! datasize BIGINT,
! totalsize BIGINT,
! relname NAME,
! kind "char",
! relid OID,
! node OID)
! ORDER BY totalsize;
!
! idxsize | datasize | totalsize | relname | kind | relid | node
! ---------+----------+-----------+----------------------+------+-------+-------
! 0 | 32768 | 32768 | pg_toast_59383_index | i | 59388 | 59388
! 0 | 49152 | 49152 | fat_uidx | i | 59911 | 59911
! 32768 | 704512 | 737280 | pg_toast_59383 | t | 59386 | 59386
! 0 | 2039808 | 2039808 | fat_idx | i | 59389 | 59389
! 2088960 | 65536 | 2891776 | fat | r | 59383 | 59383
! (5 rows)
!
! To see the sum total size of a relation:
!
! # select aggregate_relation_size('fat');
! aggregate_relation_size
! -------------------------
! 2891776
! (1 row)
!
! To see just the size of the uncompressed relation data:
!
! # select relation_size('fat');
! relation_size
! ---------------
! 65536
! (1 row)
!
! To see the size of all related indices:
!
! # select indexes_size('fat');
! indexes_size
! --------------
! 2088960
! (1 row)
Index: 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
*** dbsize.sql.in 28 Sep 2004 19:35:43 -0000 1.4
--- dbsize.sql.in 6 Feb 2005 15:06:19 -0000
***************
*** 23 ****
--- 23,132 ----
LANGUAGE C STRICT;
+
+ CREATE 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 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 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 7: don't forget to increase your free space map settings
