Large DB indexes not used

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

gabrielmarinb

Hi everybody:

We are trying to optimize as much as possible the space used by the database. After analyzing  alf_node_properties table an their associated indexes we see that there are two indexes that use a significant amount of space and haven't been used (0 scans)

IX	IX SCANS	TUPLES READ	TUPLES FETCHED	SIZE
fk_alf_nprop_n	6449609592	57930396229	31550958879	334.26 GB
idx_alf_nprop_s	21216573	21663665	60714	1.18 TB
idx_alf_nprop_l	305087	6677509	658591	1.07 TB
fk_alf_nprop_qn	234366	2317411960	2317290289	424.1 GB
idx_alf_nprop_d	2495	32891	32889	1.07 TB
idx_alf_nprop_b	0	0	0	1.07 TB
fk_alf_nprop_loc	0	0	0	516.1 GB
idx_alf_nprop_f	0	0	0	1.07 TB

Is it safe to remove them? I am aware that if they are present it is optimal to keep them, but in our situation, do you consider mandatory to keep them?

ACS 6.2.1

Regards

Gabriel Marin

angelborroy

It's up to you.

The indexes are related to boolean and float properties:

https://github.com/Alfresco/alfresco-legacy-repo/blob/1bdbd68f5c720ebeaa78153facc36d9215dcae95/projects/repository/config/alfresco/dbscripts/create/org.hibernate.dialect.PostgreSQLDialect/Schema-Reference-ALF.xml#L1991

https://github.com/Alfresco/alfresco-legacy-repo/blob/1bdbd68f5c720ebeaa78153facc36d9215dcae95/projects/repository/config/alfresco/dbscripts/create/org.hibernate.dialect.PostgreSQLDialect/Schema-Reference-ALF.xml#L2005

It looks like you are not using queries involving those kind of values.