Tenderlove Making -

Detecting Full Table Scans With SQLite

Detecting Full Table Scans With SQLite

Jul 15, 2026 @<br>8:26 am

I&rsquo;m at RubyConf this week, and it&rsquo;s great!

I recently read that lobste.rs is now running on SQLite.<br>One part from the post caught my attention:

I wish we could say in a test, &ldquo;Fail if you encounter any full table scans&rdquo;. Which would have caught the perf issues we experienced during the first deploy.

SQLite collects information about prepared statements and exposes those statistics though an API.<br>The upshot of this is that we can tell whether a statement did a full table scan after executing the statement without using an EXPLAIN.

Here&rsquo;s an example program that demonstrates detecting a query did a full table scan:

db = SQLite3::Database.new(":memory:")

db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")

# Insert a bunch of records<br>1_000.times do |i|<br>db.execute("INSERT INTO users (name, age) VALUES (?, ?)",<br>["user#{i}", i % 100])<br>end

# Prepare a statement and query it<br>stmt = db.prepare("SELECT * FROM users WHERE age = ?")<br>stmt.bind_param(1, 42)<br>stmt.to_a

# Check the number of full scan steps, we see a bunch because the query<br># did a full table scan<br>fullscan_steps = stmt.stat(:fullscan_steps)<br>puts "fullscan_steps: #{fullscan_steps}"<br>if fullscan_steps > 0<br>puts "=> query performed a full table scan"<br>end

# Create an index<br>db.execute("CREATE INDEX idx_users_age ON users(age)")

# Now the query won't do a full table scan<br>stmt2 = db.prepare("SELECT * FROM users WHERE age = ?")<br>stmt2.bind_param(1, 42)<br>stmt2.to_a

puts "after adding index, fullscan_steps: #{stmt2.stat(:fullscan_steps)}"

Feels like we could integrate this in to Rails and warn or raise in test / development.<br>I&rsquo;m not sure if we&rsquo;d want to check this all the time in production, but maybe it would be fine?

" go back