Benedictus

A query bem-dita.

A Ruby gem that turns the obscure PostgreSQL EXPLAIN output into a clear, colored, human-readable tree — for any ActiveRecord scope, query object, or expression that resolves to an ActiveRecord::Relation.

bundle exec benedictus "User.active.with_recent_orders" --analyze

Query: User.active.with_recent_orders
Total cost: 1834.21    Execution time: 412.9 ms    Planning: 1.30 ms    Rows: 1,204

└─ Hash Join (cost=234.10..1834.21  actual=412.3 ms  rows=1,204)
   Hash Cond: (orders.user_id = users.id)
   ├─ Seq Scan on orders (cost=0.00..1500.00  actual=389.2 ms  rows=120,000)
   │  ⚠  Seq Scan on a table with ~12000 estimated rows
   │     Consider adding an index that matches the filter on `orders`.
   │  ⚠  Plan estimated 12000 rows, actual was 120000 (10.0x drift).
   │     Run `ANALYZE orders` to refresh planner statistics.
   └─ Hash (cost=234.10..234.10  actual=18.70 ms  rows=1,500)
      └─ Index Scan using index_users_on_active on users (cost=0.42..234.10  actual=18.50 ms  rows=1,500)
         Index Cond: (active = true)

Executed inside rolled-back transaction. Note: side effects of volatile
Postgres functions (setval, pg_advisory_lock, pg_notify, dblink, …) are
NOT reverted by ROLLBACK.

Why?

PostgreSQL EXPLAIN is powerful but hard to read at a glance:

  • The indentation-based tree is fragile and unfamiliar.
  • Costs and times are interleaved with rows in a wall of text.
  • Critical issues — sequential scans on large tables, bad row estimates, sorts spilling to disk — aren't visually highlighted.
  • You have to mentally translate a Ruby scope chain back to SQL before the plan even starts to make sense.

Benedictus reads the plan, applies a small set of heuristics, and renders it the way you'd actually want to read it.

Installation

In your application's Gemfile, in the :development group:

group :development do
  gem "benedictus"
end

Then:

bundle install

Three executables are installed: benedictus, bnd, bemdito. They are interchangeable.

