diff options
| -rw-r--r-- | contrib/dbsize/README.dbsize | 120 | ||||
| -rw-r--r-- | contrib/dbsize/dbsize.sql.in | 109 |
2 files changed, 201 insertions, 28 deletions
diff --git a/contrib/dbsize/README.dbsize b/contrib/dbsize/README.dbsize index f1b60de75fc..e4b92553a41 100644 --- a/contrib/dbsize/README.dbsize +++ b/contrib/dbsize/README.dbsize @@ -1,8 +1,10 @@ -This module contains several functions that report the size of a given -database object: +This module contains several functions that report the on-disk size of a +given database object in bytes: int8 database_size(name) int8 relation_size(text) + int8 indexes_size(text) + int8 total_relation_size(text) int8 pg_database_size(oid) int8 pg_relation_size(oid) @@ -10,42 +12,104 @@ database object: text pg_size_pretty(int8) -The first two functions: + 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. SELECT database_size('template1'); SELECT relation_size('pg_class'); + SELECT indexes_size('pg_class'); + SELECT total_relation_size('pg_class'); -take the name of the object (possibly schema-qualified, for relation_size), -while these functions take object OIDs: +These functions take object OIDs: SELECT pg_database_size(1); -- template1 database SELECT pg_relation_size(1259); -- pg_class table size SELECT pg_tablespace_size(1663); -- pg_default tablespace +The indexes_size() function returns the total size of the indices for a +relation, including any toasted indices. + +The total_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 -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. +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 total_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 total_relation_size('fat'); + total_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) To install, just run make; make install. Then load the functions into any database using dbsize.sql. diff --git a/contrib/dbsize/dbsize.sql.in b/contrib/dbsize/dbsize.sql.in index 17aeae2c04f..6d08d289c01 100644 --- a/contrib/dbsize/dbsize.sql.in +++ b/contrib/dbsize/dbsize.sql.in @@ -21,3 +21,112 @@ CREATE FUNCTION pg_relation_size(oid) RETURNS bigint CREATE FUNCTION pg_size_pretty(bigint) RETURNS text AS 'MODULE_PATHNAME', 'pg_size_pretty' LANGUAGE C STRICT; + +CREATE FUNCTION total_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, + total_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, + total_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, + total_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, + total_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, + total_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, + total_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; + |