Query plans on bulk data (before schema)

Source file: evidence/before/query-plans.md

Failure mode: FM18 (no indexes on foreign keys, RLS subqueries evaluated per row) · Measured: 2026-11-22T16:05:41.112Z

Impact: with 72,390 invoices on the platform, every query a single agency makes scans the whole table. Each Seq Scan reads every agency's rows and throws away more than 99% of them, and the dashboard repeats one of these scans for every invoice it shows.

Setup

  • pnpm tsx before/exploits/run-all.ts boots localbase (Postgres in WASM via PGlite) with the before schema and bulkSeed(): the two demo agencies plus 60 generated ones.
  • Row counts: organizations 62, clients 1,809, projects 2,736, invoices 72,390, invoice_items 181,213, messages 9,572, payments 53,640.
  • Each query is the exact SQL that localbase's PostgREST layer compiles from the URL the app sends, run inside a transaction as role authenticated with Maya's JWT claims (as PostgREST does), so RLS policies are in the plan.
  • EXPLAIN (ANALYZE, BUFFERS) 6 times per query; the first run warms the buffer cache and is discarded; the median of the remaining 5 execution times is reported.
  • Absolute timings come from a WASM Postgres on a laptop and will differ from hosted Supabase. The plan shapes (Seq Scan, "Rows Removed by Filter") are the point, and they are the same on any Postgres without these indexes.

Summary

QueryRows returnedMedian execution (5 runs)Runs (ms)Key plan node
Invoice list by org, ordered by due date355.628 ms4.83, 4.69, 5.70, 6.17, 5.63Seq Scan on invoices t0
Line items for one invoice (runs once per invoice: the N+1)212.699 ms12.57, 12.87, 12.70, 12.72, 12.63Seq Scan on invoice_items t0
Messages for one project (RLS policy with membership subquery)50.954 ms1.07, 0.95, 0.91, 0.93, 0.97Seq Scan on messages t0
Payments for one invoice (invoice detail page)13.402 ms3.48, 3.40, 3.51, 3.40, 3.37Seq Scan on payments t0
Revenue rpc get_org_revenue (dashboard)76.482 ms6.49, 6.34, 6.48, 6.23, 6.54Function Scan on get_org_revenue r (body: Seq Scan on invoices i)

Invoice list by org, ordered by due date

App request: GET /rest/v1/invoices?select=*&org_id=eq.<uuid>&order=due_on.desc

text
Aggregate  (cost=2282.02..2282.03 rows=1 width=32) (actual time=5.597..5.600 rows=1.00 loops=1)
  Buffers: shared hit=1376
  ->  Subquery Scan on r  (cost=2281.58..2281.94 rows=29 width=170) (actual time=5.399..5.459 rows=35.00 loops=1)
        Buffers: shared hit=1376
        ->  Sort  (cost=2281.58..2281.65 rows=29 width=146) (actual time=5.391..5.407 rows=35.00 loops=1)
              Sort Key: t0.due_on DESC
              Sort Method: quicksort  Memory: 21kB
              Buffers: shared hit=1376
              ->  Seq Scan on invoices t0  (cost=0.00..2280.88 rows=29 width=146) (actual time=0.010..5.363 rows=35.00 loops=1)
                    Filter: (org_id = 'd10e897e-3fad-4fe7-8ce6-ea6c6b804e9c'::uuid)
                    Rows Removed by Filter: 72355
                    Buffers: shared hit=1376
Planning Time: 0.089 ms
Execution Time: 5.628 ms

Line items for one invoice (runs once per invoice: the N+1)

App request: GET /rest/v1/invoice_items?select=*&invoice_id=eq.<uuid>

text
Aggregate  (cost=4946.17..4946.18 rows=1 width=32) (actual time=12.665..12.668 rows=1.00 loops=1)
  Buffers: shared hit=2681
  ->  Seq Scan on invoice_items t0  (cost=0.00..4946.16 rows=3 width=82) (actual time=0.009..12.638 rows=2.00 loops=1)
        Filter: (invoice_id = 'c25b4d20-448c-4f77-b548-798f9e888d23'::uuid)
        Rows Removed by Filter: 181211
        Buffers: shared hit=2681
Planning Time: 0.118 ms
Execution Time: 12.699 ms

Messages for one project (RLS policy with membership subquery)

App request: GET /rest/v1/messages?select=*&project_id=eq.<uuid>&order=created_at.asc

