topic Re: Large DB indexes not used in Alfresco Forum

Large DB indexes not used

gabrielmarinb — Wed, 13 Dec 2023 12:39:33 GMT

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

Re: Large DB indexes not used

angelborroy — Wed, 13 Dec 2023 14:12:05 GMT

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

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

Re: Large DB indexes not used

gabrielmarinb — Thu, 14 Dec 2023 14:42:56 GMT

Thank you Angel

When you say queries I understand you mean actions like list nodes (on database) and not search (on Solr). Is that right?

Re: Large DB indexes not used

angelborroy — Thu, 14 Dec 2023 15:13:46 GMT

Search is not performed only in SOLR, it depends on the query syntax.