From middink@indo.net.id Tue Feb 3 05:33:20 2004 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 70AABD1D489 for ; Tue, 3 Feb 2004 05:33:20 +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 53030-04 for ; Tue, 3 Feb 2004 01:33:17 -0400 (AST) Received: from smtp-1.indo.net.id (smtp-1.indo.net.id [202.159.32.91]) by svr1.postgresql.org (Postfix) with SMTP id 80316D1D432 for ; Tue, 3 Feb 2004 01:33:14 -0400 (AST) Received: (qmail 25773 invoked from network); 3 Feb 2004 12:33:07 +0700 Received: from smtp.indo.net.id (202.159.32.81) by smtp-1.indo.net.id with SMTP; 3 Feb 2004 12:33:07 +0700 Received: (qmail 24783 invoked from network); 3 Feb 2004 12:33:07 +0700 Received: from unknown (HELO smtp.surabaya.indo.net.id) (202.159.51.117) by smtp.indo.net.id with SMTP; 3 Feb 2004 12:33:07 +0700 Received: from smtp [202.159.51.117] by smtp.surabaya.indo.net.id (SMTPD32-7.10) id A79E4E200F8; Tue, 03 Feb 2004 11:46:22 +0700 Received: FROM MIDDINKS BY smtp ; Tue Feb 03 11:46:20 2004 +0700 Message-ID: <002c01c3ea17$1acd10a0$4f00a8c0@MIDDINKS> From: "Muhyiddin A.M Hayat" To: Subject: Table Size Date: Tue, 3 Feb 2004 13:26:23 +0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0009_01C3EA59.518D4EB0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=BAYES_50, HTML_70_80, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200402/13 X-Sequence-Number: 2561 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
How do I know table=20 size?
------=_NextPart_000_0009_01C3EA59.518D4EB0-- From rrussell@commandprompt.com Tue Feb 3 17:56:30 2004 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 8A853D1C513 for ; Tue, 3 Feb 2004 17:56:30 +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 45971-04 for ; Tue, 3 Feb 2004 13:56:24 -0400 (AST) Received: from hosting.commandprompt.com (215.commandprompt.com [207.173.200.215]) by svr1.postgresql.org (Postfix) with ESMTP id 5FBFBD1D2D0 for ; Tue, 3 Feb 2004 13:56:23 -0400 (AST) Received: from commandprompt.com (dsl093-038-087.pdx1.dsl.speakeasy.net [66.93.38.87]) (authenticated) by hosting.commandprompt.com (8.11.6/8.11.6) with ESMTP id i13Htsm12724; Tue, 3 Feb 2004 09:55:54 -0800 Message-ID: <401FE0A4.20003@commandprompt.com> Date: Tue, 03 Feb 2004 09:55:48 -0800 From: Robby Russell Organization: Command Prompt, Inc. User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6b) Gecko/20031205 Thunderbird/0.4 X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Muhyiddin A.M Hayat" Cc: pgsql-php@postgresql.org Subject: Re: Table Size References: <002c01c3ea17$1acd10a0$4f00a8c0@MIDDINKS> In-Reply-To: <002c01c3ea17$1acd10a0$4f00a8c0@MIDDINKS> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/16 X-Sequence-Number: 2564 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 #------------------------------------------------------- From pntil@shentel.net Wed Feb 4 00:30:14 2004 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 5E260D1C9AB for ; Wed, 4 Feb 2004 00:30:14 +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 15683-08 for ; Tue, 3 Feb 2004 20:30:12 -0400 (AST) Received: from seahorse.shentel.net (seahorse.shentel.net [204.111.11.44]) by svr1.postgresql.org (Postfix) with ESMTP id E053CD1C517 for ; Tue, 3 Feb 2004 20:30:07 -0400 (AST) Received: from shentel.net (ha96s006.d.shentel.net [204.111.96.6]) by seahorse.shentel.net (8.11.7/8.11.7) with ESMTP id i140U5425256 for ; Tue, 3 Feb 2004 19:30:06 -0500 Message-ID: <40203D13.4000906@shentel.net> Date: Tue, 03 Feb 2004 19:30:11 -0500 From: Paul Tillotson User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030624 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-php@postgresql.org Subject: Re: Table Size References: <002c01c3ea17$1acd10a0$4f00a8c0@MIDDINKS> In-Reply-To: <002c01c3ea17$1acd10a0$4f00a8c0@MIDDINKS> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/17 X-Sequence-Number: 2565 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 From news_user@hebbron.com Thu Mar 4 11:51:13 2004 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 >