text
Aggregate  (cost=32153.52..32153.53 rows=1 width=32) (actual time=0.924..0.928 rows=1.00 loops=1)
  Buffers: shared hit=184
  ->  Subquery Scan on r  (cost=32153.49..32153.51 rows=2 width=146) (actual time=0.899..0.908 rows=5.00 loops=1)
        Buffers: shared hit=184
        ->  Sort  (cost=32153.49..32153.49 rows=2 width=122) (actual time=0.895..0.899 rows=5.00 loops=1)
              Sort Key: t0.created_at
              Sort Method: quicksort  Memory: 17kB
              Buffers: shared hit=184
              ->  Seq Scan on messages t0  (cost=0.00..32153.48 rows=2 width=122) (actual time=0.181..0.889 rows=5.00 loops=1)
                    Filter: ((project_id = '677bc368-9659-4982-9234-2b9351468ffd'::uuid) AND (((((COALESCE(NULLIF(current_setting('request.jwt.claims'::text, true), ''::text), '{}'::text))::jsonb -> 'user_metadata'::text) ->> 'role'::text) = 'admin'::text) OR (ANY (org_id = (hashed SubPlan 2).col1))))
                    Rows Removed by Filter: 9567
                    Buffers: shared hit=184
                    SubPlan 2
                      ->  Seq Scan on memberships m  (cost=0.00..3.15 rows=1 width=16) (actual time=0.007..0.163 rows=1.00 loops=1)
                            Filter: (user_id = (NULLIF(((COALESCE(NULLIF(current_setting('request.jwt.claims'::text, true), ''::text), '{}'::text))::jsonb ->> 'sub'::text), ''::text))::uuid)
                            Rows Removed by Filter: 65
                            Buffers: shared hit=1
Planning Time: 0.213 ms
Execution Time: 0.954 ms

Payments for one invoice (invoice detail page)

App request: GET /rest/v1/payments?select=*&invoice_id=eq.<uuid>

text
Aggregate  (cost=1333.51..1333.52 rows=1 width=32) (actual time=3.384..3.386 rows=1.00 loops=1)
  Buffers: shared hit=663
  ->  Seq Scan on payments t0  (cost=0.00..1333.50 rows=1 width=65) (actual time=0.006..3.374 rows=1.00 loops=1)
        Filter: (invoice_id = 'c25b4d20-448c-4f77-b548-798f9e888d23'::uuid)
        Rows Removed by Filter: 53639
        Buffers: shared hit=663
Planning Time: 0.043 ms
Execution Time: 3.402 ms

Revenue rpc get_org_revenue (dashboard)

App request: GET /rest/v1/rpc/get_org_revenue

text
Aggregate  (cost=12.75..12.76 rows=1 width=32) (actual time=6.464..6.465 rows=1.00 loops=1)
  Buffers: shared hit=1376
  ->  Function Scan on get_org_revenue r  (cost=0.25..10.25 rows=1000 width=24) (actual time=6.435..6.441 rows=7.00 loops=1)
        Buffers: shared hit=1376
Planning Time: 0.023 ms
Execution Time: 6.482 ms

Function body, explained directly:

text
GroupAggregate  (cost=2462.42..2462.93 rows=20 width=48) (actual time=6.136..6.158 rows=7.00 loops=1)
  Group Key: (to_char((issued_on)::timestamp with time zone, 'YYYY-MM'::text))
  Buffers: shared hit=1376
  ->  Sort  (cost=2462.42..2462.47 rows=21 width=40) (actual time=6.122..6.131 rows=26.00 loops=1)
        Sort Key: (to_char((issued_on)::timestamp with time zone, 'YYYY-MM'::text))
        Sort Method: quicksort  Memory: 17kB
        Buffers: shared hit=1376
        ->  Seq Scan on invoices i  (cost=0.00..2461.95 rows=21 width=40) (actual time=0.012..6.094 rows=26.00 loops=1)
              Filter: ((org_id = 'd10e897e-3fad-4fe7-8ce6-ea6c6b804e9c'::uuid) AND (status = 'paid'::text))
              Rows Removed by Filter: 72364
              Buffers: shared hit=1376
Planning Time: 0.071 ms
Execution Time: 6.180 ms

What fixes it

schema.sql has no indexes beyond primary keys (advisor lint 0001_unindexed_foreign_keys). create index on invoices (org_id, due_on desc), create index on invoice_items (invoice_id), create index on messages (project_id, created_at), create index on payments (invoice_id) and create index on memberships (user_id, org_id) turn each of these into an index scan. The after schema does this (after/supabase/migrations).