clockworklabs/SpacetimeDB · error

Subscriptions require indexes on join columns

Error message

Subscriptions require indexes on join columns

What it means

Subscription evaluation replays row deltas through join plans incrementally, which requires index lookups on the join columns. After optimizing the plan, the compiler rejects it via has_non_index_join when any join column lacks an index, because indexless joins cannot be evaluated per-delta at acceptable cost.

Source

Thrown at crates/subscription/src/lib.rs:649

                    alias,
                ) => {
                    table_aliases.push(*alias);
                    table_ids.push(schema.table_id);
                }
                _ => {}
            });
            (table_ids, table_aliases)
        }

        let mut subscriptions = vec![];
        let mut physical_plans = vec![];
        let params = ExecutionParams::from_auth(auth);

        for plan in plans {
            let plan_opt = plan.clone().optimize()?;

            if has_non_index_join(&plan_opt) {
                bail!("Subscriptions require indexes on join columns")
            }

            if plan_opt.reads_from_event_table() {
                bail!("Event tables cannot be used as the lookup table in subscription joins")
            }

            let (table_ids, table_aliases) = table_ids_for_plan(&plan);

            let fragments = Fragments::compile_from_plan(&plan, &table_aliases)?;
            let is_join = fragments.insert_plans.len() > 1 && fragments.delete_plans.len() > 1;

            let mut view_ids = HashSet::new();
            plan_opt.collect_views(&mut view_ids);

            let metadata = SubscriptionMetadata {
                table_ids,
                return_schema: plan_opt.return_table(),
                view_ids: view_ids.into_iter().collect(),

View on GitHub (pinned to 6dee26c6ef)

Solutions

1. Declare an index on the join column in the table definition and re-publish: #[spacetimedb::index] on the field, or #[unique]/primary key where uniqueness holds.
2. Join on the side that is already indexed (e.g. the primary key of the other table) and adjust the query shape accordingly.

Example fix

// before
#[spacetimedb::table(name = player)]
pub struct Player {
    #[primary_key]
    pub id: u64,
    pub guild_id: u64, // no index -> join subscription fails
}

// after
#[spacetimedb::table(name = player)]
pub struct Player {
    #[primary_key]
    pub id: u64,
    #[spacetimedb::index]
    pub guild_id: u64,
}

-- verify join columns are indexed before subscribing:
spacetime describe my-db
-- every column used in a subscription JOIN ... ON must appear as an index,
-- unique constraint, or primary key in the table schema

try {
  await db.subscription.build(["SELECT a.* FROM a JOIN b ON a.b_id = b.id"]).subscribe();
} catch (e: any) {
  if (String(e.message).includes("require indexes on join columns")) {
    // add #[spacetimedb::index] on a.b_id, republish, resubscribe
  }
}

When it happens

Trigger: Subscribing to a query like SELECT a.* FROM a JOIN b ON a.user_id = b.id where the join column(s) on the probing side (a.user_id) have no index, unique constraint, or primary-key coverage.

Common situations: Adding join subscriptions over newly added foreign-key-like columns without declaring #[index]; joining on plain data columns; schema evolved to add the column but the index attribute was forgotten.

Related errors

• Could not compute positional arguments during query planning
• Invalid number of tables in subscription: {}
• Subscriptions must have at least one query
• Subscriptions must be SQL strings or typed queries
• Unsubscribe has already been called

AI-assisted analysis of clockworklabs/SpacetimeDB@6dee26c6ef (2026-08-20). Data as JSON: /api/errors/a621152cbf1589e3. Report an issue: GitHub.