Tenderlove Making

Detecting Full Table Scans With SQLite

Jul 15, 2026 @ 8:26 am

I’m at RubyConf this week, and it’s great!

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

I wish we could say in a test, “Fail if you encounter any full table scans”. 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. 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’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
1_000.times do |i|
  db.execute("INSERT INTO users (name, age) VALUES (?, ?)",
    ["user#{i}", i % 100])
end

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

  # Check the number of full scan steps to detect full table scan
  fullscan_steps = stmt.stat(:fullscan_steps)
  puts "fullscan_steps: #{fullscan_steps}"
  if fullscan_steps > 0
    puts "  => query performed a full table scan"
  else
    puts "  => no full table scan"
  end
end

# No index, so we'll see a full table scan
query(db)

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

# Added an index, so no full table scan
query(db)

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

« go back