Function applySingleLevelOptimization

packages/db/src/query/optimizer.ts:381–444

* Applies single-level predicate pushdown optimization (existing logic)

(query: QueryIR)

 * Applies single-level predicate pushdown optimization (existing logic)
 */
function applySingleLevelOptimization(query: QueryIR): QueryIR {
  // Skip optimization if no WHERE clauses exist
  if (!query.where || query.where.length === 0) {
    return query
  }

  // Multi-source FROM predicates are global post-union filters. Keep them
  // residual in the main query; sourceWhereClauses still captures safe
  // collection-level filters for loadSubset/index use.
  if (query.from.type === `unionFrom`) {
    return query
  }

  // For queries without joins, combine multiple WHERE clauses into a single clause
  // to avoid creating multiple filter operators in the pipeline
  if (!query.join || query.join.length === 0) {
    // Only optimize if there are multiple WHERE clauses to combine
    if (query.where.length > 1) {
      // Combine multiple WHERE clauses into a single AND expression
      const splitWhereClauses = splitAndClauses(query.where)
      const combinedWhere = combineWithAnd(splitWhereClauses)

      return {
        ...query,
        where: [combinedWhere],
      }
    }

    // For single WHERE clauses, no optimization needed
    return query
  }

  // Filter out residual WHERE clauses to prevent them from being optimized again
  const nonResidualWhereClauses = query.where.filter(
    (where) => !isResidualWhere(where),
  )

  // Step 1: Split all AND clauses at the root level for granular optimization
  const splitWhereClauses = splitAndClauses(nonResidualWhereClauses)

  // Step 2: Analyze each WHERE clause to determine which sources it touches
  const analyzedClauses = splitWhereClauses.map((clause) =>
    analyzeWhereClause(clause),
  )

  // Step 3: Group clauses by single-source vs multi-source
  const groupedClauses = groupWhereClauses(analyzedClauses)

  // Step 4: Apply optimizations by lifting single-source clauses into subqueries
  const optimizedQuery = applyOptimizations(query, groupedClauses)

  // Add back any residual WHERE clauses that were filtered out
  const residualWhereClauses = query.where.filter((where) =>
    isResidualWhere(where),
  )
  if (residualWhereClauses.length > 0) {
    optimizedQuery.where = [
      ...(optimizedQuery.where || []),