weak search performance under certain conditions

[ThomasJejkal]

Describe the bug
Under certain conditions, search performance using the '/api/v1/dataresources/search' endpoint is unexpectedly slow. Mainly by including the ResourceType attribute, queries may cost a factor of 10. Due to relatively fast queries this effect gets relevant for very huge repositories (spotted at 84k DataResources)

To Reproduce
Steps to reproduce the behavior:

1. Use search endpoint, e.g., http://{{HOSTNAME}}:{{PORT}}/api/v1/dataresources/search
2. Provide a (valid) example DataResource including resourceType attribute in the request body, e.g.

{
   "resourceType":{
       "typeGeneral":"TEXT"
   },
   "publicationYear":"2019"
}

3. Measure the response time, e.g., 2 seconds
4. Change the example DataResource to include a full resourceType, e.g.

{
    "resourceType":{
        "typeGeneral":"TEXT",
        "value":"manuscriptMetadata"
    },
    "publicationYear":"2019"
}

5. Measure the response time, e.g., 20 seconds
6. Change the example DataResource again to include another top-level, primitive attribute, e.g., publisher
7. Measure the response time, e.g., 2 seconds

Expected behavior
The expected behaviour is, that the response time does not change within an entire order of magnitude. Including resourceType will require a more complex query than querying only for, e.g., publisher and publicationYear. However, the impact should be much less and it should not depend on the number of primitive attributes within the query (see difference in measurement 5 and 7).

Screenshots
none

Desktop (please complete the following information):

• OS: MacOS, Windows, Unix
• REST Tool: Postman
• Version 7.36.0

Additional context
none

[ThomasJejkal]

After some detailed analysis of the performed queries this issue seems to be caused by wrong assumptions of the underlying database system. In the particular case, a JOIN operation of tables 'data_resource' and 'resource_type' is required.

If the database decides to use hashed joins, performance is fine. If the database decides to used nested loop for performing the JOIN operation (typically only considers if one side of the JOIN has far less elements) performance suffers a lot. This behaviour was observed using Postgres 9.X, in some cases also for Postgres 13.

Unfortunately, there seems to be no programmatic solution for now. The only solution, in case you have severe performance issues, is to modify the database configuration. Therefor, edit the file 'postgresql.conf' and add the following line:

....
#enable_nestloop = on
enable_nestloop = off
....

However, as this might have negative impact on indexing capabilities of the database, you should disable nest loops only if really necessary.

Furthermore, it is not clear whether the same issue also appears while using another database system, e.g., MySQL or Oracle.