Rebuilding maven-metadata.xml on slower PostgreSQL could take extremely long time

SYMPTOM:

Since migrated to PostgreSQL, accessing /repository/maven-releases/com/example/my-app/maven-metadata.xml after the scheduled Cleanup task takes longer time.

REPRODUCE STEPS:

1. Install 3.35 and migrate to PostgreSQL
2. Enable DEBUG logging for "org.sonatype.nexus.content.maven.store.Maven2ComponentDAO"
3. Deploy a maven artifact with 2 versions into the maven-releases repository
4. Delete one version from Browse page
5. With curl command, request maven-metadata.xml (should be quick), for example:

   time curl -sf -D- -u admin:admin123 -O http://localhost:8081/repository/maven-releases/com/example/my-app/maven-metadata.xml
   

1. Deploy a maven artifact with 101 versions
2. Delete one version from Browse page
3. With curl command, request maven-metadata.xml again

EXPECTED BEHAVIOUR:

The number of SQL SELECT statements should be proportional to the number of components or assets (blobs)

ACTUAL BEHAVIOUR:

One particular query looks like executed close to the number of comps multiplied by the number of comps, which is from org.sonatype.nexus.repository.content.fluent.internal.FluentComponentBuilderImpl#findComponent:

grep -E '==>  Preparing:.+' -o extract_threadId_from_nexus.log | sort | uniq -c

When 100 components (versions):

... (snip) ...
10500 ==>  Preparing: SELECT * FROM maven2_component WHERE repository_id = ? AND namespace = ? AND name = ? AND version = ? ;

With 200 components:

40600 ==>  Preparing: SELECT * FROM maven2_component WHERE repository_id = ? AND namespace = ? AND name = ? AND version = ? ;

Even though above query uses the correct index and each query should be very fast (expecting a few ms), if PostgreSQL side had some slight slowness (including the connection between Nexus and PG), above can potentially cause significant slowness.