Postgres Pro
Downloads
Home   >   mailing lists

Thread: \d+ not showing TOAST table size?

\d+ not showing TOAST table size?

From
Jon Nelson
Date:
I have a table with a fair bit of TOAST data in it.
I noticed that \d+ does /not/ include that information (but
pg_total_relation_size does).
Is that intentional? It seems a bit misleading, insofar as "\d+" feels
like it is meant to be a rough indication of the table size, but if
90% of the data is in TOAST then the *real* data stored is much more
than displayed.


--
Jon

Re: \d+ not showing TOAST table size?

From
Josh Kupershmidt
Date:
On Tue, Sep 20, 2011 at 1:26 PM, Jon Nelson <jnelson+pgsql@jamponi.net> wrote:
> I have a table with a fair bit of TOAST data in it.
> I noticed that \d+ does /not/ include that information (but
> pg_total_relation_size does).

I assume by "\d+" you meant "\dt+" (\d+ doesn't show sizes at all). On
version 9.0 and up, \dt+ uses pg_relation_size() internally, which
actually does include TOAST data as this comment in dbsize.c explains:

 * Calculate total on-disk size of a given table,
 * including FSM and VM, plus TOAST table if any.
 * Indexes other than the TOAST table's index are not included.

The extra space displayed by pg_total_relation_size() is from indexes
on the table.

> Is that intentional? It seems a bit misleading, insofar as "\d+" feels
> like it is meant to be a rough indication of the table size, but if
> 90% of the data is in TOAST then the *real* data stored is much more
> than displayed.

FYI, the implementation was agreed upon here:
  http://archives.postgresql.org/pgsql-hackers/2011-03/msg01268.php

There were some ideas tossed around in that thread about ways to also
include index size as well, which I do think would be nice to have. It
would be a challenge to format that information nicely, particularly
without messing up the tabular output of different types of relations
(e.g. "\dts+").

Josh

Re: \d+ not showing TOAST table size?

From
Tom Lane
Date:
Jon Nelson <jnelson+pgsql@jamponi.net> writes:
> I have a table with a fair bit of TOAST data in it.
> I noticed that \d+ does /not/ include that information (but
> pg_total_relation_size does).
> Is that intentional?

This is changed as of (IIRC) 9.1.

            regards, tom lane

Re: \d+ not showing TOAST table size?

From
Jon Nelson
Date:
On Tue, Sep 20, 2011 at 2:09 PM, Josh Kupershmidt <schmiddy@gmail.com> wrote:
> On Tue, Sep 20, 2011 at 1:26 PM, Jon Nelson <jnelson+pgsql@jamponi.net> wrote:
>> I have a table with a fair bit of TOAST data in it.
>> I noticed that \d+ does /not/ include that information (but
>> pg_total_relation_size does).
>
> I assume by "\d+" you meant "\dt+" (\d+ doesn't show sizes at all). On
> version 9.0 and up, \dt+ uses pg_relation_size() internally, which
> actually does include TOAST data as this comment in dbsize.c explains:

I meant "\d+".  I'm using 9.0.3 and "\d+" clearly shows sizes.
Per Tom Lane's comments later in the thread, the behavior of 9.0 (and
prior) matches my recollection. I just need to wait for 9.1.

Thanks!

--
Jon