Investigate preload batching inefficiency: 44 separate zuora_rate_plans queries instead of 1

Problem

Billing::Usage::SubscriptionsFinder (SaaS path) loads subscriptions with eager loading:

::Zuora::Local::Subscription
  .latest_versions_covering_date(name: subscription_names, date: date)
  .includes(:all_rate_plans, :rate_plan_charges)
  .index_by(&:name)

The intent is to batch-load zuora_rate_plans and zuora_rate_plan_charges in a single query each. However, a production Sentry span shows 44 separate zuora_rate_plans IN queries (400 ms total) instead of a single batch query.

Similarly, the self-managed SelfManaged::SubscriptionsFinder uses the same pattern (line 50-53).

Evidence from production span

  • 44 zuora_rate_plans IN queries — 400 ms total
  • 1 zuora_rate_plan_charges IN query — 358 ms (this one batched correctly)
  • 1 zuora_subscriptions DISTINCT ON query — 46 ms (correct)

The rate plans are split into 44 individual queries rather than 1, suggesting ActiveRecord's eager loading is not batching them correctly.

Suspected causes

  1. distinct_on_name scope interference: The latest_versions_covering_date scope uses DISTINCT ON (name) via select(Arel::Nodes::DistinctOn...). This custom SELECT may cause ActiveRecord to lose track of the primary keys needed for eager loading, forcing it to fall back to per-record loading.

  2. index_by(&:name) materializing the relation: If index_by triggers enumeration before includes resolves, the eager loading may execute per-record rather than in batch.

  3. ActiveRecord includes vs preload behavior: With complex scopes (DISTINCT ON, subqueries), includes may silently degrade to eager_load (LEFT JOIN) or per-record loading. Explicit preload may work better here.

Proposed investigation

  1. Check if replacing .includes with .preload resolves the batching
  2. Test whether removing distinct_on_name from the scope allows proper batching
  3. Consider loading subscriptions first, then batch-loading rate plans in a separate explicit query: 
    subscriptions = ...latest_versions_covering_date(...).to_a
Zuora::Local::RatePlan.where(subscription_id: subscriptions.map(&:id)).each do |rp|
  # associate manually
end

Relevant files

  • app/finders/billing/usage/subscriptions_finder.rb:15-18 — SaaS finder with .includes
  • app/finders/billing/usage/self_managed/subscriptions_finder.rb:50-53 — SM finder with .includes
  • app/models/zuora/local/subscription.rb:81-95 — latest_versions_covering_date scope with distinct_on_name
  • app/models/zuora/local/subscription.rb:97-99 — distinct_on_name scope using Arel::Nodes::DistinctOn