From: Guillaume Lelarge <guillaume@lelarge.info>
To: Richard Huxton <dev@archonet.com>
Cc: Gauri Kanekar <meetgaurikanekar@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Table Size
Date: Wed, 17 Jan 2007 07:37:25 +0100
Message-ID: <45ADC425.8010900@lelarge.info> (raw)
In-Reply-To: <45AD2670.7050509@archonet.com>
References: <7e4ba9550701160548v4980393buba8bbf6926e2b0a0@mail.gmail.com>
<45AD2670.7050509@archonet.com>
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.
<!-- http://abs.traduc.org/http://lfs.traduc.org/http://docs.postgresqlfr.org/ -->
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: guillaume@lelarge.info, dev@archonet.com, meetgaurikanekar@gmail.com
Subject: Re: Table Size
In-Reply-To: <45ADC425.8010900@lelarge.info>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox