[Treebase-guts] SF.net SVN: treebase:[776] trunk/treebase-core/db

[<sfr...@us...>]

Revision: 776
          http://treebase.svn.sourceforge.net/treebase/?rev=776&view=rev
Author:   sfrgpiel
Date:     2011-03-21 20:06:58 +0000 (Mon, 21 Mar 2011)

Log Message:
-----------
These are modifications to reduce the footprint of the database and increase the performance by creating new indices and removing matrixelement records for all discrete character matrices (which at this instance includes all matrices in the database). This deletion should be performed only after a new build has been applied to production, seeing as only the new build will not create new matrixelement records. 

Added Paths:
-----------
    trunk/treebase-core/db/cleaning/2011-03-21_truncate_matrixelement_table/
    trunk/treebase-core/db/cleaning/2011-03-21_truncate_matrixelement_table/README.txt
    trunk/treebase-core/db/cleaning/2011-03-21_truncate_matrixelement_table/truncate_matrixelement.sql
    trunk/treebase-core/db/schema/patches/0006_create-indices.sql

Added: trunk/treebase-core/db/cleaning/2011-03-21_truncate_matrixelement_table/README.txt
===================================================================
--- trunk/treebase-core/db/cleaning/2011-03-21_truncate_matrixelement_table/README.txt	                        (rev 0)
+++ trunk/treebase-core/db/cleaning/2011-03-21_truncate_matrixelement_table/README.txt	2011-03-21 20:06:58 UTC (rev 776)
@@ -0,0 +1,11 @@
+Serious performance and stability issues are predicted to arise because the 
+matrixelement table is highly normalized and requires and excessive footprint 
+when storing data matrices. The c. 6000+ matrices in TreeBASE are taking up 
+nearly 200GB of space in this table alone. Empirical testing determined that 
+the contents of the matrixelement table are not used when downloading discrete 
+character data (whether as NEXUS or as NeXML) because the information is 
+obtained from the symbolstring field in the matrixrow table. This is not the case
+for matrices of datatype continuous. Since there are no matrices of this type
+currently in TreeBASE, we propose to delete all records in the matrixelement table 
+and modify the code so as not to create new ones except in cases of continuous data
+types. 
\ No newline at end of file

Added: trunk/treebase-core/db/cleaning/2011-03-21_truncate_matrixelement_table/truncate_matrixelement.sql
===================================================================
--- trunk/treebase-core/db/cleaning/2011-03-21_truncate_matrixelement_table/truncate_matrixelement.sql	                        (rev 0)
+++ trunk/treebase-core/db/cleaning/2011-03-21_truncate_matrixelement_table/truncate_matrixelement.sql	2011-03-21 20:06:58 UTC (rev 776)
@@ -0,0 +1,20 @@
+-- make a backup of the matrixelement table just in 
+-- case, edit the -h and database name as needed:
+-- pg_dump -U treebase_app -h treebase.nescent.org -t matrixelement -a -O treebaseprod > matrixelement_bkup.sql
+
+-- remove foreign key constraints
+ALTER TABLE ONLY compound_element DROP CONSTRAINT compound_element_fkto_compound;
+ALTER TABLE ONLY compound_element DROP CONSTRAINT compound_element_fkto_element;
+ALTER TABLE ONLY itemvalue DROP CONSTRAINT itemvalue_fkto_element;
+ALTER TABLE ONLY statemodifier DROP CONSTRAINT statemodifier_fkto_element;
+
+-- delete all records in matrixelement. Be sure to use TRUNCATE instead 
+-- of DELETE FROM because this makes it infinitely faster
+TRUNCATE matrixelement;
+
+-- reapply all foreign key constraints
+ALTER TABLE ONLY compound_element ADD CONSTRAINT compound_element_fkto_compound FOREIGN KEY (compound_id) REFERENCES matrixelement(matrixelement_id);
+ALTER TABLE ONLY compound_element ADD CONSTRAINT compound_element_fkto_element FOREIGN KEY (element_id) REFERENCES matrixelement(matrixelement_id);
+ALTER TABLE ONLY itemvalue ADD CONSTRAINT itemvalue_fkto_element FOREIGN KEY (element_id) REFERENCES matrixelement(matrixelement_id);
+ALTER TABLE ONLY statemodifier ADD CONSTRAINT statemodifier_fkto_element FOREIGN KEY (element_id) REFERENCES matrixelement(matrixelement_id);
+

Added: trunk/treebase-core/db/schema/patches/0006_create-indices.sql
===================================================================
--- trunk/treebase-core/db/schema/patches/0006_create-indices.sql	                        (rev 0)
+++ trunk/treebase-core/db/schema/patches/0006_create-indices.sql	2011-03-21 20:06:58 UTC (rev 776)
@@ -0,0 +1,7 @@
+insert into versionhistory(patchnumber, patchlabel, patchdescription) 
+       values (6, 'create-indices', 
+       'Create additional indices to improve query performance.'); 
+
+CREATE INDEX discretecharstate_phylochar_id_idx ON discretecharstate USING btree (phylochar_id);
+CREATE INDEX matrixcolumn_matrix_id_idx ON matrixcolumn USING btree (matrix_id);
+


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.