Re: Table size does not include toast size - Mailing list pgsql-hackers
From | Greg Smith |
---|---|
Subject | Re: Table size does not include toast size |
Date | |
Msg-id | 4B552501.2040604@2ndquadrant.com Whole thread Raw |
In response to | Re: Table size does not include toast size (Bernd Helmle <mailings@oopsware.de>) |
Responses |
Re: Table size does not include toast size
(Tom Lane <tgl@sss.pgh.pa.us>)
|
List | pgsql-hackers |
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 greg@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;
pgsql-hackers by date: