Table Size

This is a multi-part message in MIME format.

------=_NextPart_000_0009_01C3EA59.518D4EB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

How do I know table size?
------=_NextPart_000_0009_01C3EA59.518D4EB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Re: Table Size

Muhyiddin A.M Hayat typed this on 02/02/2004 09:26 PM:
> How do I know table size?

You can always dumb the table and check the file size.

-Robby

--
#-------------------------------------------------------
# Robby Russell, | Sr. Administrator / Lead Programmer
# Command Prompt, Inc. | http://www.commandprompt.com
# rrussell@commandprompt.com | Telephone: (503) 667.4564
#-------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Table Size

Muhyiddin A.M Hayat wrote:

> How do I know table size?

Someone else suggested dumping the table to find out how large it is.
If you wanted to know how large it would be when dumped, this is the
only way to find out, but it's not a reliable measurement of how much
space a table is currently using. Because dumps don't store indexes,
(and indexes can continue to grow), you will often find that a
compressed dump is many times smaller than the on-disk files which
belong to it. However, if you do a plain text dump, the dumped file may
very well be larger because everything is stored in ASCII.

The two methods below show on-disk sizes of tables, not including
indexes and TOASTED (stored out of line) columns.

METHOD 1:

You can learn approximately how much disk space it is using by doing:

SELECT relpages * 8192 AS size_in_bytes
FROM pg_class
WHERE
relnamespace = (SELECT oid FROM pg_namespace WHERE nspname =
'') AND
relname = '';

Notes:

(1) Use 'public' as the schema name unless you put your table in a
different schema.
(2) This value is only updated by the commands VACUUM, ANALYZE, and
CREATE INDEX. Thus, if you have been changing your table, run ANALYZE
before running this query. See
http://www.postgresql.org/docs/7.4/static/catalog-pg-class.h tml

METHOD 2:

Go use ls -l to look at the file in which your data is stored. Run this
command to find the file node number for a particular table:

SELECT relfilenode
FROM pg_class
WHERE
relnamespace = (SELECT oid FROM pg_namespace WHERE nspname =
'') AND
relname = '';

Then go do
ls -l /base/

Example:

ls -l /usr/local/pgsql/data/base/17384

Paul Tillotson



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Table Size

WHERE
relnamespace = (SELECT oid FROM pg_namespace WHERE nspname =
'') AND
relname = '';

can be replaced with

WHERE oid = 'schemaname.tablename'::regclass::oid

which will look up the oid for the table taking into account the current
schema_path settings.
(the cast to oid is unneccesary, but included here for clarity only)

--
Tom Hebbron
www.hebbron.com

"Paul Tillotson" wrote in message
news:40203D13.4000906@shentel.net...
> Muhyiddin A.M Hayat wrote:
>
>> How do I know table size?
>
> Someone else suggested dumping the table to find out how large it is. If
> you wanted to know how large it would be when dumped, this is the only way
> to find out, but it's not a reliable measurement of how much space a table
> is currently using. Because dumps don't store indexes, (and indexes can
> continue to grow), you will often find that a compressed dump is many
> times smaller than the on-disk files which belong to it. However, if you
> do a plain text dump, the dumped file may very well be larger because
> everything is stored in ASCII.
>
> The two methods below show on-disk sizes of tables, not including indexes
> and TOASTED (stored out of line) columns.
> METHOD 1:
>
> You can learn approximately how much disk space it is using by doing:
>
> SELECT relpages * 8192 AS size_in_bytes
> FROM pg_class
> WHERE
> relnamespace = (SELECT oid FROM pg_namespace WHERE nspname =
> '') AND
> relname = '';
>
> Notes:
>
> (1) Use 'public' as the schema name unless you put your table in a
> different schema.
> (2) This value is only updated by the commands VACUUM, ANALYZE, and CREATE
> INDEX. Thus, if you have been changing your table, run ANALYZE
> before running this query. See
> http://www.postgresql.org/docs/7.4/static/catalog-pg-class.h tml
>
> METHOD 2:
>
> Go use ls -l to look at the file in which your data is stored. Run this
> command to find the file node number for a particular table:
>
> SELECT relfilenode
> FROM pg_class
> WHERE
> relnamespace = (SELECT oid FROM pg_namespace WHERE nspname =
> '') AND
> relname = '';
>
> Then go do
> ls -l /base/
>
> Example:
>
> ls -l /usr/local/pgsql/data/base/17384
>
> Paul Tillotson
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings