Query plans on bulk data (after schema)

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

Measured: 2026-11-22T16:04:46.779Z · same method as evidence/before/query-plans.md.

Row counts: invoices 72,389, invoice_items 181,209, messages 9,572, payments 53,640. Role authenticated with Maya's claims, so the RLS policies are in every plan.

QueryRowsMedian (5 runs)Runs (ms)Key plan node
Invoice list by org, ordered by due date340.798 ms0.82, 0.77, 0.85, 0.80, 0.55Bitmap Heap Scan on invoices t0
Line items for one invoice20.104 ms0.10, 0.21, 0.10, 0.10, 0.10Index Scan using invoice_items_invoice_idx on invoice_items t0
Messages for one project50.149 ms0.16, 0.15, 0.15, 0.15, 0.15Bitmap Heap Scan on messages t0
Payments for one invoice10.079 ms0.08, 0.08, 0.08, 0.08, 0.08Index Scan using payments_invoice_idx on payments t0
Dashboard totals rpc invoice_summary (replaces get_org_revenue)40.622 ms0.70, 0.62, 0.62, 0.76, 0.50Function Scan on invoice_summary r
Dashboard list: 20 invoices with client name in one query (replaces the N+1)200.709 ms0.78, 0.83, 0.69, 0.69, 0.71Bitmap Heap Scan on invoices t0

Invoice list by org, ordered by due date

App request: GET /rest/v1/invoices?select=*&org_id=eq.d10e897e-3fad-4fe7-8ce6-ea6c6b804e9c&order=due_on.desc

text
Aggregate  (cost=128.69..128.70 rows=1 width=32) (actual time=0.523..0.526 rows=1.00 loops=1)
  Buffers: shared hit=41
  ->  Subquery Scan on r  (cost=128.54..128.66 rows=10 width=133) (actual time=0.364..0.403 rows=34.00 loops=1)
        Buffers: shared hit=41
        ->  Sort  (cost=128.54..128.56 rows=10 width=109) (actual time=0.361..0.371 rows=34.00 loops=1)
              Sort Key: t0.due_on DESC
              Sort Method: quicksort  Memory: 21kB
              Buffers: shared hit=41
              ->  Bitmap Heap Scan on invoices t0  (cost=4.64..128.37 rows=10 width=109) (actual time=0.044..0.340 rows=34.00 loops=1)
                    Recheck Cond: (org_id = 'd10e897e-3fad-4fe7-8ce6-ea6c6b804e9c'::uuid)
                    Filter: (private.is_staff(org_id) OR ((client_id = private.client_scope(org_id)) AND (status <> 'draft'::invoice_status)))
                    Heap Blocks: exact=4
                    Buffers: shared hit=41
                    ->  Bitmap Index Scan on invoices_org_status_due_idx  (cost=0.00..4.64 rows=29 width=0) (actual time=0.007..0.008 rows=36.00 loops=1)
                          Index Cond: (org_id = 'd10e897e-3fad-4fe7-8ce6-ea6c6b804e9c'::uuid)
                          Index Searches: 1
                          Buffers: shared hit=3
Planning Time: 0.080 ms
Execution Time: 0.545 ms

Line items for one invoice

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

text
Aggregate  (cost=44.81..44.82 rows=1 width=32) (actual time=0.072..0.074 rows=1.00 loops=1)
  Buffers: shared hit=6
  ->  Index Scan using invoice_items_invoice_idx on invoice_items t0  (cost=0.42..44.81 rows=2 width=88) (actual time=0.041..0.060 rows=2.00 loops=1)
        Index Cond: (invoice_id = 'c25b4d20-448c-4f77-b548-798f9e888d23'::uuid)
        Filter: (private.is_staff(org_id) OR EXISTS(SubPlan 1))
        Index Searches: 1
        Buffers: shared hit=6
        SubPlan 1
          ->  Index Scan using invoices_id_org_id_key on invoices i  (cost=0.42..9.20 rows=1 width=0) (never executed)
                Index Cond: (id = t0.invoice_id)
                Filter: ((private.is_staff(org_id) OR ((client_id = private.client_scope(org_id)) AND (status <> 'draft'::invoice_status))) AND (status <> 'draft'::invoice_status) AND (client_id = private.client_scope(org_id)))
                Index Searches: 0
Planning Time: 0.131 ms
Execution Time: 0.097 ms

Messages for one project

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

text
Aggregate  (cost=43.11..43.12 rows=1 width=32) (actual time=0.121..0.123 rows=1.00 loops=1)
  Buffers: shared hit=8
  ->  Bitmap Heap Scan on messages t0  (cost=4.31..43.10 rows=2 width=122) (actual time=0.040..0.099 rows=5.00 loops=1)
        Recheck Cond: (project_id = 'af904081-a20f-4c2b-8018-75af54233d9c'::uuid)
        Filter: (private.is_staff(org_id) OR EXISTS(SubPlan 1))
        Heap Blocks: exact=1
        Buffers: shared hit=8
        ->  Bitmap Index Scan on messages_project_idx  (cost=0.00..4.31 rows=3 width=0) (actual time=0.005..0.005 rows=5.00 loops=1)
              Index Cond: (project_id = 'af904081-a20f-4c2b-8018-75af54233d9c'::uuid)
              Index Searches: 1
              Buffers: shared hit=2
        SubPlan 1
          ->  Index Scan using projects_id_org_id_key on projects p  (cost=0.28..9.05 rows=1 width=0) (never executed)
                Index Cond: (id = t0.project_id)
                Filter: ((private.is_staff(org_id) OR (client_id = private.client_scope(org_id))) AND (client_id = private.client_scope(org_id)))
                Index Searches: 0
Planning Time: 0.112 ms
Execution Time: 0.146 ms

Payments for one invoice

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

text
Aggregate  (cost=17.88..17.89 rows=1 width=32) (actual time=0.052..0.054 rows=1.00 loops=1)
  Buffers: shared hit=5
  ->  Index Scan using payments_invoice_idx on payments t0  (cost=0.41..17.88 rows=1 width=114) (actual time=0.041..0.043 rows=1.00 loops=1)
        Index Cond: (invoice_id = 'c25b4d20-448c-4f77-b548-798f9e888d23'::uuid)
        Filter: (private.is_staff(org_id) OR EXISTS(SubPlan 1))
        Index Searches: 1
        Buffers: shared hit=5
        SubPlan 1
          ->  Index Scan using invoices_id_org_id_key on invoices i  (cost=0.42..9.19 rows=1 width=0) (never executed)
                Index Cond: (id = t0.invoice_id)
                Filter: ((private.is_staff(org_id) OR ((client_id = private.client_scope(org_id)) AND (status <> 'draft'::invoice_status))) AND (client_id = private.client_scope(org_id)))
                Index Searches: 0
Planning Time: 0.123 ms
Execution Time: 0.076 ms

Dashboard totals rpc invoice_summary (replaces get_org_revenue)

App request: POST /rest/v1/rpc/invoice_summary

text
Aggregate  (cost=12.75..12.76 rows=1 width=32) (actual time=0.483..0.484 rows=1.00 loops=1)
  Buffers: shared hit=41
  ->  Function Scan on invoice_summary r  (cost=0.25..10.25 rows=1000 width=24) (actual time=0.469..0.471 rows=4.00 loops=1)
        Buffers: shared hit=41
Planning Time: 0.018 ms
Execution Time: 0.496 ms

Dashboard list: 20 invoices with client name in one query (replaces the N+1)

App request: GET /rest/v1/invoices?select=id,number,status,total_cents,due_on,client:clients(name)&org_id=eq.<uuid>&order=due_on.desc&limit=20

text
Aggregate  (cost=216.91..216.92 rows=1 width=32) (actual time=0.680..0.683 rows=1.00 loops=1)
  Buffers: shared hit=121
  ->  Subquery Scan on r  (cost=128.54..216.89 rows=10 width=61) (actual time=0.376..0.640 rows=20.00 loops=1)
        Buffers: shared hit=121
        ->  Limit  (cost=128.54..216.79 rows=10 width=69) (actual time=0.374..0.629 rows=20.00 loops=1)
              Buffers: shared hit=121
              ->  Result  (cost=128.54..216.79 rows=10 width=69) (actual time=0.373..0.621 rows=20.00 loops=1)
                    Buffers: shared hit=121
                    ->  Sort  (cost=128.54..128.56 rows=10 width=69) (actual time=0.349..0.353 rows=20.00 loops=1)
                          Sort Key: t0.due_on DESC
                          Sort Method: quicksort  Memory: 21kB
                          Buffers: shared hit=41
                          ->  Bitmap Heap Scan on invoices t0  (cost=4.64..128.37 rows=10 width=69) (actual time=0.044..0.331 rows=34.00 loops=1)
                                Recheck Cond: (org_id = 'd10e897e-3fad-4fe7-8ce6-ea6c6b804e9c'::uuid)
                                Filter: (private.is_staff(org_id) OR ((client_id = private.client_scope(org_id)) AND (status <> 'draft'::invoice_status)))
                                Heap Blocks: exact=4
                                Buffers: shared hit=41
                                ->  Bitmap Index Scan on invoices_org_status_due_idx  (cost=0.00..4.64 rows=29 width=0) (actual time=0.007..0.007 rows=36.00 loops=1)
                                      Index Cond: (org_id = 'd10e897e-3fad-4fe7-8ce6-ea6c6b804e9c'::uuid)
                                      Index Searches: 1
                                      Buffers: shared hit=3
                    SubPlan 1
                      ->  Subquery Scan on x  (cost=0.28..8.81 rows=1 width=32) (actual time=0.012..0.012 rows=1.00 loops=20)
                            Buffers: shared hit=80
                            ->  Limit  (cost=0.28..8.80 rows=1 width=13) (actual time=0.011..0.011 rows=1.00 loops=20)
                                  Buffers: shared hit=80
                                  ->  Index Scan using clients_id_org_id_key on clients t1  (cost=0.28..8.80 rows=1 width=13) (actual time=0.010..0.010 rows=1.00 loops=20)
                                        Index Cond: ((id = t0.client_id) AND (org_id = t0.org_id))
                                        Filter: (private.is_staff(org_id) OR (id = private.client_scope(org_id)))
                                        Index Searches: 20
                                        Buffers: shared hit=80
Planning Time: 0.134 ms
Execution Time: 0.709 ms