Bernd Helmle wrote:
These are two new functions pg_table_size() and pg_indexes_size().
This patch also changes pg_total_relation_size() to be a shorthand for
pg_table_size() + pg_indexes_size().
Attached is a test program to exercise these new functions. I
thoroughly abuse generate_series and arrays to create a table with a few
megabytes of both regular and TOAST-ed text, and with two indexes on
it. Here's the results from a sample run (it's random data so each run
will be a bit different):
pg_relation_size | 11,755,520
pages_size | 11,755,520
toast_and_fsm | 22,159,360
pg_table_size | 33,914,880
pg_indexes_size | 524,288
pkey | 262,144
i | 262,144
pg_total_relation_size | 34,439,168
computed_total | 34,439,168
This seems to work as expected. You can see that pg_relation_size gives
a really misleading value for this table, whereas the new pg_table_size
does what DBAs were asking for here. Having pg_indexes_size around is
handy too. I looked over the code a bit, everything in the patch looks
clean too.
The only question I'm left with after browsing the patch and staring at
the above results is whether it makes sense to expose a pg_toast_size
function. That would make the set available here capable of handling
almost every situation somebody might want to know about, making this
area completely done as I see it. In addition to being a useful
shorthand on its own, that would then allow you to indirectly compute
just the FSM size, which seems like an interesting number to know as
feedback on what VACUUM is up to. It's easy enough to add, too: the
calculate_toast_table_size code needed is already in the patch, just
have to add another external function to expose it.
I don't think there's any useful case for further exposing the two
component parts of the toast size. If you're enough of a hacker to know
what to do with those, you can certainly break them down yourself.
--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.com
\x
DROP TABLE test;
CREATE TABLE test(s SERIAL PRIMARY KEY,d TEXT);
CREATE INDEX i on TEST(s);
INSERT INTO test(d) SELECT
array_to_string(array(
SELECT
chr(ascii('A') + (random() * 64)::integer)
FROM generate_series(1,20000)),'') -- Size
FROM generate_series(1,1000); -- Rows
INSERT INTO test(d) SELECT
array_to_string(array(
SELECT
chr(ascii('A') + (random() * 64)::integer)
FROM generate_series(1,1000)),'') -- Size
FROM generate_series(1,10000); -- Rows
--insert into test (d) SELECT repeat('xyz123'::text,(1+random() * 1000)::integer) FROM generate_series(1,100000);
ANALYZE test;
SELECT pg_relation_size(relname::regclass),relpages * 8192 AS pages_size FROM pg_class where relname='test';
SELECT pg_table_size('test'::regclass) - pg_relation_size('test'::regclass) AS toast_and_fsm;
SELECT pg_table_size('test'::regclass);
SELECT pg_indexes_size('test'::regclass),pg_relation_size('test_pkey'::regclass) as pkey,pg_relation_size('i'::regclass) AS i;
SELECT pg_total_relation_size('test'::regclass),pg_table_size('test'::regclass)+pg_indexes_size('test'::regclass) AS computed_total;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
