WHERE
relnamespace = (SELECT oid FROM pg_namespace WHERE nspname =
'<schema_name>') AND
relname = '<table_name>';
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" <pntil@xxxxxxxxxxx> wrote in message
news:40203D13.4000906@xxxxxxxxxxxxxx
> 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 =
> '<schema_name>') AND
> relname = '<table_name>';
>
> 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
> <table_name> before running this query. See
> http://www.postgresql.org/docs/7.4/static/catalog-pg-class.html
>
> 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 =
> '<schema_name>') AND
> relname = '<table_name>';
>
> Then go do
> ls -l <your postgres data directory>/base/<file node number>
>
> 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
>
