Query performance

The six saved queries plus full-text search, timed with and without the indexes in 002_indexes.sql, on a database scaled to production-like volume. Produced by devtrack bench.

PostgreSQL 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)

commits 150,226 · pull requests 30,135 · reviews 75,269 · tasks 20,055 · acceptance criteria 16,064 · commit tasks 120,055 · task criteria 14,472 · pull request commits 150,179 · requirement criteria 16,031. Median of 5 runs. Indexes took 0.47s to build.

Query	Before (ms)	After (ms)	Change	Plan after indexing
Acceptance criteria with no linked commits or no linked tests.	105.6	103.5	no change	Index Only Scan using ix_task_criteria_rev; Index Only Scan using ix_commit_tasks_rev; Seq… Before Sort (actual rows=16052 loops=1) Sort Key: (CASE WHEN (COALESCE((count(DISTINCT ct_1.commit_id)), '0'::bigint) = 0) THEN 0 ELSE 1 END), a.key Sort Method: quicksort Memory: 2634kB -> Hash Join (actual rows=16052 loops=1) Hash Cond: (s.feature_id = f.id) -> Hash Join (actual rows=16052 loops=1) Hash Cond: (a.story_id = s.id) -> Hash Left Join (actual rows=16052 loops=1) Hash Cond: (a.id = ct.criterion_id) Filter: ((COALESCE((count(DISTINCT ct_1.commit_id)), '0'::bigint) = 0) OR (COALESCE(ct.tests, '0'::bigint) = 0)) Rows Removed by Filter: 12 -> Hash Right Join (actual rows=16064 loops=1) Hash Cond: (tc.criterion_id = a.id) -> GroupAggregate (actual rows=12861 loops=1) Group Key: tc.criterion_id -> Sort (actual rows=96157 loops=1) Sort Key: tc.criterion_id, ct_1.commit_id Sort Method: external merge Disk: 2456kB -> Hash Join (actual rows=96157 loops=1) Hash Cond: (ct_1.task_id = tc.task_id) -> Seq Scan on commit_tasks ct_1 (actual rows=120055 loops=1) -> Hash (actual rows=14472 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 807kB -> Seq Scan on task_criteria tc (actual rows=14472 loops=1) -> Hash (actual rows=16064 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 1848kB -> Seq Scan on acceptance_criteria a (actual rows=16064 loops=1) -> Hash (actual rows=12 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Subquery Scan on ct (actual rows=12 loops=1) -> HashAggregate (actual rows=12 loops=1) Group Key: criterion_tests.criterion_id Batches: 1 Memory Usage: 24kB -> Seq Scan on criterion_tests (actual rows=12 loops=1) -> Hash (actual rows=4017 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 284kB -> Seq Scan on stories s (actual rows=4017 loops=1) -> Hash (actual rows=406 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 31kB -> Seq Scan on features f (actual rows=406 loops=1) After Sort (actual rows=16052 loops=1) Sort Key: (CASE WHEN (COALESCE((count(DISTINCT ct_1.commit_id)), '0'::bigint) = 0) THEN 0 ELSE 1 END), a.key Sort Method: quicksort Memory: 2634kB -> Nested Loop (actual rows=16052 loops=1) -> Hash Join (actual rows=16052 loops=1) Hash Cond: (a.story_id = s.id) -> Hash Left Join (actual rows=16052 loops=1) Hash Cond: (a.id = ct.criterion_id) Filter: ((COALESCE((count(DISTINCT ct_1.commit_id)), '0'::bigint) = 0) OR (COALESCE(ct.tests, '0'::bigint) = 0)) Rows Removed by Filter: 12 -> Hash Right Join (actual rows=16064 loops=1) Hash Cond: (tc.criterion_id = a.id) -> GroupAggregate (actual rows=12861 loops=1) Group Key: tc.criterion_id -> Incremental Sort (actual rows=96157 loops=1) Sort Key: tc.criterion_id, ct_1.commit_id Presorted Key: tc.criterion_id Full-sort Groups: 2563 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB -> Nested Loop (actual rows=96157 loops=1) -> Index Only Scan using ix_task_criteria_rev on task_criteria tc (actual rows=14472 loops=1) Heap Fetches: 0 -> Index Only Scan using ix_commit_tasks_rev on commit_tasks ct_1 (actual rows=7 loops=14472) Index Cond: (task_id = tc.task_id) Heap Fetches: 0 -> Hash (actual rows=16064 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 1848kB -> Seq Scan on acceptance_criteria a (actual rows=16064 loops=1) -> Hash (actual rows=12 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Subquery Scan on ct (actual rows=12 loops=1) -> HashAggregate (actual rows=12 loops=1) Group Key: criterion_tests.criterion_id Batches: 1 Memory Usage: 24kB -> Seq Scan on criterion_tests (actual rows=12 loops=1) -> Hash (actual rows=4017 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 284kB -> Seq Scan on stories s (actual rows=4017 loops=1) -> Memoize (actual rows=1 loops=16052) Cache Key: s.feature_id Cache Mode: logical Hits: 15646 Misses: 406 Evictions: 0 Overflows: 0 Memory Usage: 51kB -> Index Scan using features_pkey on features f (actual rows=1 loops=406) Index Cond: (id = s.feature_id)
Commits not tied to any task	> 60,000 (timed out)	451.6	> 133x faster	Seq Scan on commits; Index Only Scan using ix_commit_tasks_rev; Seq Scan on people; Index … Before Gather Merge (cost=12879.05..81388230.30 rows=30165 width=134) Workers Planned: 2 -> Sort (cost=11879.02..11910.45 rows=12569 width=138) Sort Key: c.committed_at DESC -> Hash Left Join (cost=3724.82..11023.23 rows=12569 width=138) Hash Cond: (c.author_id = p.id) -> Hash Anti Join (cost=3702.24..10967.41 rows=12569 width=130) Hash Cond: (c.id = ct.commit_id) -> Parallel Seq Scan on commits c (cost=0.00..6918.94 rows=62582 width=130) Filter: (NOT is_merge) -> Hash (cost=2201.55..2201.55 rows=120055 width=8) -> Seq Scan on commit_tasks ct (cost=0.00..2201.55 rows=120055 width=8) -> Hash (cost=15.59..15.59 rows=559 width=24) -> Seq Scan on people p (cost=0.00..15.59 rows=559 width=24) SubPlan 1 -> Limit (cost=2697.55..2697.56 rows=1 width=4) -> Sort (cost=2697.55..2697.56 rows=1 width=4) Sort Key: pr.number -> Nested Loop (cost=0.29..2697.54 rows=1 width=4) -> Seq Scan on pull_request_commits prc (cost=0.00..2689.24 rows=1 width=8) Filter: (commit_id = c.id) -> Index Scan using pull_requests_pkey on pull_requests pr (cost=0.29..8.30 rows=1 width=12) Index Cond: (id = prc.pull_request_id) JIT: Functions: 29 Options: Inlining true, Optimization true, Expressions true, Deforming true After Gather Merge (actual rows=30126 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (actual rows=10042 loops=3) Sort Key: c.committed_at DESC Sort Method: quicksort Memory: 2108kB Worker 0: Sort Method: quicksort Memory: 1573kB Worker 1: Sort Method: quicksort Memory: 2646kB -> Hash Left Join (actual rows=10042 loops=3) Hash Cond: (c.author_id = p.id) -> Parallel Hash Anti Join (actual rows=10042 loops=3) Hash Cond: (c.id = ct.commit_id) -> Parallel Seq Scan on commits c (actual rows=50060 loops=3) Filter: (NOT is_merge) Rows Removed by Filter: 15 -> Parallel Hash (actual rows=40018 loops=3) Buckets: 131072 Batches: 1 Memory Usage: 5792kB -> Parallel Index Only Scan using ix_commit_tasks_rev on commit_tasks ct (actual rows=40018 loops=3) Heap Fetches: 0 -> Hash (actual rows=559 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 42kB -> Seq Scan on people p (actual rows=559 loops=3) SubPlan 1 -> Limit (actual rows=1 loops=30126) -> Sort (actual rows=1 loops=30126) Sort Key: pr.number Sort Method: quicksort Memory: 25kB -> Nested Loop (actual rows=1 loops=30126) -> Index Only Scan using ix_pr_commits_rev on pull_request_commits prc (actual rows=1 loops=30126) Index Cond: (commit_id = c.id) Heap Fetches: 74 -> Index Scan using pull_requests_pkey on pull_requests pr (actual rows=1 loops=30125) Index Cond: (id = prc.pull_request_id)
Cycle time: from story start to the merge of the last PR carrying its work.	224.5	255.7	no change	Seq Scan on pull_request_commits; Seq Scan on commit_tasks; Seq Scan on tasks; Seq Scan on… Before Sort (actual rows=3217 loops=1) Sort Key: (round((EXTRACT(epoch FROM ((max(pr.merged_at)) - s.started_at)) / 3600.0), 1)) DESC Sort Method: quicksort Memory: 441kB -> Hash Join (actual rows=3217 loops=1) Hash Cond: (s.feature_id = f.id) -> Hash Join (actual rows=3217 loops=1) Hash Cond: (t.story_id = s.id) -> GroupAggregate (actual rows=3217 loops=1) Group Key: t.story_id -> Sort (actual rows=120054 loops=1) Sort Key: t.story_id, pr.id Sort Method: external merge Disk: 4008kB -> Hash Join (actual rows=120054 loops=1) Hash Cond: (prc.pull_request_id = pr.id) -> Hash Join (actual rows=120054 loops=1) Hash Cond: (ct.task_id = t.id) -> Hash Join (actual rows=120054 loops=1) Hash Cond: (prc.commit_id = ct.commit_id) -> Seq Scan on pull_request_commits prc (actual rows=150179 loops=1) -> Hash (actual rows=120055 loops=1) Buckets: 131072 Batches: 1 Memory Usage: 6652kB -> Seq Scan on commit_tasks ct (actual rows=120055 loops=1) -> Hash (actual rows=20055 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1197kB -> Seq Scan on tasks t (actual rows=20055 loops=1) -> Hash (actual rows=30135 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1669kB -> Seq Scan on pull_requests pr (actual rows=30135 loops=1) Filter: (merged_at IS NOT NULL) -> Hash (actual rows=4017 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 386kB -> Seq Scan on stories s (actual rows=4017 loops=1) Filter: (started_at IS NOT NULL) -> Hash (actual rows=406 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 31kB -> Seq Scan on features f (actual rows=406 loops=1) After Sort (actual rows=3217 loops=1) Sort Key: (round((EXTRACT(epoch FROM ((max(pr.merged_at)) - s.started_at)) / 3600.0), 1)) DESC Sort Method: quicksort Memory: 441kB -> Hash Join (actual rows=3217 loops=1) Hash Cond: (s.feature_id = f.id) -> Hash Join (actual rows=3217 loops=1) Hash Cond: (t.story_id = s.id) -> GroupAggregate (actual rows=3217 loops=1) Group Key: t.story_id -> Sort (actual rows=120054 loops=1) Sort Key: t.story_id, pr.id Sort Method: external merge Disk: 4008kB -> Hash Join (actual rows=120054 loops=1) Hash Cond: (prc.pull_request_id = pr.id) -> Hash Join (actual rows=120054 loops=1) Hash Cond: (ct.task_id = t.id) -> Hash Join (actual rows=120054 loops=1) Hash Cond: (prc.commit_id = ct.commit_id) -> Seq Scan on pull_request_commits prc (actual rows=150179 loops=1) -> Hash (actual rows=120055 loops=1) Buckets: 131072 Batches: 1 Memory Usage: 6652kB -> Seq Scan on commit_tasks ct (actual rows=120055 loops=1) -> Hash (actual rows=20055 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1197kB -> Seq Scan on tasks t (actual rows=20055 loops=1) -> Hash (actual rows=30135 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1669kB -> Seq Scan on pull_requests pr (actual rows=30135 loops=1) Filter: (merged_at IS NOT NULL) -> Hash (actual rows=4017 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 386kB -> Seq Scan on stories s (actual rows=4017 loops=1) Filter: (started_at IS NOT NULL) -> Hash (actual rows=406 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 31kB -> Seq Scan on features f (actual rows=406 loops=1)
Requirements changed after the code for one of their criteria merged.	140.0	149.4	no change	Seq Scan on acceptance_criteria; Seq Scan on requirement_criteria; Subquery Scan; Seq Scan… Before Sort (actual rows=95 loops=1) Sort Key: r.updated_at DESC, r.key, a.key Sort Method: quicksort Memory: 35kB -> Hash Left Join (actual rows=95 loops=1) Hash Cond: ((r.id = rev.requirement_id) AND (r.version = rev.version)) -> Hash Join (actual rows=95 loops=1) Hash Cond: (a.id = rc.criterion_id) -> Seq Scan on acceptance_criteria a (actual rows=16064 loops=1) -> Hash (actual rows=95 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 75kB -> Hash Join (actual rows=95 loops=1) Hash Cond: (rc.requirement_id = r.id) Join Filter: (r.updated_at > cc.last_merge) Rows Removed by Join Filter: 12734 -> Hash Join (actual rows=12829 loops=1) Hash Cond: (rc.criterion_id = cc.criterion_id) -> Seq Scan on requirement_criteria rc (actual rows=16031 loops=1) -> Hash (actual rows=12860 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 731kB -> Subquery Scan on cc (actual rows=12860 loops=1) -> HashAggregate (actual rows=12860 loops=1) Group Key: tc.criterion_id Batches: 1 Memory Usage: 1425kB -> Hash Join (actual rows=96156 loops=1) Hash Cond: (prc.pull_request_id = pr.id) -> Hash Join (actual rows=96156 loops=1) Hash Cond: (prc.commit_id = ct.commit_id) -> Seq Scan on pull_request_commits prc (actual rows=150179 loops=1) -> Hash (actual rows=96157 loops=1) Buckets: 131072 Batches: 1 Memory Usage: 5532kB -> Hash Join (actual rows=96157 loops=1) Hash Cond: (ct.task_id = tc.task_id) -> Seq Scan on commit_tasks ct (actual rows=120055 loops=1) -> Hash (actual rows=14472 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 807kB -> Seq Scan on task_criteria tc (actual rows=14472 loops=1) -> Hash (actual rows=30135 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1669kB -> Seq Scan on pull_requests pr (actual rows=30135 loops=1) Filter: (merged_at IS NOT NULL) -> Hash (actual rows=1609 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 147kB -> Seq Scan on requirements r (actual rows=1609 loops=1) -> Hash (actual rows=13 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on requirement_revisions rev (actual rows=13 loops=1) After Sort (actual rows=95 loops=1) Sort Key: r.updated_at DESC, r.key, a.key Sort Method: quicksort Memory: 35kB -> Hash Left Join (actual rows=95 loops=1) Hash Cond: ((r.id = rev.requirement_id) AND (r.version = rev.version)) -> Hash Join (actual rows=95 loops=1) Hash Cond: (a.id = rc.criterion_id) -> Seq Scan on acceptance_criteria a (actual rows=16064 loops=1) -> Hash (actual rows=95 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 75kB -> Hash Join (actual rows=95 loops=1) Hash Cond: (rc.requirement_id = r.id) Join Filter: (r.updated_at > cc.last_merge) Rows Removed by Join Filter: 12734 -> Hash Join (actual rows=12829 loops=1) Hash Cond: (rc.criterion_id = cc.criterion_id) -> Seq Scan on requirement_criteria rc (actual rows=16031 loops=1) -> Hash (actual rows=12860 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 731kB -> Subquery Scan on cc (actual rows=12860 loops=1) -> HashAggregate (actual rows=12860 loops=1) Group Key: tc.criterion_id Batches: 1 Memory Usage: 1425kB -> Hash Join (actual rows=96156 loops=1) Hash Cond: (prc.pull_request_id = pr.id) -> Hash Join (actual rows=96156 loops=1) Hash Cond: (prc.commit_id = ct.commit_id) -> Seq Scan on pull_request_commits prc (actual rows=150179 loops=1) -> Hash (actual rows=96157 loops=1) Buckets: 131072 Batches: 1 Memory Usage: 5532kB -> Hash Join (actual rows=96157 loops=1) Hash Cond: (ct.task_id = tc.task_id) -> Seq Scan on commit_tasks ct (actual rows=120055 loops=1) -> Hash (actual rows=14472 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 807kB -> Seq Scan on task_criteria tc (actual rows=14472 loops=1) -> Hash (actual rows=30135 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1669kB -> Seq Scan on pull_requests pr (actual rows=30135 loops=1) Filter: (merged_at IS NOT NULL) -> Hash (actual rows=1609 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 147kB -> Seq Scan on requirements r (actual rows=1609 loops=1) -> Hash (actual rows=13 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on requirement_revisions rev (actual rows=13 loops=1)
Review turnaround by reviewer: hours from PR opened to that reviewer's first review.	62.0	65.6	no change	Seq Scan on reviews; Seq Scan on pull_requests; Seq Scan on people Before Sort (actual rows=503 loops=1) Sort Key: (round(avg((EXTRACT(epoch FROM ((min(reviews.submitted_at)) - pr.created_at)) / 3600.0)), 1)) DESC Sort Method: quicksort Memory: 68kB -> HashAggregate (actual rows=503 loops=1) Group Key: p.id Batches: 1 Memory Usage: 297kB -> Hash Join (actual rows=30096 loops=1) Hash Cond: (reviews.reviewer_id = p.id) -> Hash Join (actual rows=30096 loops=1) Hash Cond: (reviews.pull_request_id = pr.id) -> HashAggregate (actual rows=30269 loops=1) Group Key: reviews.reviewer_id, reviews.pull_request_id Batches: 1 Memory Usage: 3601kB -> Seq Scan on reviews (actual rows=75269 loops=1) -> Hash (actual rows=30045 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1665kB -> Seq Scan on pull_requests pr (actual rows=30045 loops=1) Filter: (created_at IS NOT NULL) Rows Removed by Filter: 90 -> Hash (actual rows=559 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 47kB -> Seq Scan on people p (actual rows=559 loops=1) After Sort (actual rows=503 loops=1) Sort Key: (round(avg((EXTRACT(epoch FROM ((min(reviews.submitted_at)) - pr.created_at)) / 3600.0)), 1)) DESC Sort Method: quicksort Memory: 68kB -> HashAggregate (actual rows=503 loops=1) Group Key: p.id Batches: 1 Memory Usage: 297kB -> Hash Join (actual rows=30096 loops=1) Hash Cond: (reviews.reviewer_id = p.id) -> Hash Join (actual rows=30096 loops=1) Hash Cond: (reviews.pull_request_id = pr.id) -> HashAggregate (actual rows=30269 loops=1) Group Key: reviews.reviewer_id, reviews.pull_request_id Batches: 1 Memory Usage: 3601kB -> Seq Scan on reviews (actual rows=75269 loops=1) -> Hash (actual rows=30045 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1665kB -> Seq Scan on pull_requests pr (actual rows=30045 loops=1) Filter: (created_at IS NOT NULL) Rows Removed by Filter: 90 -> Hash (actual rows=559 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 47kB -> Seq Scan on people p (actual rows=559 loops=1)
Full trace for one requirement: requirement -> criteria -> tasks -> commits -> PRs -> reviews.	51.3	2.2	23.1x faster	Seq Scan on people; Index Scan using requirements_key_key; Index Only Scan using requireme… Before Sort (actual rows=187 loops=1) Sort Key: a.key, t.key, c.committed_at, rv.submitted_at Sort Method: quicksort Memory: 69kB -> Hash Left Join (actual rows=187 loops=1) Hash Cond: (rv.reviewer_id = rp.id) -> Hash Right Join (actual rows=187 loops=1) Hash Cond: (rv.pull_request_id = pr.id) -> Seq Scan on reviews rv (actual rows=75269 loops=1) -> Hash (actual rows=75 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 24kB -> Nested Loop Left Join (actual rows=75 loops=1) -> Hash Right Join (actual rows=75 loops=1) Hash Cond: (prc.commit_id = c.id) -> Seq Scan on pull_request_commits prc (actual rows=150179 loops=1) -> Hash (actual rows=75 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 22kB -> Nested Loop Left Join (actual rows=75 loops=1) -> Hash Right Join (actual rows=75 loops=1) Hash Cond: (ct.task_id = t.id) -> Seq Scan on commit_tasks ct (actual rows=120055 loops=1) -> Hash (actual rows=10 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Nested Loop Left Join (actual rows=10 loops=1) -> Hash Right Join (actual rows=10 loops=1) Hash Cond: (tc.criterion_id = a.id) -> Seq Scan on task_criteria tc (actual rows=14472 loops=1) -> Hash (actual rows=10 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Nested Loop (actual rows=10 loops=1) -> Nested Loop (actual rows=10 loops=1) -> Index Scan using requirements_key_key on requirements r (actual rows=1 loops=1) Index Cond: (key = 'SCALE-R-17'::text) -> Index Only Scan using requirement_criteria_pkey on requirement_criteria rc (actual rows=10 loops=1) Index Cond: (requirement_id = r.id) Heap Fetches: 0 -> Index Scan using acceptance_criteria_pkey on acceptance_criteria a (actual rows=1 loops=10) Index Cond: (id = rc.criterion_id) -> Index Scan using tasks_pkey on tasks t (actual rows=1 loops=10) Index Cond: (id = tc.task_id) -> Index Scan using commits_pkey on commits c (actual rows=1 loops=75) Index Cond: (id = ct.commit_id) -> Index Scan using pull_requests_pkey on pull_requests pr (actual rows=1 loops=75) Index Cond: (id = prc.pull_request_id) -> Hash (actual rows=559 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 42kB -> Seq Scan on people rp (actual rows=559 loops=1) After Sort (actual rows=187 loops=1) Sort Key: a.key, t.key, c.committed_at, rv.submitted_at Sort Method: quicksort Memory: 69kB -> Hash Right Join (actual rows=187 loops=1) Hash Cond: (rp.id = rv.reviewer_id) -> Seq Scan on people rp (actual rows=559 loops=1) -> Hash (actual rows=187 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 53kB -> Nested Loop Left Join (actual rows=187 loops=1) -> Nested Loop Left Join (actual rows=75 loops=1) -> Nested Loop Left Join (actual rows=75 loops=1) -> Nested Loop Left Join (actual rows=75 loops=1) -> Nested Loop Left Join (actual rows=75 loops=1) -> Nested Loop Left Join (actual rows=10 loops=1) -> Nested Loop Left Join (actual rows=10 loops=1) -> Nested Loop (actual rows=10 loops=1) -> Nested Loop (actual rows=10 loops=1) -> Index Scan using requirements_key_key on requirements r (actual rows=1 loops=1) Index Cond: (key = 'SCALE-R-17'::text) -> Index Only Scan using requirement_criteria_pkey on requirement_criteria rc (actual rows=10 loops=1) Index Cond: (requirement_id = r.id) Heap Fetches: 0 -> Index Scan using acceptance_criteria_pkey on acceptance_criteria a (actual rows=1 loops=10) Index Cond: (id = rc.criterion_id) -> Index Only Scan using ix_task_criteria_rev on task_criteria tc (actual rows=1 loops=10) Index Cond: (criterion_id = a.id) Heap Fetches: 0 -> Index Scan using tasks_pkey on tasks t (actual rows=1 loops=10) Index Cond: (id = tc.task_id) -> Index Only Scan using ix_commit_tasks_rev on commit_tasks ct (actual rows=8 loops=10) Index Cond: (task_id = t.id) Heap Fetches: 0 -> Index Scan using commits_pkey on commits c (actual rows=1 loops=75) Index Cond: (id = ct.commit_id) -> Index Only Scan using ix_pr_commits_rev on pull_request_commits prc (actual rows=1 loops=75) Index Cond: (commit_id = c.id) Heap Fetches: 0 -> Index Scan using pull_requests_pkey on pull_requests pr (actual rows=1 loops=75) Index Cond: (id = prc.pull_request_id) -> Index Scan using ix_reviews_pr on reviews rv (actual rows=2 loops=75) Index Cond: (pull_request_id = pr.id)
Full-text search on commit messages	94.1	2.2	42.2x faster	Bitmap Heap Scan on commits; Bitmap Index Scan using ix_commits_search Before Seq Scan on commits (actual rows=0 loops=1) Filter: ((message ~~* '%zstd%'::text) AND (message ~~* '%proxy%'::text)) Rows Removed by Filter: 150226 After Bitmap Heap Scan on commits (actual rows=0 loops=1) Recheck Cond: (search @@ '''zstd'' & ''proxi'''::tsquery) -> Bitmap Index Scan on ix_commits_search (actual rows=0 loops=1) Index Cond: (search @@ '''zstd'' & ''proxi'''::tsquery)

SQLite 3.53.1

Query	Before (ms)	After (ms)	Change	Plan after indexing
Acceptance criteria with no linked commits or no linked tests.	94.1	64.5	1.5x faster	SCAN tc USING COVERING INDEX ix_task_criteria_rev; SEARCH ct USING COVERING INDEX ix_commi… Before MATERIALIZE crit_commits SCAN ct USING COVERING INDEX sqlite_autoindex_commit_tasks_1 SEARCH tc USING COVERING INDEX sqlite_autoindex_task_criteria_1 (task_id=?) USE TEMP B-TREE FOR GROUP BY USE TEMP B-TREE FOR count(DISTINCT) MATERIALIZE crit_tests SCAN criterion_tests USING COVERING INDEX sqlite_autoindex_criterion_tests_1 SCAN a SEARCH s USING INTEGER PRIMARY KEY (rowid=?) SEARCH f USING INTEGER PRIMARY KEY (rowid=?) BLOOM FILTER ON cc (criterion_id=?) SEARCH cc USING AUTOMATIC COVERING INDEX (criterion_id=?) LEFT-JOIN BLOOM FILTER ON ct (criterion_id=?) SEARCH ct USING AUTOMATIC COVERING INDEX (criterion_id=?) LEFT-JOIN USE TEMP B-TREE FOR ORDER BY After MATERIALIZE crit_commits SCAN tc USING COVERING INDEX ix_task_criteria_rev SEARCH ct USING COVERING INDEX ix_commit_tasks_rev (task_id=?) USE TEMP B-TREE FOR count(DISTINCT) MATERIALIZE crit_tests SCAN criterion_tests USING COVERING INDEX sqlite_autoindex_criterion_tests_1 SCAN f USING COVERING INDEX sqlite_autoindex_features_1 SEARCH s USING INDEX ix_stories_feature (feature_id=?) SEARCH a USING INDEX ix_criteria_story (story_id=?) BLOOM FILTER ON cc (criterion_id=?) SEARCH cc USING AUTOMATIC COVERING INDEX (criterion_id=?) LEFT-JOIN BLOOM FILTER ON ct (criterion_id=?) SEARCH ct USING AUTOMATIC COVERING INDEX (criterion_id=?) LEFT-JOIN USE TEMP B-TREE FOR ORDER BY
Commits not tied to any task	> 60,000 (timed out)	179.5	> 334x faster	SCAN c; SEARCH ct USING COVERING INDEX sqlite_autoindex_commit_tasks_1 (commit_id=?); SEAR… Before SCAN c CORRELATED SCALAR SUBQUERY 2 SEARCH ct USING COVERING INDEX sqlite_autoindex_commit_tasks_1 (commit_id=?) SEARCH p USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN CORRELATED SCALAR SUBQUERY 1 SCAN pr USING COVERING INDEX sqlite_autoindex_pull_requests_2 SEARCH prc USING COVERING INDEX sqlite_autoindex_pull_request_commits_1 (pull_request_id=? AND commit_id=?) USE TEMP B-TREE FOR ORDER BY USE TEMP B-TREE FOR ORDER BY After SCAN c CORRELATED SCALAR SUBQUERY 2 SEARCH ct USING COVERING INDEX sqlite_autoindex_commit_tasks_1 (commit_id=?) SEARCH p USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN CORRELATED SCALAR SUBQUERY 1 SEARCH prc USING COVERING INDEX ix_pr_commits_rev (commit_id=?) SEARCH pr USING INTEGER PRIMARY KEY (rowid=?) USE TEMP B-TREE FOR ORDER BY USE TEMP B-TREE FOR ORDER BY
Cycle time: from story start to the merge of the last PR carrying its work.	144.6	366.7	2.5x slower	SCAN t USING COVERING INDEX ix_tasks_story; SEARCH ct USING COVERING INDEX ix_commit_tasks… Before CO-ROUTINE story_merges SCAN pr SEARCH prc USING COVERING INDEX sqlite_autoindex_pull_request_commits_1 (pull_request_id=?) SEARCH ct USING COVERING INDEX sqlite_autoindex_commit_tasks_1 (commit_id=?) SEARCH t USING INTEGER PRIMARY KEY (rowid=?) USE TEMP B-TREE FOR GROUP BY USE TEMP B-TREE FOR count(DISTINCT) SCAN sm SEARCH s USING INTEGER PRIMARY KEY (rowid=?) SEARCH f USING INTEGER PRIMARY KEY (rowid=?) USE TEMP B-TREE FOR ORDER BY After CO-ROUTINE story_merges SCAN t USING COVERING INDEX ix_tasks_story SEARCH ct USING COVERING INDEX ix_commit_tasks_rev (task_id=?) SEARCH prc USING COVERING INDEX ix_pr_commits_rev (commit_id=?) BLOOM FILTER ON pr (id=?) SEARCH pr USING INTEGER PRIMARY KEY (rowid=?) USE TEMP B-TREE FOR count(DISTINCT) SCAN sm SEARCH s USING INTEGER PRIMARY KEY (rowid=?) SEARCH f USING INTEGER PRIMARY KEY (rowid=?) USE TEMP B-TREE FOR ORDER BY
Requirements changed after the code for one of their criteria merged.	85.5	96.0	no change	SCAN tc USING COVERING INDEX ix_task_criteria_rev; SEARCH ct USING COVERING INDEX ix_commi… Before CO-ROUTINE criterion_code SCAN pr SEARCH prc USING COVERING INDEX sqlite_autoindex_pull_request_commits_1 (pull_request_id=?) SEARCH ct USING COVERING INDEX sqlite_autoindex_commit_tasks_1 (commit_id=?) SEARCH tc USING COVERING INDEX sqlite_autoindex_task_criteria_1 (task_id=?) USE TEMP B-TREE FOR GROUP BY SCAN r SEARCH rc USING COVERING INDEX sqlite_autoindex_requirement_criteria_1 (requirement_id=?) SEARCH a USING INTEGER PRIMARY KEY (rowid=?) BLOOM FILTER ON cc (criterion_id=?) SEARCH cc USING AUTOMATIC COVERING INDEX (criterion_id=?) SEARCH rev USING INDEX sqlite_autoindex_requirement_revisions_1 (requirement_id=? AND version=?) LEFT-JOIN USE TEMP B-TREE FOR ORDER BY After CO-ROUTINE criterion_code SCAN tc USING COVERING INDEX ix_task_criteria_rev SEARCH ct USING COVERING INDEX ix_commit_tasks_rev (task_id=?) SEARCH prc USING COVERING INDEX ix_pr_commits_rev (commit_id=?) BLOOM FILTER ON pr (id=?) SEARCH pr USING INTEGER PRIMARY KEY (rowid=?) SCAN cc SEARCH rc USING COVERING INDEX ix_req_criteria_rev (criterion_id=?) SEARCH a USING INTEGER PRIMARY KEY (rowid=?) SEARCH r USING INTEGER PRIMARY KEY (rowid=?) SEARCH rev USING INDEX sqlite_autoindex_requirement_revisions_1 (requirement_id=? AND version=?) LEFT-JOIN USE TEMP B-TREE FOR ORDER BY
Review turnaround by reviewer: hours from PR opened to that reviewer's first review.	75.5	167.7	2.2x slower	SCAN reviews USING INDEX ix_reviews_reviewer_time; SCAN fr; SEARCH pr USING INTEGER PRIMAR… Before CO-ROUTINE first_reviews SCAN reviews USE TEMP B-TREE FOR GROUP BY SCAN fr SEARCH pr USING INTEGER PRIMARY KEY (rowid=?) SEARCH p USING INTEGER PRIMARY KEY (rowid=?) USE TEMP B-TREE FOR GROUP BY USE TEMP B-TREE FOR ORDER BY After CO-ROUTINE first_reviews SCAN reviews USING INDEX ix_reviews_reviewer_time USE TEMP B-TREE FOR GROUP BY SCAN fr SEARCH pr USING INTEGER PRIMARY KEY (rowid=?) SEARCH p USING INTEGER PRIMARY KEY (rowid=?) USE TEMP B-TREE FOR GROUP BY USE TEMP B-TREE FOR ORDER BY
Full trace for one requirement: requirement -> criteria -> tasks -> commits -> PRs -> reviews.	148.0	1.2	123.3x faster	SEARCH r USING COVERING INDEX sqlite_autoindex_requirements_1 (key=?); SEARCH rc USING COV… Before SEARCH r USING COVERING INDEX sqlite_autoindex_requirements_1 (key=?) SEARCH rc USING COVERING INDEX sqlite_autoindex_requirement_criteria_1 (requirement_id=?) SEARCH a USING INTEGER PRIMARY KEY (rowid=?) BLOOM FILTER ON tc (criterion_id=?) SEARCH tc USING AUTOMATIC COVERING INDEX (criterion_id=?) LEFT-JOIN SEARCH t USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN BLOOM FILTER ON ct (task_id=?) SEARCH ct USING AUTOMATIC COVERING INDEX (task_id=?) LEFT-JOIN SEARCH c USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN BLOOM FILTER ON prc (commit_id=?) SEARCH prc USING AUTOMATIC COVERING INDEX (commit_id=?) LEFT-JOIN SEARCH pr USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN BLOOM FILTER ON rv (pull_request_id=?) SEARCH rv USING AUTOMATIC COVERING INDEX (pull_request_id=?) LEFT-JOIN SEARCH rp USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN USE TEMP B-TREE FOR ORDER BY After SEARCH r USING COVERING INDEX sqlite_autoindex_requirements_1 (key=?) SEARCH rc USING COVERING INDEX sqlite_autoindex_requirement_criteria_1 (requirement_id=?) SEARCH a USING INTEGER PRIMARY KEY (rowid=?) SEARCH tc USING COVERING INDEX ix_task_criteria_rev (criterion_id=?) LEFT-JOIN SEARCH t USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN SEARCH ct USING COVERING INDEX ix_commit_tasks_rev (task_id=?) LEFT-JOIN SEARCH c USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN SEARCH prc USING COVERING INDEX ix_pr_commits_rev (commit_id=?) LEFT-JOIN SEARCH pr USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN SEARCH rv USING INDEX ix_reviews_pr (pull_request_id=?) LEFT-JOIN SEARCH rp USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN USE TEMP B-TREE FOR ORDER BY
Full-text search on commit messages	18.8	0.9	21.9x faster	SCAN commits_fts VIRTUAL TABLE INDEX 0:M1 Before SCAN commits After SCAN commits_fts VIRTUAL TABLE INDEX 0:M1

Reading the results

Indexes pay off for selective lookups and anti-joins: the full trace for one requirement, finding commits with no task, full-text search. Whole-table aggregations (cycle time, review turnaround) read every row anyway; Postgres keeps its hash joins and doesn't change, while SQLite, which has no hash join, can switch to slower nested loops through a new index. Every index also costs write time and disk, so each one in 002_indexes.sql is tied to a query it serves.