February 2nd, 2010 by depesz | Tags: , , , , , , , , | Comments Off on Waiting for 9.0 – table and index sizes
Did it help? If yes - maybe you can help me?

On 19th of January Tom Lane committed really brilliant patch:

Log Message:
-----------
Add pg_table_size() and pg_indexes_size() to provide more user-friendly
wrappers around the pg_relation_size() function.
 
Bernd Helmle, reviewed by Greg Smith

Why is it brilliant? Because I'm lazy. And I think it's a virtue, and not flaw.

Let's consider this example:

create table x (
    id serial primary key,
    text1 text,
    text2 text,
    text3 text
);

Table is pretty simple, and so is data:

insert into x (text1, text2, text3)
    select
        repeat( 'text1:' || i, 10 ),
        repeat( 'text2:' || i, 100),
        repeat( 'text3:' || i, 1000)
    from generate_series( 1, 500000 ) i;

But, let's check how big the table really is.

Theoretically I could:

# select pg_relation_size('x');
 pg_relation_size
------------------
        816275456
(1 row)

But that's not real true. There is also TOAST. Of course I could:

# select pg_total_relation_size('x');
 pg_total_relation_size
------------------------
              827760640
(1 row)

But this also doesn't solve my problem, as it contains also size of indexes.

Of course, in my trivial example I can:

# select pg_total_relation_size('x') - pg_total_relation_size('x_pkey');
 ?column?
-----------
 816504832
(1 row)

But just imagine how “fun" it would be if the table was more realistic – more columns and 12 indexes on it.

But now, we have these 2 new function. Which solve the problem once and for all:

# select pg_table_size('x'), pg_indexes_size('x');
 pg_table_size | pg_indexes_size
---------------+-----------------
     816504832 |        11255808
(1 row)

Simple, and to the point. No more guessing, finding name of toast table, or getting summarized size of all indexes on given table. Thank you Bernd, I really appreciate it.

Sorry, comments for this post are disabled.