Menu
▾
▴
[Treebase-devel] Fields that need indices
|
From: William P. <wil...@ya...> - 2010-02-03 16:51:20
|
Because we have reverted to an older instance of the data (and schema), there are some changes that we may need to make. One is to create indexes on fields that are likely to be queried a lot using something like "CREATE INDEX taxonlabel_i ON taxonlabel USING btree (taxonlabel);". Is this true that we need these indices? Or is the reason we haven't already indexed commonly searched fields because of something to do with how hibernate interacts with the database?
At the bottom is a list of all our existing indices -- although all the PKs are covered (and I assume that FKs are automatically indexed too, no?), other fields would seem to benefit from an index.
Of the top of my head, I would assume that the following fields need to be indexed:
Table Field
-------- -------
taxon name
taxonvariant fullname
taxonlabel taxonlabel
citation title
citation abstract
person lastname
My credentials don't allow me to alter the database schema. Shall I add this as an artifact? The changes should happen to both dev and stage (and perhaps the original schema installation script for instantiating a new TreeBASE).
bp
SELECT n.nspname AS "Schema", t.relname AS "Table", c.relname AS "Index"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
JOIN pg_catalog.pg_class t ON i.indrelid = t.oid
WHERE c.relkind = 'i'
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY n.nspname, t.relname, c.relname;
Schema | Table | Index
--------+-----------------------------+--------------------------------------------
public | algorithm | algorithm_pkey
public | analysis | analysis_pkey
public | analysis | analysis_study_id_idx
public | analysisstep | analysisstep_pkey
public | analyzeddata | analyzeddata_pkey
public | ancestralstate | ancestralstate_pkey
public | ancstateset | ancstateset_pkey
public | chargroup | chargroup_pkey
public | chargroup_colrange | chargroup_colrange_columnrange_id_key
public | charpartition | charpartition_pkey
public | charset | charset_pkey
public | charset_colrange | charset_colrange_columnrange_id_key
public | charweight | charweight_pkey
public | charweight_colrange | charweight_colrange_columnrange_id_key
public | charweightset | charweightset_pkey
public | citation | citation_pkey
public | citation_author | citation_author_pkey
public | citation_editor | citation_editor_pkey
public | citationstatus | citationstatus_pkey
public | coderecord_colrange | coderecord_colrange_columnrange_id_key
public | codonchar1_colrange | codonchar1_colrange_columnrange_id_key
public | codonchar2_colrange | codonchar2_colrange_columnrange_id_key
public | codonchar3_colrange | codonchar3_colrange_columnrange_id_key
public | codonnoncoding_colrange | codonnoncoding_colrange_columnrange_id_key
public | codonpositionset | codonpositionset_pkey
public | columnrange | columnrange_pkey
public | compound_element | compound_element_element_id_key
public | compound_element | compound_element_pkey
public | cstreenode | cstreenode_pkey
public | discretecharstate | discretecharstate_phylochar_id_idx
public | discretecharstate | discretecharstate_pkey
public | distancematrixelement | distancematrixelement_pkey
public | gapmode | gapmode_pkey
public | geneticcode | geneticcode_pkey
public | geneticcoderecord | geneticcoderecord_pkey
public | geneticcodeset | geneticcodeset_pkey
public | help | help_pkey
public | itemdefinition | itemdefinition_pkey
public | itemvalue | itemvalue_pkey
public | matrix | matrix_pkey
public | matrix_itemdefinition | matrix_itemdefinition_pkey
public | matrixcolumn | matrixcolumn_phylochar_id_idx
public | matrixcolumn | matrixcolumn_pkey
public | matrixcolumn_itemdefinition | matrixcolumn_itemdefinition_pkey
public | matrixdatatype | matrixdatatype_phylochar_id_idx
public | matrixdatatype | matrixdatatype_pkey
public | matrixelement | matrixelement_discretecharstate_id_idx
public | matrixelement | matrixelement_itemdefinition_id_idx
public | matrixelement | matrixelement_pkey
public | matrixkind | matrixkind_pkey
public | matrixrow | matrixrow_matrix_id_idx
public | matrixrow | matrixrow_pkey
public | matrixrow | matrixrow_taxonlabel_id_idx
public | nodeattribute | nodeattribute_pkey
public | person | person_pkey
public | phylochar | phylochar_pkey
public | phylotree | phylotree_pkey
public | phylotreenode | phylotreenode_pkey
public | polytcount | polytcount_pkey
public | rowsegment | rowsegment_matrixrow_id_idx
public | rowsegment | rowsegment_pkey
public | software | software_pkey
public | statechangeset | statechangeset_pkey
public | stateformat | stateformat_pkey
public | statemodifier | statemodifier_pkey
public | stateset | stateset_pkey
public | stepmatrixelement | stepmatrixelement_pkey
public | study | study_citation_id_idx
public | study | study_pkey
public | study_nexusfile | study_nexusfile_pkey
public | studystatus | studystatus_pkey
public | sub_matrix | sub_matrix_matrix_id_key
public | sub_matrix | sub_matrix_pkey
public | sub_taxonlabel | sub_taxonlabel_pkey
public | sub_taxonlabel | sub_taxonlabel_taxonlabel_id_key
public | sub_treeblock | sub_treeblock_pkey
public | sub_treeblock | sub_treeblock_treeblock_id_key
public | submission | submission_pkey
public | submission | submission_study_id_idx
public | submission | submission_study_id_key
public | taxon | taxon_pkey
public | taxonauthority | taxonauthority_pkey
public | taxonlabel | taxonlabel_pkey
public | taxonlabelgroup | taxonlabelgroup_pkey
public | taxonlabelpartition | taxonlabelpartition_pkey
public | taxonlabelset | taxonlabelset_pkey
public | taxonlabelset_taxonlabel | taxonlabelset_taxonlabel_pkey
public | taxonlink | taxonlink_pkey
public | taxonset | taxonset_pkey
public | taxonset_taxon | taxonset_taxon_pkey
public | taxonvariant | taxonvariant_pkey
public | treeattribute | treeattribute_pkey
public | treeblock | treeblock_pkey
public | treegroup | treegroup_pkey
public | treegroup_phylotree | treegroup_phylotree_pkey
public | treekind | treekind_pkey
public | treenodeedge | treenodeedge_pkey
public | treepartition | treepartition_pkey
public | treequality | treequality_pkey
public | treeset | treeset_pkey
public | treeset_phylotree | treeset_phylotree_pkey
public | treetype | treetype_pkey
public | typeset | typeset_pkey
public | user | user_pkey
public | user | user_username_key
public | userrole | userrole_pkey
public | usertype | usertype_pkey
public | usertyperecord | usertyperecord_pkey
public | usertyperrd_colrange | usertyperrd_colrange_columnrange_id_key
(109 rows)
|
