During filling of DB for the purpose of performance tests I have realized that DB size is quite bigger then I would expect, then I have found that its the indexes which takes cca. 3x more spaces than the data itself.
By default, for every property, alfresco indexes all its type columns. Lets say I have a property of type string - its value is stored in string_value column and these: "boolean_value, double_value, float_value, long_value" are all set to NULL, but they take space in index anyway. I have optimized the indexes using the query bellow, indexing only those columns related to type of the property.
Is there any point of indexing those columns unrelated to the property type? Is my optimization OK or is there any risk? Thank you!
drop index idx_alf_nprop_d;
drop index idx_alf_nprop_s;
drop index idx_alf_nprop_f;
drop index idx_alf_nprop_b;
drop index idx_alf_nprop_l;
CREATE INDEX idx_alf_nprop_b_custom ON alf_node_properties (boolean_value) WHERE persisted_type_n = 1;
CREATE INDEX idx_alf_nprop_d_custom ON alf_node_properties (double_value) WHERE persisted_type_n = 5;
CREATE INDEX idx_alf_nprop_f_custom ON alf_node_properties (float_value) WHERE persisted_type_n = 4;
CREATE INDEX idx_alf_nprop_l_custom ON alf_node_properties (long_value) WHERE persisted_type_n = 3;
CREATE INDEX idx_alf_nprop_s_custom ON alf_node_properties (string_value) WHERE persisted_type_n = 6;