chadtmiller.com/knotless

Switch repository

Loading…

What changes

The block and hide subqueries in Index::notified's watching and mention branches now name their index: FROM forge o INDEXED BY forge_moderation, as listItems' moderation CTE already does.

Why

Owners' notifications check blocks and hides through their index added forge_moderation's own WHERE to these subqueries so SQLite could use that index. That worked on an index built fresh. It did nothing on prod: after the deploy, the owner's notifications statement still took 600 to 700 ms on every getUnreadCount and push round (admin_logs).

Prod's index has no sqlite_stat1. forge_repo_created and forge_moderation both match (repo_did = ? AND repo_key = ?) at the same estimated cost, so SQLite breaks the tie by the order the indexes were created. On prod, forge_repo_created was made after forge_moderation, since a later version added it to an existing table. A fresh index, as in the tests, creates them in EXPRESSIONS' order, the other way round. So prod still read every record of the repository, JSON-parsed, once for each row heard of, which is the regression the owner default brought in.

Naming the index makes the choice the same however the file was built.

How it was checked

On a read-only snapshot of prod's index.db and private.sqlite (Python's sqlite3 backup API, copied off the host), I ran the query from index.rs as written, for chadtmiller.com, with grain.social as its org and the 20 owned-repository watches owned_watches gives, on SQLite 3.53, as the bundled libsqlite3-sys 0.38.2 is:

The plan now has both subqueries on SEARCH o USING INDEX forge_moderation (repo_did=? AND repo_key=?). On both databases, the rows match the query before 3mzt7fogvzsyz for no kind and for each of comments, pulls-and-issues, reviews, mentions and checks.

Shortcomings

plans.rs builds its planner's tables in EXPRESSIONS' order, as a fresh index does, so it cannot see a plan that depends on creation order. Another query that leaves SQLite to choose between two indexes with the same leading columns could plan differently on prod in the same way.

This pins both moderation subqueries in Index::notified to forge_moderation with INDEXED BY. The planner's choice then no longer depends on the order the indexes were created in. The index is created in the schema with IF NOT EXISTS. Its WHERE is the instr pair the subqueries already repeat, so SQLite can use it. listItems already pins the same index this way. I found no problems. I did not run the query or the benchmark; the prod timings come from the description. The tests don't cover the creation-order case, as the description admits.