class ExplainAnalyzer
def initialize(relation, watched_tables:)
@relation = relation
@watched_tables = Array(watched_tables).map(&:to_s)
end
def plan
@plan ||= begin
sql = "EXPLAIN (FORMAT JSON) #{@relation.to_sql}"
raw = ActiveRecord::Base.connection.select_value(sql)
parsed = raw.is_a?(String) ? JSON.parse(raw) : raw
parsed.first.fetch("Plan")
end
end
def sequential_scan_on?
nodes.any? do |node|
node["Node Type"] == "Seq Scan" &&
@watched_tables.include?(node["Relation Name"])
end
end
def offending_table
seq = nodes.find { |n| n["Node Type"] == "Seq Scan" }
seq && seq["Relation Name"]
end
private
def nodes
@nodes ||= [].tap { |acc| walk(plan, acc) }
end
def walk(node, acc)
return if node.nil?
acc << node
Array(node["Plans"]).each { |child| walk(child, acc) }
end
end
module AssertsIndexUsage
def expect_index_usage(relation, on:)
analyzer = ExplainAnalyzer.new(relation, watched_tables: on)
if analyzer.sequential_scan_on?
raise RSpec::Expectations::ExpectationNotMetError, <<~MSG
Sequential scan detected on `#{analyzer.offending_table}`.
A supporting index is probably missing.
Query:
#{relation.to_sql}
Plan:
#{JSON.pretty_generate(analyzer.plan)}
MSG
end
analyzer
end
end
RSpec.configure do |config|
config.include AssertsIndexUsage, type: :model
end
require "rails_helper"
RSpec.describe Order, type: :model do
describe "query plans" do
let(:customer) { create(:customer) }
before do
# Give the planner enough rows to prefer an index over a seq scan.
create_list(:order, 500, customer: customer)
ActiveRecord::Base.connection.execute("ANALYZE orders")
end
it "uses the (customer_id, created_at) index for recent orders" do
relation = Order.recent_for_customer(customer.id).limit(20)
expect_index_usage(relation, on: :orders)
end
it "uses an index when filtering by status" do
relation = Order.where(status: "pending").order(created_at: :desc)
expect_index_usage(relation, on: :orders)
end
end
end
This snippet wires a Postgres EXPLAIN check into the test suite so that a query which falls back to a sequential scan on a large table fails loudly during CI rather than silently degrading in production. The pattern is useful because a missing index is invisible in a small dev database — the planner happily seq-scans a hundred rows — but the same query melts a table with millions. Encoding the expectation as a test turns an operational surprise into a fast, deterministic failure.
In ExplainAnalyzer, the class takes an ActiveRecord relation and asks Postgres for the machine-readable plan via EXPLAIN (FORMAT JSON). It deliberately avoids ANALYZE so the query is not actually executed — only planned — which keeps the check cheap and side-effect free. The raw SQL is produced with relation.to_sql, and connection.select_value returns the JSON plan that walk recurses through. sequential_scan_on? flattens the nested Plans tree and looks for any Seq Scan node whose Relation Name matches a table under scrutiny, ignoring scans on tiny lookup tables where an index would be pointless.
AssertsIndexUsage is a thin RSpec helper mixed into example groups. Its expect_index_usage method builds an ExplainAnalyzer, and if a seq scan is found it raises with the offending table name and the full plan, so the failure message tells an engineer exactly which query and table need an index. Returning the analyzer lets callers make further assertions if needed.
orders_spec.rb shows the intended use: a scope like Order.recent_for_customer is passed straight to expect_index_usage, guarding the composite index on (customer_id, created_at). Because the relation is lazy, nothing hits the database until EXPLAIN runs, so the spec stays fast.
The main trade-off is that plans depend on table statistics and row counts, so a fresh CI database with empty tables can mislead the planner into preferring a seq scan even when an index exists. Seeding representative volume, or running ANALYZE after seeds, keeps the check honest. This approach complements rather than replaces load testing, but it catches the most common and most expensive mistake early.
Related snips
class CommentsController < ApplicationController
before_action :set_post
def create
@comment = @post.comments.build(comment_params)System test: asserting Turbo Stream responses
class Post < ApplicationRecord
belongs_to :author, class_name: 'User'
has_many :comments, dependent: :destroy
scope :published, -> { where.not(published_at: nil).where('published_at <= ?', Time.current) }
scope :draft, -> { where(published_at: nil) }ActiveRecord scopes for reusable query logic
module Api
module V1
class UsersController < BaseController
def show
user = User.includes(:profile).find(params[:id])ETags for conditional requests and caching
class PostsController < ApplicationController
def index
@posts = Post.includes(:author)
.order(created_at: :desc)
.page(params[:page])
.per(10)Turbo Frames: infinite scroll with lazy-loading frame
package dbutil
import (
"context"
"github.com/jackc/pgconn"Retry Postgres serialization failures with bounded attempts
require "csv"
class PeopleCsvStream
include Enumerable
HEADERS = %w[id full_name email signed_up_at plan].freezeResilient CSV Export as a Streamed Response
Share this code
Here's the card — post it anywhere.
