Suboptimal compartment search execution plan on Postgres

[punktilious]

Describe the bug
Example compartment search:

https://localhost:9443/fhir-server/api/v4/Patient/17456ba2724-64c01687-cdff-44f7-b889-33d7a598f5c7/CareTeam?status=active

Query (after parameter variable substitution):

SELECT COUNT(DISTINCT R.RESOURCE_ID)  
  FROM fhirdata.CareTeam_LOGICAL_RESOURCES LR
  JOIN fhirdata.CareTeam_RESOURCES R ON R.LOGICAL_RESOURCE_ID = LR.LOGICAL_RESOURCE_ID
   AND R.RESOURCE_ID = LR.CURRENT_RESOURCE_ID   
   AND R.IS_DELETED = 'N'  
  JOIN fhirdata.CareTeam_TOKEN_VALUES_V  AS param0 ON (param0.PARAMETER_NAME_ID = 2009 AND ((param0.TOKEN_VALUE = 'active')))
   AND LR.LOGICAL_RESOURCE_ID = param0.LOGICAL_RESOURCE_ID
  JOIN fhirdata.CareTeam_TOKEN_VALUES_V  AS param1
    ON ((param1.PARAMETER_NAME_ID = 2002 AND param1.TOKEN_VALUE = '17456ba2724-64c01687-cdff-44f7-b889-33d7a598f5c7' AND param1.CODE_SYSTEM_ID = 341713292)
    OR (param1.PARAMETER_NAME_ID = 2006 AND param1.TOKEN_VALUE = '17456ba2724-64c01687-cdff-44f7-b889-33d7a598f5c7' AND param1.CODE_SYSTEM_ID = 341713292))
   AND LR.LOGICAL_RESOURCE_ID = param1.LOGICAL_RESOURCE_ID;


For reference, the parameter names are:

fhirdb=> select * from fhirdata.parameter_names where parameter_name_id in (2002,2006,2009);
 parameter_name_id | parameter_name 
-------------------+----------------
              2002 | patient
              2006 | participant
              2009 | status

According to the execution plan, Postgres decides to perform the access for (param0) token-values = 'active' first before it attempts to find the matching patient (param1). In other words, it is computing "get all active CareTeam values and then find me any which match the given patient" instead of "get all CareTeam resources for the given patient and find which are active". The "OR" appears to confuse the cardinality estimation. If the OR is removed for the participant (2006) reference, the query response in about 260ms (over the internet).

Expected behavior
Database should filter on the most selective predicate first (patient logical-id, in the above example) before considering far less selective predicates such as status.

Implementation Notes
Because compartment is an important concept in many searches, it seems appropriate to extract compartment membership during ingestion and populate specific fields instead of interpreting compartment membership via references in the search query.