DISTINCT in subquery prevents Db2 from selecting optimal execution plan

[punktilious]

Describe the bug
Search request including tokens generates the following SQL:

SELECT R.RESOURCE_ID, R.LOGICAL_RESOURCE_ID, R.VERSION_ID, R.LAST_UPDATED, R.IS_DELETED,
       R.DATA, LR.LOGICAL_ID
  FROM Observation_LOGICAL_RESOURCES LR 
  JOIN Observation_RESOURCES R 
    ON R.LOGICAL_RESOURCE_ID=LR.LOGICAL_RESOURCE_ID
   AND R.RESOURCE_ID = LR.CURRENT_RESOURCE_ID
   AND R.IS_DELETED <> 'Y'
  JOIN (SELECT DISTINCT LOGICAL_RESOURCE_ID
          FROM Observation_TOKEN_VALUES  
         WHERE (PARAMETER_NAME_ID=1191 AND ((TOKEN_VALUE = ?)))
       ) Observation0
    ON Observation0.LOGICAL_RESOURCE_ID = R.LOGICAL_RESOURCE_ID
  JOIN (SELECT DISTINCT LOGICAL_RESOURCE_ID
          FROM Observation_STR_VALUES  
         WHERE (PARAMETER_NAME_ID=1396 AND (STR_VALUE = ?))
       ) Observation1 
    ON Observation1.LOGICAL_RESOURCE_ID = R.LOGICAL_RESOURCE_ID
  JOIN (SELECT DISTINCT LOGICAL_RESOURCE_ID
          FROM Observation_TOKEN_VALUES 
         WHERE (PARAMETER_NAME_ID=1008 AND ((TOKEN_VALUE = ? AND CODE_SYSTEM_ID = ?)))
       ) Observation2 
    ON Observation2.LOGICAL_RESOURCE_ID = R.LOGICAL_RESOURCE_ID
ORDER BY RESOURCE_ID ASC  LIMIT 10 OFFSET 0

Several of the sub-selects use SELECT DISTINCT. This prevents the optimizer from pushing down join predicates resulting in a less-than-optimal execution plan.

To Reproduce
Performance test with search requests.

Expected behavior
Rewrite the query to permit a more efficient execution plan.

Additional context
N/A

[albertwang-ibm]

Can not remember if the distinct was added to avoid the duplication issue ...

[prb112]

Can not remember if the distinct was added to avoid the duplication issue ...

It was, Robin has an alternative.

[JohnTimm]

https://github.com/IBM/FHIR/blob/eaa432fb2be210ad301d2872553ecde8da5ff0f6/fhir-persistence-jdbc/src/main/java/com/ibm/fhir/persistence/jdbc/util/QuerySegmentAggregator.java#L428
https://github.com/IBM/FHIR/blob/eaa432fb2be210ad301d2872553ecde8da5ff0f6/fhir-persistence-jdbc/src/main/java/com/ibm/fhir/persistence/jdbc/util/QuerySegmentAggregator.java#L438
https://github.com/IBM/FHIR/blob/eaa432fb2be210ad301d2872553ecde8da5ff0f6/fhir-persistence-jdbc/src/main/java/com/ibm/fhir/persistence/jdbc/util/JDBCQueryBuilder.java#L1268-L1271

[punktilious]

The new EXISTS queries follow a similar form to this:

SELECT R.RESOURCE_ID,MIN(S1.QUANTITY_VALUE)  
  FROM Observation_LOGICAL_RESOURCES LR 
  JOIN Observation_RESOURCES R 
    ON R.LOGICAL_RESOURCE_ID=LR.LOGICAL_RESOURCE_ID 
   AND R.RESOURCE_ID = LR.CURRENT_RESOURCE_ID 
   AND R.IS_DELETED <> 'Y'  
  AND  EXISTS (SELECT 1 FROM Observation_TOKEN_VALUES  AS V  WHERE (PARAMETER_NAME_ID=1392 AND ((TOKEN_VALUE = 'corrected'))) AND V.LOGICAL_RESOURCE_ID = R.LOGICAL_RESOURCE_ID)  
  AND  EXISTS (SELECT 1 FROM Observation_TOKEN_VALUES  AS V  WHERE (PARAMETER_NAME_ID=1073 AND ((TOKEN_VALUE = '1fEdfCQ1W8'))) AND V.LOGICAL_RESOURCE_ID = R.LOGICAL_RESOURCE_ID)  
  LEFT OUTER JOIN Observation_QUANTITY_VALUES S1 
               ON (S1.PARAMETER_NAME_ID=1093 AND S1.LOGICAL_RESOURCE_ID = R.LOGICAL_RESOURCE_ID)  
  GROUP BY R.RESOURCE_ID  ORDER BY MIN(S1.QUANTITY_VALUE) ASC NULLS LAST OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;

• Using fake test data to replace the parameter markers.

The EXISTS sub-queries are correlated with the main join using the predicate AND V.LOGICAL_RESOURCE_ID = R.LOGICAL_RESOURCE_ID. The optimizer can produce an execution plan minimizing the number of logical IOs because it can use the higher selectivity filters first, then use that to access parameters with lower selectivity (e.g. a _tag property) using an index that includes the resource_id.