Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 9ED9A9FA344 for ; Wed, 17 Jan 2007 02:37:16 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 95886-08 for ; Wed, 17 Jan 2007 02:36:59 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sd-2317.dedibox.fr (sd-2317.dedibox.fr [88.191.17.192]) by postgresql.org (Postfix) with ESMTP id DBEF19FA339 for ; Wed, 17 Jan 2007 02:36:59 -0400 (AST) Received: from sd-2317.dedibox.fr (localhost.localdomain [127.0.0.1]) by sd-2317.dedibox.fr (Postfix) with ESMTP id 07EFFBA1C; Wed, 17 Jan 2007 07:40:27 +0100 (CET) Received: from [192.168.10.65] (ASte-Genev-Bois-153-1-57-54.w81-249.abo.wanadoo.fr [81.249.151.54]) by sd-2317.dedibox.fr (Postfix) with ESMTP id 4FE5EB16A; Wed, 17 Jan 2007 07:40:25 +0100 (CET) Message-ID: <45ADC425.8010900@lelarge.info> Date: Wed, 17 Jan 2007 07:37:25 +0100 From: Guillaume Lelarge User-Agent: Thunderbird 1.5.0.9 (X11/20070103) MIME-Version: 1.0 To: Richard Huxton Cc: Gauri Kanekar , pgsql-performance@postgresql.org Subject: Re: Table Size References: <7e4ba9550701160548v4980393buba8bbf6926e2b0a0@mail.gmail.com> <45AD2670.7050509@archonet.com> In-Reply-To: <45AD2670.7050509@archonet.com> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: ClamAV using ClamSMTP X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/251 X-Sequence-Number: 22852 Richard Huxton a écrit : > Gauri Kanekar wrote: >> Hi, >> >> Can anybody help me out to get following info of all the tables in a >> database. > > 1. Have you read up on the information schema and system catalogues? > http://www.postgresql.org/docs/8.2/static/catalogs.html > http://www.postgresql.org/docs/8.2/static/catalogs.html > > >> table_len >> tuple_count >> tuple_len > > 2. Not sure what the difference is between "len" and "count" here. > tuple_count is the number of live tuples. tuple_len is the length (in bytes) for all live tuples. >> tuple_percent > > 3. Or what this "percent" refers to. > tuple_percent is % of live tuple from all tuples in a table. >> dead_tuple_count >> dead_tuple_len >> dead_tuple_percent >> free_space >> free_percent > > 4. You might find some of the stats tables useful too: > http://www.postgresql.org/docs/8.2/static/monitoring-stats.html > Actually, these columns refer to the pgstattuple contrib module. This contrib module must be installed on the server (how you install it depends on your distro). Then, you have to create the functions on you database : psql -f /path/to/pgstattuple.sql your_database Right after that, you can query these columns : test=> \x Expanded display is on. test=> SELECT * FROM pgstattuple('pg_catalog.pg_proc'); -[ RECORD 1 ]------+------- table_len | 458752 tuple_count | 1470 tuple_len | 438896 tuple_percent | 95.67 dead_tuple_count | 11 dead_tuple_len | 3157 dead_tuple_percent | 0.69 free_space | 8932 free_percent | 1.95 Example from README.pgstattuple. Regards. -- Guillaume.