SET useMultistageEngine=true;
SET usePhysicalOptimizer=true;

EXPLAIN PLAN FOR SELECT * FROM userFactEvents WHERE userUUID NOT IN (
    SELECT userUUID FROM userGroups WHERE groupUUID = 'group-1'
  )
LIMIT 10

Execution Plan
PhysicalSort(fetch=[10])
  PhysicalExchange(exchangeStrategy=[SINGLETON_EXCHANGE], distKeys=[[]], execStrategy=[STREAMING], collation=[[]])
    PhysicalSort(fetch=[10])
      PhysicalProject(cityName=[$0], deviceOS=[$1], distanceMeters=[$2], productCode=[$3], tripAmount=[$4], tripCurrencyCode=[$5], ts=[$6], userUUID=[$7])
        PhysicalFilter(condition=[IS NOT TRUE($10)])
          PhysicalJoin(condition=[=($8, $9)], joinType=[left])
            PhysicalExchange(exchangeStrategy=[IDENTITY_EXCHANGE], distKeys=[[]], execStrategy=[STREAMING], collation=[[]])
              PhysicalProject(cityName=[$3], deviceOS=[$4], distanceMeters=[$5], productCode=[$6], tripAmount=[$7], tripCurrencyCode=[$8], ts=[$9], userUUID=[$10], userUUID0=[$10])
                PhysicalTableScan(table=[[default, userFactEvents]])
            PhysicalExchange(exchangeStrategy=[IDENTITY_EXCHANGE], distKeys=[[]], execStrategy=[STREAMING], collation=[[]])
              PhysicalAggregate(group=[{0}], agg#0=[MIN($1)])
                PhysicalProject(userUUID=[$4], $f1=[true])
                  PhysicalFilter(condition=[=($3, _UTF-8'group-1')])
                    PhysicalTableScan(table=[[default, userGroups]])

SET useMultistageEngine=true;
SET usePhysicalOptimizer=true;

explain plan for WITH tmp AS (
  SELECT userUUID FROM userFactEvents WHERE deviceOS IN ('macos', 'ios') OR tripAmount > 100
  UNION ALL
	SELECT userUUID FROM userAttributes 
      WHERE deviceOS IN ('macos') OR daysSinceFirstTrip > 100
),
tmp2 AS (
  SELECT DISTINCT userUUID FROM tmp
)
SELECT
  userUUID
FROM
  userFactEvents
WHERE
  deviceOS IN ('macos', 'ios')
  AND userUUID IN (SELECT userUUID FROM tmp2)

Execution Plan
PhysicalJoin(condition=[=($0, $1)], joinType=[semi])
  PhysicalExchange(exchangeStrategy=[IDENTITY_EXCHANGE], distKeys=[[]], execStrategy=[STREAMING], collation=[[]])
    PhysicalProject(userUUID=[$10])
      PhysicalFilter(condition=[SEARCH($4, Sarg[_UTF-8'ios':VARCHAR CHARACTER SET "UTF-8", _UTF-8'macos':VARCHAR CHARACTER SET "UTF-8"]:VARCHAR CHARACTER SET "UTF-8")])
        PhysicalTableScan(table=[[default, userFactEvents]])
  PhysicalAggregate(group=[{0}])
    PhysicalUnion(all=[true])
      PhysicalExchange(exchangeStrategy=[IDENTITY_EXCHANGE], distKeys=[[]], execStrategy=[STREAMING], collation=[[]])
        PhysicalProject(userUUID=[$10])
          PhysicalFilter(condition=[OR(SEARCH($4, Sarg[_UTF-8'ios':VARCHAR CHARACTER SET "UTF-8", _UTF-8'macos':VARCHAR CHARACTER SET "UTF-8"]:VARCHAR CHARACTER SET "UTF-8"), >($7, 100.0E0))])
            PhysicalTableScan(table=[[default, userFactEvents]])
      PhysicalExchange(exchangeStrategy=[IDENTITY_EXCHANGE], distKeys=[[]], execStrategy=[STREAMING], collation=[[]])
        PhysicalProject(userUUID=[$6])
          PhysicalFilter(condition=[OR(=($4, _UTF-8'macos'), >($3, 100))])
            PhysicalTableScan(table=[[default, userAttributes]])