X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 2EDC3D1BB43 for ; Thu, 4 Mar 2004 11:51:13 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 36604-01 for ; Thu, 4 Mar 2004 07:50:57 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 73C4ED1B911 for ; Thu, 4 Mar 2004 07:50:56 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i24Bot1Y079377 for ; Thu, 4 Mar 2004 11:50:55 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i24BbAgm077167 for pgsql-php@postgresql.org; Thu, 4 Mar 2004 11:37:10 GMT From: "Tom Hebbron" X-Newsgroups: comp.databases.postgresql.interfaces.php Subject: Re: Table Size Date: Thu, 4 Mar 2004 12:37:04 +0100 Organization: Hub.Org Networking Services Lines: 84 Message-ID: References: <002c01c3ea17$1acd10a0$4f00a8c0@MIDDINKS> <40203D13.4000906@shentel.net> X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2082 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2082 X-RFC2646: Format=Flowed; Response To: pgsql-php@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/30 X-Sequence-Number: 2629 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.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 = > '') 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 >