Describe the bug
a whole-system search like this:
runQueryTest(Resource.class, "_type", "Basic,EvidenceVariable,ServiceRequest")
results in queries like these.
count:
SELECT COUNT(*) AS CNT
FROM LOGICAL_RESOURCES AS LR0
WHERE LR0.IS_DELETED = 'N'
AND LR0.RESOURCE_TYPE_ID IN (8,52,126)
AND EXISTS (
SELECT 1
FROM LOGICAL_RESOURCES AS LR1
WHERE LR1.LOGICAL_RESOURCE_ID = LR0.LOGICAL_RESOURCE_ID)
fetch ids:
SELECT LR0.RESOURCE_TYPE_ID, LR0.LOGICAL_RESOURCE_ID
FROM LOGICAL_RESOURCES AS LR0
WHERE LR0.IS_DELETED = 'N'
AND LR0.RESOURCE_TYPE_ID IN (8,52,126)
AND EXISTS (
SELECT 1
FROM LOGICAL_RESOURCES AS LR1
WHERE LR1.LOGICAL_RESOURCE_ID = LR0.LOGICAL_RESOURCE_ID)
ORDER BY LR0.LOGICAL_RESOURCE_ID
FETCH FIRST 10 ROWS ONLY
fetch resources:
SELECT RESOURCE_ID, LOGICAL_RESOURCE_ID, VERSION_ID, LAST_UPDATED, IS_DELETED, DATA, LOGICAL_ID, RESOURCE_PAYLOAD_KEY, RESOURCE_TYPE_ID
FROM (
SELECT R.RESOURCE_ID, R.LOGICAL_RESOURCE_ID, R.VERSION_ID, R.LAST_UPDATED, R.IS_DELETED, R.DATA, LR.LOGICAL_ID, R.RESOURCE_PAYLOAD_KEY, LR.RESOURCE_TYPE_ID AS RESOURCE_TYPE_ID
FROM (
SELECT LR.LOGICAL_RESOURCE_ID, LR.LOGICAL_ID, LR.CURRENT_RESOURCE_ID, 8 AS RESOURCE_TYPE_ID
FROM Basic_LOGICAL_RESOURCES AS LR
WHERE LR.IS_DELETED = 'N'
AND LR.LOGICAL_RESOURCE_ID IN (30002)) AS LR
INNER JOIN Basic_RESOURCES AS R ON LR.CURRENT_RESOURCE_ID = R.RESOURCE_ID) AS COMBINED_RESULTS
ORDER BY COMBINED_RESULTS.LOGICAL_RESOURCE_ID
The fetch ids query in particular is very slow when you have a lot of resources.
This is because the inner select grabs every row in the table and then the outer select filters it down to the (possibly very small) subset of resources.
Environment
Which version of IBM FHIR Server?
To Reproduce
Steps to reproduce the behavior:
- load a lot of resources
- do a whole-system search with a _type parameter
Expected behavior
The search should return in less than 2 seconds.
Additional context
In our testing, adding AND LR0.RESOURCE_TYPE_ID IN (8,52,126) to the inner select takes this query from >20 seconds down to ~100 ms.
Describe the bug
a whole-system search like this:
results in queries like these.
count:
fetch ids:
fetch resources:
The
fetch idsquery in particular is very slow when you have a lot of resources.This is because the inner select grabs every row in the table and then the outer select filters it down to the (possibly very small) subset of resources.
Environment
Which version of IBM FHIR Server?
To Reproduce
Steps to reproduce the behavior:
Expected behavior
The search should return in less than 2 seconds.
Additional context
In our testing, adding
AND LR0.RESOURCE_TYPE_ID IN (8,52,126)to the inner select takes this query from >20 seconds down to ~100 ms.