In addition, I’ve posted about the postgres limitatoin to CTEs here:
If you can try PG 13.0, there’s optimizations there.
The reason why this request is tricky is that some of the primary criteria results in a full table copy (such as, ‘find all death’ or ‘find all visits’) and with the ‘non-materialized’ mode in PG 13, that means that it can leverage the additional criteria outside of the CTE to limit the intial primary events to a smaller working-set. If we pull out the CTE into a temp table, the temp table will simply be a copy of the domain table, and that’s something I’d like to try to avoid, but I’m not saying that there isn’t something we can do…just pointing out that we may need to look at alternatives.