Compiling ViewDefinition to SQL — four implementations, four ways to avoid grinding to a halt — Jan 30, 2024
Josh's compiler, walked end to end
Parse the FHIRPath with fhirpath.js into an intermediate representation, then emit SQL as a chain of table expressions and select * from the last one.
Patients explode into names, names explode into given names, a table of the constant gets built so every value can be compared against it, matches come back as zeros and ones, and then it groups back up by the name each row came from.
The honest headline: a very short ViewDefinition produced a full screen of SQL, and it has never been run on more than two patients.
The performance objection landed immediately
Those table-wide GROUP BYs are a function of how many resources you have, and at millions of resources they get expensive. Someone reported a colleague had been pushed to write the SQL with no group-bys at all and managed it, but it was painful. Josh's response: he would genuinely like to know how — it was not obvious to him, and he suspected there was prior art he had not found.
Nikolai — not CTEs, a series of lateral joins
In Postgres, CTEs are eager, so at real scale you hit performance problems. A lateral join is evaluated per row and stays local, so the engine effectively iterates rows, expands and joins as it goes.
Aidbox translates FHIRPath into Postgres jsonpath, which lets simple field access sit directly in the select instead of earning its own join, and uses explicit lateral joins for forEach. Nested selects become additional lateral joins in a flat linear list rather than nesting.
The gotcha: the jsonpath query functions behave slightly differently from each other, and picking the wrong one bites you on arrays.
Two other approaches — DuckDB flat, BigQuery deep-nested
DuckDB prototype — stays as flat as it can, only dropping into a subquery for first so it can do a LIMIT 1 — and not even then if it can use DuckDB's own syntax in the select.
BigQuery — group-bys were avoided by leaning on the engine: since a FHIRPath expression is recursive and SQL nests recursively, they emitted deeply nested inner selects all the way down, no CTEs and no joins, keeping everything local. It made the SQL almost entirely unreadable by a human and ran efficiently.
Pathling's sharpest finding — joins are poison in Spark
They used to use explode plus joins, and removed all the joins because joins are poison in Spark — every one may redistribute data between workers. Now they hold a collection representation as far along as possible, building up a complicated select clause of functions that operate on collections, and only explode at the tail end when the user actually asked for more than one row per resource.
Exploding early is what costs you: you end up with two representations and more steps in the query plan. Union needed a custom cross-product function written against the Catalyst API.
Two ideas from the comparison
- Every project converges on some intermediate representation, and finding a common one might let them plug into more engines
- Apache Calcite as a way to hand off the last half mile to a pluggable optimiser, which matters more if ViewDefinitions ever get an idiomatic SQL syntax instead of JSON