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:

Previous
From: Tom Lane
Date:
Subject: Re: plpgsql: open for execute - add USING clause
Next
From: Tom Lane
Date:
Subject: Re: Table size does not include toast size