Re: [Treebase-devel] TreeBASE disk space

[William P. <wil...@ya...>]

On Apr 23, 2010, at 8:39 AM, William Piel wrote:

> Is there a way to output relative MB disc space used by different tables?

Running the query below, I see that matrixelement is the biggest  memory hog, taking up 81 GB. Is this also where disproportionate growth is coming from? (e.g. comparing this with production?). No other table is measured in GBs. 

regards,

Bill


SELECT c.relname AS "Relation", 
pg_size_pretty(pg_total_relation_size( CAST (c.relname AS text) )) AS "Relation Size"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid);

          Relation           | Relation Size 
-----------------------------+---------------
 distancematrixelement       | 8192 bytes
 cstreenode                  | 8192 bytes
 itemvalue                   | 8192 bytes
 leftchangeset_charstate     | 0 bytes
 geneticcodeset              | 16 kB
 help                        | 96 kB
 nodeattribute               | 8192 bytes
 matrixkind                  | 24 kB
 geneticcode                 | 16 kB
 matrix_itemdefinition       | 8192 bytes
 matrixdatatype              | 40 kB
 itemdefinition              | 24 kB
 matrixcolumn_itemdefinition | 8192 bytes
 matrixrow                   | 290 MB
 matrixcolumn                | 855 MB
 gapmode                     | 24 kB
 study_nexusfile             | 111 MB
 phylotreenode               | 151 MB
 stateset                    | 8192 bytes
 statemodifier               | 8192 bytes
 rightchangeset_charstate    | 0 bytes
 rowsegment                  | 48 kB
 studystatus                 | 24 kB
 phylochar                   | 3224 kB
 stateformat                 | 24 kB
 sub_matrix                  | 512 kB
 statechangeset              | 8192 bytes
 sub_taxonlabel              | 14 MB
 sub_treeblock               | 1096 kB
 submission                  | 424 kB
 taxonvariant                | 137 MB
 taxonset                    | 8192 bytes
 taxonlabelgroup             | 8192 bytes
 taxonset_taxon              | 8192 bytes
 treegroup                   | 8192 bytes
 typeset                     | 8192 bytes
 taxonlabelset_taxonlabel    | 92 MB
 taxonauthority              | 16 kB
 taxon                       | 79 MB
 taxonlabelset               | 1376 kB
 treeattribute               | 8192 bytes
 treegroup_phylotree         | 8192 bytes
 treekind                    | 24 kB
 treenodeedge                | 8192 bytes
 treepartition               | 8192 bytes
 treequality                 | 24 kB
 treeset_phylotree           | 8192 bytes
 treetype                    | 24 kB
 versionhistory              | 48 kB
 userrole                    | 24 kB
 usertyperrd_colrange        | 8192 bytes
 usertype                    | 8192 bytes
 polytcount                  | 24 kB
 algorithm                   | 560 kB
 chargroup_colrange          | 1456 kB
 analysis                    | 688 kB
 discretecharstate           | 14 MB
 analysisstep                | 720 kB
 phylotree                   | 8568 kB
 analyzeddata                | 1272 kB
 software                    | 448 kB
 ancestralstate              | 16 kB
 study                       | 424 kB
 ancstateset                 | 8192 bytes
 chargroup                   | 144 kB
 charweightset               | 24 kB
 citation                    | 4256 kB
 citation_author             | 784 kB
 charpartition               | 64 kB
 citation_editor             | 24 kB
 charset                     | 736 kB
 person                      | 736 kB
 charset_colrange            | 17 MB
 charweight                  | 88 kB
 charweight_colrange         | 680 kB
 codonchar3_colrange         | 80 kB
 codonnoncoding_colrange     | 440 kB
 codonpositionset            | 544 kB
 columnrange                 | 22 MB
 citationstatus              | 24 kB
 compound_element            | 16 kB
 coderecord_colrange         | 8192 bytes
 geneticcoderecord           | 8192 bytes
 codonchar1_colrange         | 112 kB
 matrix                      | 1024 kB
 codonchar2_colrange         | 112 kB
 taxonlabelpartition         | 8192 bytes
 contancstate_value          | 0 bytes
 taxonlink                   | 8192 bytes
 matrixelement               | 81 GB
 stepmatrixelement           | 8192 bytes
 treeblock                   | 1152 kB
 user                        | 40 kB
 taxonlabel                  | 30 MB
 treeset                     | 8192 bytes
 taxonlabelgroup_taxonlabel  | 0 bytes
 usertyperecord              | 8192 bytes
(97 rows)