On Sunday, January 22, 2012 12:26:22 am Dan Charrois wrote: > > Thank you Adrian. I think that you seem to have found the trouble. For > most of the TOAST tables I have, oid=relfilenode, but not for that one. I > found the table that has reltoastrelid linking to that huge TOAST table.. > and it makes some sense, since it is also the largest "regular" table too > (79 GB). The reason for that is found here: http://www.postgresql.org/docs/9.0/interactive/storage-file-layout.html " Caution Note that while a table's filenode often matches its OID, this is not necessarily the case; some operations, like TRUNCATE, REINDEX, CLUSTER and some forms of ALTER TABLE, can change the filenode while preserving the OID. Avoid assuming that filenode and table OID are the same. Also, for certain system catalogs including pg_class itself, pg_class.relfilenode contains zero. The actual filenode number of these catalogs is stored in a lower-level data structure, and can be obtained using the pg_relation_filenode() function. " > > So perhaps there are no orphaned TOAST tables after all, as now I know who > its parent is. The database still takes up a lot more physical storage > than I'd anticipated it would, but at least it appears as though that > space can be accounted for. > > It's too bad \dt+ doesn't take into account the related TOAST table too - > if it had, I would have expected that much disk space right from the > get-go, and never thought twice about it. In pre 9.1 databases you can use: " pg_total_relation_size accepts the OID or name of a table or toast table, and returns the total on-disk space used for that table, including all associated indexes. This function is equivalent to pg_table_size + pg_indexes_size. pg_table_size accepts the OID or name of a table and returns the disk space needed for that table, exclusive of indexes. (TOAST space, free space map, and visibility map are included.) " See details here: http://www.postgresql.org/docs/9.0/interactive/functions-admin.html > > Dan > -- > Syzygy Research & Technology > Box 83, Legal, AB T0G 1L0 Canada > Phone: 780-961-2213 -- Adrian Klaver adrian.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
