Quickly calculating row size of a table?

[Jared Evans]

Is there a way for me to quickly calculate the maximum size of a row
for a table?  I wanted to know if there was an automatic way to do it
before I do it manually.


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly

[Jim C. Nasby]

On Mon, Oct 17, 2005 at 04:42:15PM -0700, Jared Evans wrote:
> Is there a way for me to quickly calculate the maximum size of a row
> for a table?  I wanted to know if there was an automatic way to do it
> before I do it manually.

Well, if the table is well-vacuumed, SELECT relpages*8192/reltuples from
pg_class will give you a good idea (assuming a default 8K page size),
but of course it's not perfect.

The only way I know of to get row length info for certain is vacuum full
verbose:

decibel=# vacuum full verbose rrs;
INFO:  vacuuming "rrs.rrs"
INFO:  "rrs": found 0 removable, 7 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 61 to 73 bytes long.
--
Jim C. Nasby, Sr. Engineering Consultant      [hidden email]
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Tom Lane-2]

"Jim C. Nasby" <[hidden email]> writes:
> The only way I know of to get row length info for certain is vacuum full
> verbose:

See also contrib/pgstattuple.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly

[Jared Evans]

Thanks very much for both your posts.

I tried both:

First, I performed a full vacuum on the entire database then

dbn=# SELECT relname, relpages*8192/reltuples from pg_class where
reltuples <> 0 and relname not like 'pg%';

atablename                |  2047.95

The first SQL statement gave me a rough idea of the tuple size while
the second SQL statement seemed to give out more details about the
table and its tuples.

dbn=# select 'atablename' as table_name, * from
pgstattuple('"public"."atablename"');

-[ RECORD 1 ]------+----------
table_name         | atablename
table_len          | 160137216
tuple_count        | 78194
tuple_len          | 129868251
tuple_percent      | 81.1
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 29419740
free_percent       | 18.37

129868251 / 78194 = 1660.85

There's still a difference between the two reported tuple size of
atablename: 2047.95 - 1660.85 = 387.10

Can someone shed some more light on this and which one more closely
approximates the size of the tuples?

Jared


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Tom Lane-2]

"Jared Evans" <[hidden email]> writes:
> dbn=# SELECT relname, relpages*8192/reltuples from pg_class where
> reltuples <> 0 and relname not like 'pg%';

That calculation lumps free space (and page header overhead and so on)
into the size of the tuples.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly