Thread: table and index size

Is there a way to determine the size of a table? an index?
I didn't see anything in the reference manual about this so just did a
'du' before and after creating and populating a table and then again after
creating an index on the table.  The numbers I got were shockingly low.

I created a table with two int4 columns and inserted 100 K rows.  The
change in disk usage was only 4.3 KBytes, or .17 bits per integer.

One of the columns had values ranging from 0 to 3.  The other had random
32 bit integers.  Making an index (btree) on either column used an
additional 1.8 KB, or .14 bits per row.

These numbers were a little surprising, so I du'ed / to see if the data
was being hidden somewhere, and got the same differences.

So, I guess there are two questions here.

1) Do these numbers seem reasonable to someone with a little more
Postgresql experience?

2) Are there commands to determine directly the size of a database, a
table and an index?

One further question, if anyone can comment.  I have the New Riders
PostgreSQL Essential Reference book.  It claims that in my database
directory I should find some files with plain text names.  As an example
they say that "$PGBASE/payroll_idx" is the name of the payroll index file.
All of the files in my database directory have opaque names like
"$PGBASE/16555/16475".  Is there a way to generate more human filenames or
is the New Riders book just off base on this?

Thanks,
 Damon

On Mon, 15 Jul 2002, Damon Fasching wrote:

> Is there a way to determine the size of a table? an index?

The pg_class table has the size of every object in (usually 8K) pages. So:

    SELECT relname, reltype, relpages, relpages / 128 AS MB
    FROM pg_class
    WHERE relname LIKE 'session%'

or whatever.

> I created a table with two int4 columns and inserted 100 K rows.  The
> change in disk usage was only 4.3 KBytes, or .17 bits per integer.

Something's wrong there. Did you sync? 430 KB I'd believe.

> 1) Do these numbers seem reasonable to someone with a little more
> Postgresql experience?

No, they're completely out to lunch. ints are 4 bytes. postgres
row overhead is around about 40 bytes or so.

> One further question, if anyone can comment.  I have the New Riders
> PostgreSQL Essential Reference book.  It claims that in my database
> directory I should find some files with plain text names.

It's based on an old version of postgres. Now we use the object
IDs. So change the above query:

    SELECT relname, reltype, relfilenode, relpages, relpages / 128 AS MB
    FROM pg_class
    WHERE relname LIKE 'session%'

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Curt Sampson <cjs@cynic.net> writes:
> On Mon, 15 Jul 2002, Damon Fasching wrote:
>> Is there a way to determine the size of a table? an index?

> The pg_class table has the size of every object in (usually 8K) pages. So:

>     SELECT relname, reltype, relpages, relpages / 128 AS MB
>     FROM pg_class
>     WHERE relname LIKE 'session%'

Note that those numbers are only up to date if you've vacuumed recently.

>> I created a table with two int4 columns and inserted 100 K rows.  The
>> change in disk usage was only 4.3 KBytes, or .17 bits per integer.

> Something's wrong there. Did you sync? 430 KB I'd believe.

I'd believe 4300 KB, but not anything much less than that.
I doubt sync has anything to do with it; I'd wonder whether he
was du'ing the right place with the right privileges.  $PGDATA is
normally not readable by anyone except the postgres user...

            regards, tom lane