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