Address limitations with FHIR search in citus

[punktilious]

Describe the bug In Citus we distribute resource and parameter tables using logical_resource_id. All joins between tables must therefore include logical_resource_id in the join predicate.

Certain search queries which require a join to connect between two resources currently do not work and will result in a SQLException thrown by Citus. These include:

• _include
• _revinclude
• _has

Environment Which version of IBM FHIR Server? 5.0.0-SNAPSHOT

To Reproduce Steps to reproduce the behavior:

1. Configure a tenant using citus
2. Load test data
3. Attempt a search using '_include', '_revinclude' or '_has'
4. See error

Expected behavior The search should complete successfully.

Additional context It may be possible to use common table expression (CTE) queries for which Citus will fetch the data first then distribute the results to each node as required so that they can be used in-line with rest of the query. This can be effective if the result set is small (performance). There are also limits to the result set size (although this is configurable).

The _include and _revinclude queries are probably the simplest to address because we already break down processing to fetch rows for individual reference links then iterate using multiple queries.