Ruby SQLite query plan regression

.gitignore

*.sqlite

.ruby-version

3.4.1

repro.rb

require "bundler/inline"

sqlite_version = if ENV["REGRESSION"]
  "2.0.0" # vendored sqlite: 3.45.3
else
  "1.7.3" # vendored sqlite: 3.45.2
end

gemfile do
  source "https://rubygems.org"
  gem "benchmark"
  gem "sqlite3", "~> #{sqlite_version}"
end

require "sqlite3"

puts "Using sqlite3 version: #{SQLite3::VERSION}"

file_path = "repro.sqlite"
File.delete(file_path) if File.exist?(file_path)

db = SQLite3::Database.new(file_path)
db.execute_batch(File.read("repro.sql"))

query = "SELECT * FROM products WHERE processed = 0 AND _enqueued = 0 LIMIT 1;"
puts "=== EXPLAIN QUERY PLAN ==="
puts db.execute("EXPLAIN QUERY PLAN #{query}")

puts "=== BENCHMARK QUERY (single run) ==="
puts Benchmark.measure { db.execute(query) }

repro.sql

begin deferred transaction;
CREATE TABLE products (
  `id` INTEGER PRIMARY KEY,
  `errors` TEXT,
  `processed` INTEGER DEFAULT 0,
  `_enqueued` INTEGER DEFAULT 0,
  `title` TEXT
);
WITH RECURSIVE product_numbers(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM product_numbers WHERE n < 1000000
)
INSERT INTO products (`title`) SELECT 'Product ' || n FROM product_numbers;
commit transaction;
CREATE INDEX IF NOT EXISTS products_to_import_idx ON products (`_enqueued`) WHERE processed = 0;
CREATE INDEX IF NOT EXISTS products_progress_idx ON products (`processed`, `errors`);
PRAGMA optimize=0x10002;
UPDATE products SET processed=1 WHERE id <= 900000;
EXPLAIN QUERY PLAN SELECT * FROM products WHERE processed = 0 AND _enqueued = 0 LIMIT 1;

• Running ruby repro.rb will show the query plan and performance of the query: The query uses an index and is fast.
• Running REGRESSION=1 ruby repro.rb will show the query plan and performance of the query with the 2.0.0 version of the gem. The query no longer uses an index.

See notes here: sparklemotion/sqlite3-ruby#619 (comment) It looks like a regression happened upstream - thank you @flavorjones for the help investigating!

Results of the script on my MacBook Pro.

sqlite3-ruby 1.7.3 (vendored sqlite: 3.45.2)

ruby repro.rb
WARN: Unresolved or ambiguous specs during Gem::Specification.reset:
      stringio (>= 0)
      Available/installed versions of this gem:
      - 3.1.2
      - 3.1.1
WARN: Clearing out unresolved specs. Try 'gem cleanup <gem>'
Please report a bug if this causes problems.
Using sqlite3 version: 1.7.3
=== EXPLAIN QUERY PLAN ===
4
0
0
SEARCH products USING INDEX products_to_import_idx (_enqueued=?)
=== BENCHMARK QUERY (single run) ===
  0.000024   0.000012   0.000036 (  0.000033)

sqlite3-ruby 2.0.0 (vendored sqlite: 3.45.3)

REGRESSION=1 ruby repro.rb
WARN: Unresolved or ambiguous specs during Gem::Specification.reset:
      stringio (>= 0)
      Available/installed versions of this gem:
      - 3.1.2
      - 3.1.1
WARN: Clearing out unresolved specs. Try 'gem cleanup <gem>'
Please report a bug if this causes problems.
Using sqlite3 version: 2.0.4
=== EXPLAIN QUERY PLAN ===
3
0
0
SCAN products
=== BENCHMARK QUERY (single run) ===
  0.010466   0.002315   0.012781 (  0.012779)