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

[rubys]

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.