lobsters: fixture DB ships without planner statistics; one misplanned query dominates several routes

The lobsters fixture DB (benchmarks/lobsters/db/production.sqlite3) carries no sqlite_stat1 table — ANALYZE has never been run on it — and benchmark.rb's in-memory copy inherits that. Without planner statistics, SQLite misplans the hottest-stories query (the scope under /, /rss, and /recent, which the route mix visits every iteration): it walks index_stories_on_merged_story_id across all ~11k stories, evaluates the correlated hidden_stories subquery per row, then temp-B-tree sorts, instead of reading hotness_idx in order and stopping at the LIMIT.

Repro, runnable from benchmarks/lobsters/ with only the sqlite3 gem (the query text is exactly what ActiveRecord generates for a logged-in user; .to_sql on the StoryRepository#hottest relation produces it verbatim):

# repro.rb — run from benchmarks/lobsters/
require "sqlite3"

Q = <<~SQL
  SELECT "stories".* FROM "stories" WHERE "stories"."merged_story_id" IS NULL
    AND "stories"."is_deleted" = FALSE AND (score >= 0)
    AND NOT (EXISTS (SELECT TRUE FROM "hidden_stories"
      WHERE (hidden_stories.story_id = stories.id) AND "hidden_stories"."user_id" = 12))
  ORDER BY hotness LIMIT 26 OFFSET 0
SQL

mem  = SQLite3::Database.new(":memory:")
file = SQLite3::Database.new("db/production.sqlite3", readonly: true)
bk = SQLite3::Backup.new(mem, "main", file, "main")
bk.step(-1)
bk.finish
file.close

def bench(db, label)
  db.execute(Q) # warm
  t0 = Process.clock_gettime(Process::CLOCK_MONOTONIC)
  50.times { db.execute(Q) }
  ms = (Process.clock_gettime(Process::CLOCK_MONOTONIC) - t0) / 50 * 1000
  plan = db.execute("EXPLAIN QUERY PLAN #{Q}").map { |r| r[3] }.join(" | ")
  puts format("%-12s %7.3f ms   %s", label, ms, plan)
end

bench(mem, "no stats:")
mem.execute("ANALYZE")
bench(mem, "with stats:")

Output on an M-series Mac (CRuby 4.0.5, sqlite3 2.9.5):

no stats:      4.936 ms   SEARCH stories USING INDEX index_stories_on_merged_story_id (merged_story_id=?) | CORRELATED SCALAR SUBQUERY 1 | SEARCH hidden_stories USING COVERING INDEX index_hidden_stories_on_user_id_and_story_id (user_id=? AND story_id=?) | USE TEMP B-TREE FOR ORDER BY
with stats:    0.090 ms   SCAN stories USING INDEX hotness_idx | CORRELATED SCALAR SUBQUERY 1 | SEARCH hidden_stories USING COVERING INDEX index_hidden_stories_on_user_id_and_story_id (user_id=? AND story_id=?)

~55× on this query, and it runs many times per benchmark iteration. In a per-route breakdown of the frozen visit sequence, this one query is ~73% of /rss's wall time and similarly dominates / (hottest) and /recent — measured identically on stock Rails and on a transpiled variant of the app, so it's a property of the fixture, not the framework.

Why it may matter for ruby-bench's purpose: this is constant C-side SQLite time, identical across every Ruby/JIT configuration, so it dilutes exactly the Ruby-side differences the benchmark exists to measure. A production database would have statistics (MySQL/MariaDB in real lobsters keeps them automatically; Rails' SQLite3 adapter in a long-running deployment accumulates them via PRAGMA optimize), so the no-stats plan is an artifact of seeding from a bare fixture rather than something a real deployment would exhibit.

Two possible remedies, either of which resolves it:

1. Run ANALYZE once in benchmark.rb right after the SQLite3::Backup restore (outside the timed region; ~100 ms one-time).
2. Ship the fixture DB with statistics baked in (sqlite3 db/production.sqlite3 ANALYZE once — sqlite_stat1 is an ordinary table, so the online-backup copy carries it into the in-memory DB).

The trade-off is score continuity: iteration times drop noticeably (in my runs, ~15% for the stock-Rails sequence), so historical comparisons would need a flag day. Results between Ruby versions/JITs remain comparable in kind either way — every configuration pays or saves the same amount. Happy to send a PR for either variant if there's interest.

Commencez dans benchmarks/lobsters/benchmark.rb et examinez comment la sauvegarde restaure db/production.sqlite3 en mémoire. Exécutez la reproduction fournie depuis benchmarks/lobsters/ pour confirmer les plans de requête avec et sans statistiques, puis comparez les deux emplacements proposés pour les conserver. C’est terminé lorsque le benchmark basé sur des fixtures utilise les statistiques du planificateur en dehors de la région chronométrée et que les temps des routes concernées ainsi que le plan de requête sont vérifiés.