# Pattern 07 — EXISTS subquery without composite index

## The failure

AI generates a correct multi-tenant RLS policy using EXISTS:

```sql
CREATE POLICY "org_members_select_posts" ON posts
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM org_members
      WHERE org_members.org_id = posts.org_id
      AND org_members.user_id = auth.uid()
    )
  );
```

The logic is correct. But there's no index on `org_members(org_id, user_id)`. For every row PostgreSQL evaluates in `posts`, it runs the EXISTS subquery — which, without an index, is a full sequential scan of `org_members`.

## The performance math

Say you have 10,000 posts and 500 org members:
- **Without index**: 10,000 rows × 500 member rows = 5,000,000 comparisons per query
- **With index**: 10,000 rows × ~10 index lookups = 100,000 operations (50× faster)

At 100,000 posts: 50 million comparisons without an index. Supabase's default 30-second query timeout will fire long before the query returns. Your users see loading spinners or errors.

This is the kind of bug that doesn't show up in development (small data) and detonates in production (real data scale).

## The fix

Always create the index before creating the EXISTS-based policy:

```sql
-- Composite index covering both join columns used in the EXISTS
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_org_members_org_user
  ON org_members(org_id, user_id);
```

Use `CONCURRENTLY` on tables with existing data to avoid locking the table during index creation.

## Verify the index is being used

After creating the index, use EXPLAIN ANALYZE to confirm:

```sql
EXPLAIN ANALYZE
SELECT * FROM posts
WHERE EXISTS (
  SELECT 1 FROM org_members
  WHERE org_members.org_id = posts.org_id
  AND org_members.user_id = auth.uid()
);
```

Look for `Index Scan using idx_org_members_org_user` in the output. If you see `Seq Scan on org_members`, the index isn't being used (check column order matches the index definition).

## Audit: find your unindexed EXISTS policies

```sql
-- View all RLS policies with their USING expressions
SELECT tablename, policyname, qual
FROM pg_policies
WHERE qual ILIKE '%exists%'
ORDER BY tablename;
-- For each result that references a join table, verify the join columns are indexed.
```