How Changing SQLite's JOIN Order Took Processing from 173 Seconds to 1 Second
Rewriting
JOINasCROSS JOINtook us from 173 seconds to 1 second.
The takeaway
While working on my own CLI tool, ghusage, I was getting frustrated by how long CI took on every PR. It turned out that generating reports from SQLite's cache took 173 seconds per run. I rewrote JOIN as CROSS JOIN to fix the loop order, and got the time down to the 1-second range. Wow.
- The cause was SQLite choosing the wrong tables for the outer and inner loops of a nested loop
- SQLite made that choice because it didn't have table statistics
-
EXPLAIN QUERY PLANshowedSEARCHfor both rows, so the plan alone didn't reveal the performance problem
| Condition | Before | After |
|---|---|---|
| CI (macos-15, one 7-day aggregation, debug build) | 173 seconds | 1.7–2.1 seconds |
| CI (macos-15, 30 days) | 240 seconds | 2.3–2.7 seconds |
| Local reproduction (2,000 queries, no statistics) | 260.8 seconds | 0.79 seconds |
Local reproduction (same as above, after ANALYZE) | 0.33 seconds | 0.43 seconds |
By the way, ghusage is a CLI / TUI that aggregates my GitHub activity, including what happens inside PRs. Install it with npm install -g ghusage.
- Documentation: https://ghusage.under18.dev/
- Source code: https://github.com/tanahiro2010/ghusage
Here's the most important point of this article, up front.
SEARCH in EXPLAIN QUERY PLAN does not mean “fast.” SEARCH only indicates that an index was used to filter rows. It doesn't tell you how many rows remain after filtering. If every row has the same value in the leftmost column of an index, SEARCH can be no different from reading every row. And without statistics, SQLite doesn't know that.
This article expands on my lightning talk, “How Changing SQLite's JOIN Order Took Processing from 173 Seconds to 1 Second,” with reproduction experiments and details about SQLite's query planner that I couldn't fit into the talk. It's for people who use SQLite as an embedded database and have written a JOIN.
1. What is ghusage? (A little promotion)
ghusage is a CLI and terminal UI for aggregating your GitHub activity. It's written in Rust and can be installed from npm.
npm install -g ghusage ghusage sync # fetch the last 30 days ghusage # overview of the last 7 days ghusage tui # terminal UI The tagline is “Know what you actually did on GitHub.”
The grass in your GitHub profile (the contribution graph) only tells you how many times you were active on each day. ghusage treats a PR as a process. It counts who did what and when, including commits in PRs, reviews and their states, the difference between inline and conversation comments, review threads, and force pushes.
Here's what my actual activity over the last week looks like:
❯ ghusage Overview tanahiro2010 2026-11-16 00:00 → 2026-11-22 16:23 (Asia/Tokyo) Commits 1225 (80 merge) PRs created 74 PRs merged (yours) 71 Merges you performed 72 PRs closed without merge 1 Open PRs (current) 25 Reviews submitted 4 (approved 1, changes requested 1, commented 2) on 2 PRs Reviews received 81 (73 from bots) Comments written 209 (conversation 80, inline 125, review summary 4) Comments received 363 (conversation 113, inline 172, review summary 78) Review thread replies 122 PR commits 510 Force pushes 3 Issues opened 16, closed 14, reopened 1 Active PRs 87 (with anyone's activity: 101) PR internal events 965 CI results (not counted as activity) passed 667, failed 2, pending 31 Active days 7 Top repositories Repository Vis Commits PRs Merged Reviews Comments Issues Active PRs PR events Last activity under18dev/veritas public* 657 21 20 0 185 0 21 537 2026-11-22 16:14 under18dev/ippon-slides public* 101 27 29 0 9 0 29 178 2026-11-22 12:22 tanahiro2010/ghusage public* 125 6 5 0 0 0 6 106 2026-11-22 16:15 under18dev/under18dev private* 143 0 0 0 0 0 0 0 2026-11-21 23:27 under18dev/services public* 32 5 9 0 2 0 12 57 2026-11-20 21:48 Of the 79 reviews I received, 71 were from bots. The number of times my PRs were merged (70) and the number of times I merged PRs myself (71) are also shown separately. CI passed 621 times, but as “not counted as activity” indicates, those results aren't counted as my activity.
I'm particular about how things are counted.
- Count me, other people, and bots separately. Comments I wrote and comments I received are distinct, as are merges I performed and merges of my PRs. CI results are never counted as my activity
- Define every metric. Unique counts, such as “number of PRs reviewed,” are explicitly labeled as such
- Don't hide gaps. Every report shows data coverage. Add
--require-completeto make a script fail if it couldn't retrieve all the data - Run locally. Data is stored in a local SQLite database and synced incrementally. You can generate reports without a network connection using
--offline. There is no telemetry - Output as tables, JSON, CSV, or TUI. The JSON schema is versioned
There are also daily, weekly, monthly, and yearly reports, per-repository aggregations, and timelines for individual PRs (ghusage pr <URL> --timeline). See the documentation for details.
- Documentation: https://ghusage.under18.dev/
- GitHub: https://github.com/tanahiro2010/ghusage
- npm: https://www.npmjs.com/package/ghusage
2. Background: all reports come from the SQLite cache
ghusage doesn't calculate reports directly from GitHub API responses. It first saves GitHub data to a local SQLite database (ghusage.db), and both CLI reports and the TUI are generated from this cache. SQLite is bundled using the bundled feature of rusqlite, and the version is 3.54.1.
flowchart LR G[GitHub API] -->|ghusage sync| C[(ghusage.db<br/>SQLite)] C --> R[期間を指定してレポート] C --> T[TUI] The part that runs each time you generate a report is the right half: reading from the cache and aggregating the data. That's where the slowdown was.
Events and accounts
The cache centers on two tables.
CREATE TABLE activity_events ( event_id TEXT NOT NULL PRIMARY KEY, parent_pr_id TEXT REFERENCES pull_requests(id), occurred_at INTEGER NOT NULL, -- ほかの列は省略 ); CREATE INDEX activity_events_parent_pr ON activity_events(parent_pr_id) WHERE parent_pr_id IS NOT NULL; CREATE TABLE account_events ( account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, event_id TEXT NOT NULL REFERENCES activity_events(event_id) ON DELETE CASCADE, PRIMARY KEY (account_id, event_id) ) WITHOUT ROWID; CREATE INDEX account_events_event ON account_events(event_id); activity_events contains the events themselves. Each commit, review, comment, merge, force push, and other event gets a row. Events that happen inside a PR have a parent_pr_id.
account_events is a mapping table that records which events belong to the cache for each account being analyzed. ghusage is generally used to analyze the authenticated user, but you can also analyze another account with --user. Since the same event can be visible to multiple accounts being analyzed, the event data and mapping table are separate.
I normally analyze only one account: my own. That means nearly every row in account_events has the same account_id value. That's where the 173 seconds began.
Why read events outside the date range?
When generating a report, ghusage first reads events within the specified date range.
Some metrics need more than that. For example, the time it took to complete one of my PRs, shown by ghusage prs --details, and metrics like “Time to first review” (from creation to the first review) and “Creation to merge” (from creation to merge) can't be calculated from in-range events alone if the PR was created earlier. So for PRs with activity in the date range, ghusage also reads all events outside that range.
flowchart LR A[期間内のイベントを読む] --> B[動きのあった PR を集める] B --> C{PR ごとにループ} C --> D[その PR の全イベントを読む] D --> C This was the query called inside the loop.
SELECT {EVENT_COLS} FROM activity_events e JOIN account_events ae ON ae.event_id = e.event_id WHERE ae.account_id = ?1 AND e.parent_pr_id = ?2 It retrieves all events for one PR belonging to one account being analyzed. That's it. parent_pr_id has an index, and account_events has a composite primary key. The indexes were there.
3. The trigger: CI was painfully slow
On the morning of November 22, 2026, I noticed something was wrong with CI.
The macOS job alone was taking more than 10 minutes. The benchmark workflow was even worse: it repeatedly got cancelled after hitting its one-hour timeout.
I opened the logs and followed the timestamps on the test lines.
macos-15 03:12:50.4055930 Testing cached 7-day aggregation (load + overview) macos-15 03:15:43.5606920 Success macos-15 03:15:43.5609030 Testing cached 30-day aggregation (load + overview) macos-15 03:19:43.3846610 Success The 7-day aggregation started at 03:12:50 and finished at 03:15:43.
173 seconds.
Measuring it: over two minutes on every OS
This test is a benchmark that runs as part of cargo test --workspace --all-targets. It measures one complete run of loading data from the cache and generating an overview report. When Criterion runs in test mode, it runs each benchmark once and outputs Testing ... and Success. The difference between the timestamps on these two lines gives us the time for one aggregation.
The conditions were a cache with 100,000 events, 2,000 PRs, and 40 repositories, using a debug build.
| OS | 7 days | 30 days |
|---|---|---|
| ubuntu-latest | 131.24 seconds | 168.91 seconds |
| macos-15 | 173.16 seconds | 239.82 seconds |
| windows-latest | 133.08 seconds | 172.47 seconds |
It wasn't an OS-specific issue. Every OS took more than two minutes.
In the benchmark data, almost all 2,000 PRs have activity even in the 30-day date range. That means the query from §2 was called about 2,000 times per aggregation.
4. EXPLAIN QUERY PLAN showed SEARCH
First, I checked the query plan.
QUERY PLAN |--SEARCH ae USING PRIMARY KEY (account_id=?) `--SEARCH e USING INDEX sqlite_autoindex_activity_events_1 (event_id=?) SQLite executes JOINs using nested loops. This plan means:
- In the outer loop, look up
account_eventsby the primary keyaccount_id = ? - In the inner loop, look up
activity_eventsbyevent_idfor each row returned - Discard rows that don't match
parent_pr_id = ?
Both rows show SEARCH, and both use indexes. There's no SCAN, which would indicate a full table scan.
It looked fine.
Gotcha ①: account_id had the same value in every row
As I mentioned in §2, I normally analyze only one account with ghusage: my own. The benchmark cache also had only one account, so all 100,000 rows in account_events had the same account_id value.
Filtering by account_id = 1 still leaves all 100,000 rows. Even though the plan says SEARCH, it's no different from a full table scan.
And this was repeated once for every PR.
[
100{,}000 \text{ rows} \times 2{,}000 \text{ times} = 200 \text{ million rows}
]
With the loops reversed, filtering by parent_pr_id first leaves about 50 rows per PR. Checking account_events one row at a time after that means just 100,000 rows in total.
flowchart TB subgraph before[修正前] A1["account_events を account_id で引く<br/>10 万行"] --> B1["1 行ずつ activity_events を引く<br/>parent_pr_id が違えば捨てる"] end subgraph after[修正後] A2["activity_events を parent_pr_id で引く<br/>約 50 行"] --> B2["1 行ずつ account_events を引く"] end That was the whole cause: the outer and inner loops were reversed.
5. Why did SQLite choose the slower option?
With an INNER JOIN, SQLite decides which table goes in the outer loop. The official documentation says so, too.
Inner joins can be freely reordered.
So why did it choose the slower option?
Without statistics, it estimates “about 10 rows”
SQLite uses a statistics table called sqlite_stat1 to understand how the data in a table is distributed. These statistics are created by running ANALYZE.
There was no ANALYZE or PRAGMA optimize in ghusage's code.
Without statistics, SQLite makes this estimate:
the default guess is that there are an average of 10 duplicates for every value in the left-most column of the index.
In other words, it estimates that there are about 10 rows per value in the leftmost column of an index.
Applying this estimate to our query gives us:
- Look up
account_eventsbyaccount_id = ?→ about 10 rows - Look up
activity_eventsbyparent_pr_id = ?→ about 10 rows
Either loop order appears to have the same cost. The actual row counts differed by a factor of 2,000—100,000 rows versus 50—but SQLite couldn't see that difference.
And it picked the worse option.
The official docs call this a low-quality index
SQLite's official documentation has a section that names this pattern directly.
A low-quality index (for the purpose of this checklist) is one where there are more than 10 or 20 rows in the table that have the same value for the left-most column of the index.
If you must use a low-quality index, be sure to run ANALYZE.
An index whose leftmost column has the same value in more than 10–20 rows. The primary key of account_events, (account_id, event_id), fits this definition exactly because the leftmost column, account_id, has the same value in every row.
The column order (account_id, event_id) makes sense for a composite primary key. It's ideal for queries that retrieve all events for an account, and other ghusage queries do use it that way. Combined with the assumption that there's only one user, it becomes a trap only when this table is placed in the outer loop.
6. The fix: use CROSS JOIN to pin the order
Here's the updated query.
SELECT {EVENT_COLS} FROM activity_events e CROSS JOIN account_events ae ON ae.event_id = e.event_id WHERE e.parent_pr_id = ?2 AND ae.account_id = ?1 I only changed JOIN to CROSS JOIN.
SQLite guarantees that it won't swap the left and right sides of a CROSS JOIN.
The CROSS JOIN operator is commutative, in theory. However, SQLite chooses to never reorder tables in a CROSS JOIN. This provides a mechanism by which the programmer can force SQLite to choose a particular loop nesting order.
As written, activity_events is now the outer loop, and account_events is the inner loop.
Terminology: CROSS JOIN
In standard SQL, aCROSS JOINwithout anONclause means a Cartesian product (all combinations). ACROSS JOINwith anONclause returns the same result as an INNER JOIN. Keeping the order fixed is SQLite-specific behavior; doing the same thing in PostgreSQL or MySQL won't fix the order.
Here's the query plan after the change.
QUERY PLAN |--SEARCH e USING INDEX activity_events_parent_pr (parent_pr_id=?) `--SEARCH ae USING COVERING INDEX account_events_event (event_id=? AND account_id=?) There's a reason the second line says COVERING INDEX. account_events is a WITHOUT ROWID table, so its secondary index, account_events_event(event_id), also contains the primary key column account_id. Both event_id and account_id can be checked using the index alone, without reading the table itself.
The activity_events_parent_pr index in the first line is a partial index with WHERE parent_pr_id IS NOT NULL. Because the query has e.parent_pr_id = ?2, SQLite can determine that this row isn't NULL and use the partial index.
I also changed the order of the conditions in the WHERE clause, but that was just for readability. SQLite doesn't change the plan based on the order of conditions in a WHERE clause. The order is determined by CROSS JOIN.
Turn the query plan into a test
If someone changes it back to JOIN, we'll be back to 173 seconds. A regular JOIN looks more natural, so there's a good chance someone might change it and ask, “Why CROSS JOIN?” That includes future me.
So I turned the query plan itself into a test.
/// Events of one pull request seen by an account. `CROSS JOIN` keeps /// SQLite from driving the join from `account_events`, which would scan every /// event of the account once per pull request. fn pr_events_sql() -> String { format!( "SELECT {EVENT_COLS} FROM activity_events e \ CROSS JOIN account_events ae ON ae.event_id = e.event_id \ WHERE e.parent_pr_id = ?2 AND ae.account_id = ?1" ) } #[test] fn pull_request_events_are_found_through_the_parent_index() { let dir = tempfile::tempdir().unwrap(); let store = Store::open(&dir.path().join("t.db")).unwrap(); let plan: Vec<String> = query_list( &store.conn, &format!("EXPLAIN QUERY PLAN {}", pr_events_sql()), params![1, "PR_x"], |r| Ok(r.get::<_, String>(3)?), ) .unwrap(); assert!( plan[0].contains("activity_events_parent_pr"), "unexpected plan: {plan:?}" ); } The fourth column of EXPLAIN QUERY PLAN contains the plan description. This checks that activity_events_parent_pr appears in the first row, meaning it's in the outer loop.
This test runs against an empty database. It doesn't depend on the amount of data, so it's fast and can run every time in CI. On the other hand, it doesn't check the plan with data in the database. I confirmed in the reproduction in the next section that the plan is the same for an empty database without statistics and a database with 100,000 rows and no statistics.
Measuring it: CI after the fix
I compared the CI runs triggered by pushes immediately before and after the fix. The only commit between those two runs was the fix itself.
| OS | 7 days | 30 days |
|---|---|---|
| ubuntu-latest | 131.24 seconds → 1.97 seconds | 168.91 seconds → 2.55 seconds |
| macos-15 | 173.16 seconds → 1.67 seconds | 239.82 seconds → 2.29 seconds |
| windows-latest | 133.08 seconds → 2.23 seconds | 172.47 seconds → 2.92 seconds |
The “1 second” in the title refers to the 1.67-second result for 7 days on macOS. It got roughly 70–100 times faster on every OS.
These are times from a single run using a debug build, so there is some variation. In another run of the same commit, the 7-day aggregation on Ubuntu took 1.34 seconds.
7. Reproducing it locally
The CI logs alone couldn't tell me whether the loop order was really the cause, or whether other factors were involved. I reproduced the issue locally by extracting only the relevant columns and indexes from ghusage's schema.
The data consists of 100,000 events, 2,000 PRs, one event in five that doesn't belong to a PR, and one account. This matches the shape of the data in ghusage's benchmark.
CREATE TABLE activity_events ( event_id TEXT NOT NULL PRIMARY KEY, event_type TEXT NOT NULL, parent_pr_id TEXT, occurred_at INTEGER NOT NULL, meta TEXT NOT NULL); CREATE INDEX activity_events_occurred ON activity_events(occurred_at); CREATE INDEX activity_events_parent_pr ON activity_events(parent_pr_id) WHERE parent_pr_id IS NOT NULL; CREATE TABLE account_events ( account_id INTEGER NOT NULL, event_id TEXT NOT NULL, PRIMARY KEY (account_id, event_id)) WITHOUT ROWID; CREATE INDEX account_events_event ON account_events(event_id); WITH RECURSIVE n(i) AS (SELECT 0 UNION ALL SELECT i+1 FROM n WHERE i < 99999) INSERT INTO activity_events SELECT 'EV_'||i, CASE WHEN i%5=0 THEN 'commit' ELSE 'review' END, CASE WHEN i%5=0 THEN NULL ELSE 'PR_'||(i%2000) END, 1700000000 + i*315, '{}' FROM n; INSERT INTO account_events SELECT 1, event_id FROM activity_events; I wrote a Python script to run the query for 2,000 PRs.
import sqlite3, sys, time OLD="SELECT e.* FROM activity_events e JOIN account_events ae ON ae.event_id = e.event_id WHERE ae.account_id = ?1 AND e.parent_pr_id = ?2" NEW="SELECT e.* FROM activity_events e CROSS JOIN account_events ae ON ae.event_id = e.event_id WHERE e.parent_pr_id = ?2 AND ae.account_id = ?1" db=sys.argv[1]; analyze=len(sys.argv)>2 c=sqlite3.connect(db) print("sqlite", sqlite3.sqlite_version, "analyze" if analyze else "no-stats") if analyze: c.execute("ANALYZE") else: c.execute("DROP TABLE IF EXISTS sqlite_stat1") for name,q in (("old",OLD),("new",NEW)): plan=[r[3] for r in c.execute("EXPLAIN QUERY PLAN "+q,(1,"PR_7"))] for _ in range(2): t=time.perf_counter(); n=0 for p in range(2000): n+=len(c.execute(q,(1,f"PR_{p}")).fetchall()) dt=time.perf_counter()-t print(f"{name}: {dt:.2f}s rows={n} plan={plan}") I ran it twice and recorded the time from the second run.
The old query took almost nine minutes for two runs. When it was time for the new query, it finished in an instant.
Measuring it: 330 times faster without statistics
The environment was macOS 26.5, Apple M4, and Python 3.10.4's sqlite3 module (SQLite 3.51.0).
| Query | Total for 2,000 runs | Outer loop |
|---|---|---|
JOIN | 260.82 seconds | SEARCH ae USING PRIMARY KEY (account_id=?) |
CROSS JOIN | 0.79 seconds | SEARCH e USING INDEX activity_events_parent_pr |
About 330 times faster.
Both queries returned 80,000 rows, so the results were identical.
The query plans also matched those from ghusage in CI. The loop order alone made this difference.
8. With ANALYZE, the original query is fast as-is
This was the thing I most wanted to verify.
If missing statistics were the cause, SQLite should pick the right option on its own once it has statistics. I ran ANALYZE once on the same database and tried again.
Measuring it: after ANALYZE
| Query | Total for 2,000 runs | Outer loop |
|---|---|---|
JOIN | 0.33 seconds | SEARCH e USING INDEX activity_events_parent_pr |
CROSS JOIN | 0.43 seconds | SEARCH e USING INDEX activity_events_parent_pr |
The original JOIN took 0.33 seconds without any changes to the query. Its plan was also the same as the CROSS JOIN plan.
Since these times were measured only once, I think the difference between 0.33 and 0.43 seconds is within the margin of error.
Analysis: what was in sqlite_stat1?
After ANALYZE, sqlite_stat1 looked like this:
account_events|account_events_event|100000 1 account_events|account_events|100000 100000 1 activity_events|activity_events_parent_pr|80000 50 activity_events|activity_events_occurred|100000 1 activity_events|sqlite_autoindex_activity_events_1|100000 1 The numbers in the third column are “number of rows, number of rows per value in the leftmost column, number of rows per value in the first two columns, and so on.”
-
account_events|account_events|100000 100000 1means that specifying onlyaccount_idin the primary key returns 100,000 rows, while specifyingevent_idtoo returns one row -
activity_events_parent_pr|80000 50means the partial index has 80,000 rows, with 50 rows perparent_pr_idvalue
Without statistics, both estimates were “about 10 rows.” Once SQLite knows the actual numbers are 100,000 and 50, it has no reason to choose the wrong plan.
The official docs say so, too
SQLite's official documentation was updated in 2024 with the following:
Update 2024: The query planner has been improved so much over the years that you should never need to use any of the hacks described below. The capabilities described below are still available, for backwards compatibility. But you shouldn't use them.
These “hacks” include both unlikely() and using CROSS JOIN to pin the loop order. What the docs recommend instead is running ANALYZE if you have to use a low-quality index, as well as using PRAGMA optimize.
Applications with short-lived database connections should run "PRAGMA optimize;" once, just prior to closing each database connection.
The CROSS JOIN in this case was a technique the official docs say you “shouldn't need” anymore.
So was using CROSS JOIN wrong?
I don't think it was wrong.
CROSS JOIN works reliably. It doesn't depend on whether statistics are available, the data distribution, or changes in the query planner between SQLite versions. It runs in the order you write, and you can lock that behavior in with a test. Given a CI job that was stuck for an hour, I think it was the right way to get a reliable fix in place first.
That said, statistics are the root cause. There's room to add PRAGMA optimize to ghusage.
There's another consideration. When ghusage can't write to the cache file, it reopens it as read-only (SQLITE_OPEN_READ_ONLY) so it can still generate reports. ANALYZE writes to sqlite_stat1, so it can't run on this connection. If we rely on statistics, then opening the database as read-only means running without them. With CROSS JOIN, the order stays the same even in that case.
I'll decide whether to remove CROSS JOIN and rely on PRAGMA optimize after comparing performance with different data sizes.
9. CI got faster, too
This fix also changed the overall CI time. Here are the times for each step in the same two runs as in §6.
| OS | cargo test | Entire job |
|---|---|---|
| ubuntu-latest | 364 seconds → 92 seconds | 7.8 minutes → 3.2 minutes |
| ubuntu-24.04-arm | 588 seconds → 115 seconds | 11.3 minutes → 3.3 minutes |
| macos-15 | 509 seconds → 78 seconds | 10.7 minutes → 3.0 minutes |
| macos-15-intel | 553 seconds → 204 seconds | 13.3 minutes → 8.2 minutes |
| windows-latest | 467 seconds → 175 seconds | 11.2 minutes → 6.4 minutes |
Of the 509 seconds spent on cargo test on macOS, these two benchmarks alone accounted for 413 seconds (173 + 240).
The total CI wait time is determined by the slowest job. It went from about 13 minutes to about 8 minutes. The current bottleneck is macos-15-intel at 8.2 minutes.
This sped up not only aggregation for users, but also the development loop.
10. Another opportunity to improve performance
There's one more JOIN with a similar shape: the first query, which reads events in the date range.
SELECT {EVENT_COLS} FROM activity_events e JOIN account_events ae ON ae.event_id = e.event_id WHERE ae.account_id = ?1 AND ((e.occurred_at BETWEEN ?2 AND ?3) OR (e.author_date BETWEEN ?2 AND ?3)) Using the simplified schema from §7, I checked the query plan with only one side of the OR (the occurred_at side). Without statistics, the outer loop was the primary key of account_events.
QUERY PLAN |--SEARCH ae USING PRIMARY KEY (account_id=?) `--SEARCH e USING INDEX sqlite_autoindex_activity_events_1 (event_id=?) This query only runs once, not inside a loop, so it reads 100,000 rows just once. It's not going to make a difference on the scale of 173 seconds. Still, for a 30-day range, it reads 100,000 rows when it could get by with around 8,000. The actual schema has a partial index on author_date and the OR condition, so I'll need to verify whether it produces the same plan in ghusage itself.
Adding PRAGMA optimize should speed this one up as well. I'll try it next, along with the comparison in §8.
11. Summary
- The 173-second delay was caused by the outer and inner loops of a nested loop being in the wrong order
- Changing
JOINtoCROSS JOINpinned the order and brought the time down to the 1-second range - SQLite chose the wrong order because it had no statistics and estimated that both indexes would return “about 10 rows per value”
- After running
ANALYZE, SQLite picks the right order even with the originalJOIN. The official docs recommend this approach, too - CI wait time also went from about 13 minutes to about 8 minutes
When EXPLAIN QUERY PLAN shows SEARCH, think about how many rows will remain after...

