# Bug 3475: join planning

- Status: closed
- Project: Zero
- Creator: @tantaman
- Created: 2025-01-24T21:05:52Z
- Modified: 2026-06-27T17:58:44Z
- Reactions: 👍️ ×2 (cloleb, danielcompton)
- URL: https://bugs.rocicorp.dev/p/zero/issue/3475

## Description

A user [is running up against a slow query](https://discord.com/channels/830183651022471199/1332349837554880513/1332359164592128131) because we do not currently plan `whereExists` clauses at all. We treat them as `left joins` when we should treat them as `inner joins`. If we treat them as `inner joins` we can swap the join order to move the smallest table to the outer loop.

As a concrete example, say zbugs organized issues into workspaces and a user can only see issues from workspaces belonging to their team. This is a variation on the data model shared with us in the error report.

```ts
z.query.issue
  .whereExists('workspace', q => q.where('team_id', 'my_team_id'))
```

What we do today is a full table scan of the `issue` table since we treat `exists` as a `left-join`. In reality, we should pull workspaces with the given `team_id` first and then join the issues.

---

Our original plan for query planning was to gather stats (which SQLite will do for us and save in a sqlite_stat table) and use those stats to come up with a query plan. A different path may be to compile ZQL to SQL and ask SQLite for the plan. idk.. maybe that's too crazy.

## Comments (5)

### @aboodman — 2025-01-24T21:54:19Z

I still like the original plan a lot. Sounds like a fun project.

A kind of interesting thing here is that we should theoretically be able to run the query either way and get the same results. Because of this, we could do it different server-side vs client-side. So like, we could implement the optimization on the server but not the client, or do the client later.

### @izakfilmalter — 2025-05-06T13:09:49Z

Running into this.

### @aboodman — 2025-08-26T01:24:54Z

We are still planning to do something for this, but it is a big project. While investigating, we realized that users can often work around by just phrasing their queries differently. This is temporarily documented here:

https://www.notion.so/replicache/Query-Performance-Recipes-24a3bed895458011a3b1c0eb3991955f?source=copy_link#24a3bed89545800c896cfd98e8f4a3fe

(will move to real docs soon)

Reactions: 👍️ ×1 (joodaloop)

### @aboodman — 2025-10-17T18:17:15Z

Manual join planning has shipped: https://zero.rocicorp.dev/docs/reading-data#join-flipping. The team is now working on automatic planning.

### @aboodman — 2026-06-27T17:58:42Z

Automatic planning is shipped