From 4f15699d706130540f7cf1ed4f995b61cf50b09c Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 19 Jan 2010 05:50:18 +0000 Subject: Add pg_table_size() and pg_indexes_size() to provide more user-friendly wrappers around the pg_relation_size() function. Bernd Helmle, reviewed by Greg Smith --- doc/src/sgml/func.sgml | 128 +++++++++++++++++++++++++++++++++---------------- 1 file changed, 86 insertions(+), 42 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 49ca0874677..3143767dc5e 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ - + Functions and Operators @@ -13173,12 +13173,12 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); text Get last transaction log location received and synced to disk during - streaming recovery. If streaming recovery is still in progress - this will increase monotonically. If streaming recovery has completed - then this value will remain static at the value of the last WAL record - received and synced to disk during that recovery. When the server has - been started without a streaming recovery then the return value will be - InvalidXLogRecPtr (0/0). + streaming recovery. If streaming recovery is still in progress + this will increase monotonically. If streaming recovery has completed + then this value will remain static at the value of the last WAL record + received and synced to disk during that recovery. When the server has + been started without a streaming recovery then the return value will be + InvalidXLogRecPtr (0/0). @@ -13187,11 +13187,11 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); text Get last transaction log location replayed during recovery. - If recovery is still in progress this will increase monotonically. - If recovery has completed then this value will remain static at - the value of the last WAL record applied during that recovery. - When the server has been started normally without a recovery - then the return value will be InvalidXLogRecPtr (0/0). + If recovery is still in progress this will increase monotonically. + If recovery has completed then this value will remain static at + the value of the last WAL record applied during that recovery. + When the server has been started normally without a recovery + then the return value will be InvalidXLogRecPtr (0/0). @@ -13207,19 +13207,25 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); pg_column_size - pg_database_size + pg_total_relation_size - pg_relation_size + pg_table_size - pg_size_pretty + pg_indexes_size + + + pg_database_size pg_tablespace_size - pg_total_relation_size + pg_relation_size + + + pg_size_pretty @@ -13238,44 +13244,48 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); - pg_database_size(oid) + pg_total_relation_size(regclass) bigint - Disk space used by the database with the specified OID + + Total disk space used by the table with the specified OID or name, + including all indexes and TOAST data + - pg_database_size(name) + pg_table_size(regclass) bigint - Disk space used by the database with the specified name + + Disk space used by the table with the specified OID or name, + excluding indexes (but including TOAST, free space map, and visibility + map) + - pg_relation_size(relation regclass, fork text) + pg_indexes_size(regclass) bigint - Disk space used by the specified fork ('main', - 'fsm' or 'vm') - of the table or index with the specified OID or name + Total disk space used by indexes attached to the table with the + specified OID or name - pg_relation_size(relation regclass) + pg_database_size(oid) bigint - - Shorthand for pg_relation_size(..., 'main') - + Disk space used by the database with the specified OID - pg_size_pretty(bigint) + pg_database_size(name) - text - Converts a size in bytes into a human-readable format with size units + bigint + Disk space used by the database with the specified name @@ -13293,14 +13303,31 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); - pg_total_relation_size(regclass) + pg_relation_size(relation regclass, fork text) bigint - Total disk space used by the table with the specified OID or name, - including indexes and TOAST data + Disk space used by the specified fork ('main', + 'fsm' or 'vm') + of the table or index with the specified OID or name + + + pg_relation_size(relation regclass) + + bigint + + Shorthand for pg_relation_size(..., 'main') + + + + + pg_size_pretty(bigint) + + text + Converts a size in bytes into a human-readable format with size units +
@@ -13310,6 +13337,26 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); data value. + + pg_total_relation_size accepts the OID or name of a + table or toast table, and returns the total on-disk space used for + that table, including all associated indexes. This function is + equivalent to pg_table_size + + pg_indexes_size. + + + + pg_table_size accepts the OID or name of a table and + returns the disk space needed for that table, exclusive of indexes. + (TOAST space, free space map, and visibility map are included.) + + + + pg_indexes_size accepts the OID or name of a table and + returns the total disk space used by all the indexes attached to that + table. + + pg_database_size and pg_tablespace_size accept the OID or name of a database or tablespace, and return the total @@ -13318,14 +13365,17 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); pg_relation_size accepts the OID or name of a table, index or - toast table, and returns the size in bytes. Specifying + toast table, and returns the on-disk size in bytes. Specifying 'main' or leaving out the second argument returns the size of the main data fork of the relation. Specifying 'fsm' returns the size of the Free Space Map (see ) associated with the relation. Specifying 'vm' returns the size of the Visibility Map (see ) associated with the - relation. + relation. Note that this function shows the size of only one fork; + for most purposes it is more convenient to use the higher-level + functions pg_total_relation_size or + pg_table_size. @@ -13334,12 +13384,6 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); appropriate. - - pg_total_relation_size accepts the OID or name of a - table or toast table, and returns the size in bytes of the data - and all associated indexes and toast tables. - - The functions shown in provide native access to -- cgit v1.2.3