Re: Table Size

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]



 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
> 




[Index of Archives]     [Postgresql General]     [Postgresql Admin]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Backpacking]     [Postgresql Jobs]

  Powered by Linux