From mallah@trade-india.com Fri Apr 26 14:48:52 2002 Received: from mail-relay.trade-india.com (unknown [203.196.129.235]) by postgresql.org (Postfix) with SMTP id 057C247591E for ; Fri, 26 Apr 2002 10:48:52 -0400 (EDT) Received: (qmail 8809 invoked from network); 26 Apr 2002 14:41:36 -0000 Received: from system11.trade-india-local.com (HELO trade-india.com) (192.168.0.11) by ptil-236-129-del.primus-india.net with SMTP; 26 Apr 2002 14:41:36 -0000 Message-ID: <3CC969AF.418C544B@trade-india.com> Date: Fri, 26 Apr 2002 20:22:31 +0530 From: Rajesh Kumar Mallah X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.4.2-2 i686) X-Accept-Language: en MIME-Version: 1.0 To: PostgResql SQL Mailing List Subject: abnormal size of the on-disk file. Content-Type: multipart/alternative; boundary="------------D0262001AC6D9334B4EDF9C2" X-Archive-Number: 200204/321 X-Sequence-Number: 7585 --------------D0262001AC6D9334B4EDF9C2 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi There, I have a small table companies (< 300 rows) and its on-disk size shows abnormal i check it as follows: SELECT relname,relfilenode from pg_class where relfilenode=30977; relname | relfilenode -----------+------------- companies | 30977 (1 row) ls -lsh 30977 487M -rw------- 1 postgres postgres 486M Apr 26 20:27 30977 bash-2.03$ i created a new table with the same content tradein_clients=> CREATE TABLE t_a as select * from companies; SELECT its on disk file is very small tradein_clients=> SELECT relname,relfilenode from pg_class where relname='t_a'; relname | relfilenode ---------+------------- t_a | 87812505 (1 row) bash-2.03$ ls -lsh 87812505 196k -rw------- 1 postgres postgres 192k Apr 26 20:27 87812505 bash-2.03$ Can anyone explain this .. does unvaccuming makes tables x2000 times largers?? regds mallah. --------------D0262001AC6D9334B4EDF9C2 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Hi There,

I have a small table  companies (< 300 rows)  and its on-disk size shows
abnormal
 

i check it as follows:

 SELECT relname,relfilenode  from pg_class where relfilenode=30977;
  relname  | relfilenode
-----------+-------------
 companies |       30977
(1 row)

ls -lsh  30977
487M -rw-------    1 postgres postgres     486M Apr 26 20:27 30977
bash-2.03$
 

i created a new table with the same content

tradein_clients=> CREATE  TABLE t_a as select * from companies;

SELECT

its on disk file is very small

tradein_clients=> SELECT relname,relfilenode  from pg_class where relname='t_a';
 relname | relfilenode
---------+-------------
 t_a     |    87812505
(1 row)

bash-2.03$ ls -lsh  87812505
196k -rw-------    1 postgres postgres     192k Apr 26 20:27 87812505
bash-2.03$

Can anyone explain this ..

does unvaccuming makes tables  x2000  times largers??

regds
mallah. --------------D0262001AC6D9334B4EDF9C2-- From sszabo@megazone23.bigpanda.com Mon Apr 29 17:06:57 2002 Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id D3D284760C0 for ; Mon, 29 Apr 2002 13:06:57 -0400 (EDT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 530A7475F1F for ; Mon, 29 Apr 2002 12:46:54 -0400 (EDT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 40169D5FA; Mon, 29 Apr 2002 09:46:59 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 35C605C01; Mon, 29 Apr 2002 09:46:59 -0700 (PDT) Date: Mon, 29 Apr 2002 09:46:59 -0700 (PDT) From: Stephan Szabo To: Rajesh Kumar Mallah Cc: PostgResql SQL Mailing List Subject: Re: abnormal size of the on-disk file. In-Reply-To: <3CC969AF.418C544B@trade-india.com> Message-ID: <20020429094521.G58196-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200204/362 X-Sequence-Number: 7626 On Fri, 26 Apr 2002, Rajesh Kumar Mallah wrote: > Hi There, > > I have a small table companies (< 300 rows) and its on-disk size shows > abnormal If you've been making modifications to the table rows and haven't been vacuuming you're going to have all the dead rows in the table as well as live ones. On 7.2 you'd need a vacuum full to clear the space at this point (you can use normal vacuum more frequently during normal operation to avoid needing the full). > does unvaccuming makes tables x2000 times largers??