Requirements

  • Ruby >= 3.1
  • ActiveRecord >= 6.1 (verified against Rails 8.x; 6.1 / 7.x should work but aren't covered by CI yet)
  • PostgreSQL (any version supported by your Rails app)

Usage

The first positional argument is a Ruby expression that resolves to an ActiveRecord::Relation — or a query object responding to .call, or anything responding to .to_sql.

bundle exec benedictus "User.active.recent"
bundle exec benedictus "User.where(active: true).order(:created_at)"
bundle exec benedictus "OrdersQuery.new(user_id: 123).call"
bundle exec bnd        "User.active" --analyze --sql

Options

Flag Description Default
--analyze / --analyse Run EXPLAIN ANALYZE inside a rolled-back transaction false
--format FORMAT Output format: tree, json, or raw tree
--sql Print the SQL being analyzed (formatted) false
--buffers Include buffer usage info (requires --analyze) false
--verbose Verbose plan output false
--no-color Disable colored output TTY-aware
--seq-scan-threshold ROWS Min plan_rows to flag a Seq Scan 10_000
--drift-factor X Min actual/plan ratio to flag row-estimate drift 10
--nested-loop-threshold LOOPS Min inner loops to flag Nested Loop blowup 10_000
--per-row-cost-threshold COST Min (total − startup) / rows to flag a scan 1.0
--version / -v Print version —
--help / -h Show help —

The NO_COLOR environment variable also disables color, per no-color.org. --buffers without --analyze emits a stderr warning and is otherwise ignored.

Output formats

  • tree (default) — the colored, annotated tree shown above.
  • json — pretty-printed Postgres JSON plan; paste into explain.dalibo.com for a deeper look.
  • raw — the text-format EXPLAIN exactly as Postgres returns it.

Heuristics (v1)

Inline warnings flag the most common issues. Every threshold is overridable via a CLI flag — see Tuning thresholds below.

Heuristic Default trigger Severity Override flag
Seq Scan on a large table plan_rows > 10_000 Critical (red) --seq-scan-threshold
Row estimate drift actual / plan > 10x (under --analyze) Warning (yellow) --drift-factor
External sort Sort Method includes "external" Critical (red) —
Nested Loop blowup inner-side actual_loops > 10_000 (under --analyze) Warning (yellow) --nested-loop-threshold
Expensive per-row scan (total_cost − startup_cost) / plan_rows > 1.0 Critical when correlated subplans drive it; Warning otherwise --per-row-cost-threshold

The "expensive per-row scan" heuristic catches the most common hidden-cost pattern: a small Seq Scan that looks fine on row counts but whose total cost is multiplied by correlated subplans running once per outer row, like:

└─ Seq Scan on stars (cost=0.00..49318.79  rows=519)
   ⚠  Seq Scan on stars costs 95.03 per row over 519 rows (total 49318.79). Driven by SubPlan 1, SubPlan 2.
      Correlated subplans run once per outer row. Consider rewriting as a JOIN, LATERAL, or window function.

Tuning thresholds

# Lower the Seq Scan trigger for a small dev database
bundle exec benedictus "User.active" --seq-scan-threshold=500

# Be more aggressive about flagging row-estimate drift
bundle exec benedictus "User.active" --analyze --drift-factor=3

# Catch even mildly expensive per-row work
bundle exec benedictus "User.active" --per-row-cost-threshold=0.5

Each flag accepts a number; the units match the trigger column above.

Safety

When --analyze is set, Benedictus applies three layers of defense:

  1. Tokenizing static check. The SQL is normalized — string literals (single-quoted and dollar-quoted), line comments, and nested block comments are stripped before any keyword scan. The first remaining keyword must be SELECT (or WITH with no data-modifying CTE).
  2. Multi-statement rejection. SQL containing ; outside string literals is rejected, so a RawSqlAdapter returning "SELECT 1; DELETE FROM users" is refused.
  3. AR-class rollback. The EXPLAIN ANALYZE runs inside relation.klass.transaction(requires_new: true) { …; raise ActiveRecord::Rollback }. The class is verified to be <= ActiveRecord::Base, so an arbitrary duck-typed transaction(...) cannot silently disable the rollback.

RawSqlAdapter (the fallback wrapper for any object that just responds to .to_sql) is rejected outright under --analyze — pass an actual ActiveRecord::Relation if you want to analyze.

Caveats

EXPLAIN ANALYZE physically executes the query plan in order to measure runtime. The transaction wrapping reverts row-level changes, but the following kinds of side effects escape ROLLBACK and will persist:

  • Sequence advancement: setval(...), nextval(...)
  • Advisory locks: pg_advisory_lock(...), including session-level locks
  • Notifications: pg_notify(...), LISTEN/NOTIFY
  • Cross-database writes via dblink(...), postgres_fdw, foreign data wrappers
  • Large-object operations: lo_create, lo_unlink, lo_put
  • COPY ... TO PROGRAM (executes shell commands on the server)
  • SECURITY DEFINER UDFs whose body performs the above

If your scope or query object passes a value through one of these functions, --analyze will run it for real and there is no rolling it back. Use --analyze only on read-only queries that don't call volatile functions.

Security note

Benedictus uses eval to evaluate the expression you pass in. The expression is your own Ruby code, executed in your own development environment. Do not pass untrusted input.

Development

git clone https://github.com/tonyaraujop/benedictus
cd benedictus
bundle install
bundle exec rspec spec/benedictus              # unit tests, no DB required
bundle exec rspec spec/integration              # full integration, needs PG

Integration tests need a reachable PostgreSQL — see spec/support/dummy_app/README.md. A one-line docker run is documented there.

Etymology

Latin benedictus = bene (well) + dictus (said) = "well-said".

License

MIT. See LICENSE.