Benedictus

A query bem-dita.

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

Supports PostgreSQL, MySQL, and SQLite.

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.

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.

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.

• Ruby >= 3.1
• ActiveRecord >= 6.1 (verified against Rails 8.x; 6.1 / 7.x should work but aren't covered by CI yet)
• One of:
  • PostgreSQL (any version supported by your Rails app)
  • MySQL >= 5.7 (JSON EXPLAIN format support)
  • SQLite >= 3.8 (EXPLAIN QUERY PLAN support)

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

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

• 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.

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

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.

# 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.

Benedictus automatically detects your database adapter and adjusts its behavior accordingly.

All features available:

• ✅ JSON format with rich metadata (costs, timing, buffers)
• ✅ EXPLAIN ANALYZE with safe transaction rollback
• ✅ All 5 heuristics
• ✅ --buffers flag (requires --analyze)

MySQL support with limitations:

• ✅ JSON format with cost estimates
• ⚠️ EXPLAIN ANALYZE disabled by default (see Safety section below)
• ✅ 2 heuristics: SeqScanOnLargeTable, RowEstimateDrift
• ❌ No --buffers support
• 🔄 Access type mapping: ALL → Seq Scan, ref/eq_ref → Index Scan

MySQL-specific notes:

• Cost model differs from PostgreSQL (read_cost + eval_cost)
• Nested loops shown as arrays, transformed into tree structure
• Requires MySQL >= 5.7 for JSON EXPLAIN support

SQLite support with minimal features:

• ⚠️ Text-only output (no JSON, no costs)
• ❌ No EXPLAIN ANALYZE support
• ✅ 1 heuristic: SeqScanOnLargeTable (basic detection only)
• 🔄 Text parsing: SCAN TABLE → Seq Scan, SEARCH ... USING INDEX → Index Scan

SQLite-specific notes:

• No cost estimates or timing information
• Tree structure reconstructed from id/parent relationships
• All cost/row values reported as 0

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.

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.

CRITICAL: MySQL's EXPLAIN ANALYZE actually executes the query and cannot be rolled back. All side effects (database writes, external API calls, email sends, etc.) WILL occur.

For this reason, --analyze is disabled for MySQL by default. You must use --force-analyze to enable it:

# This will fail with MySQL
bundle exec benedictus "User.active" --analyze

# This works but prints a prominent warning and waits 3 seconds
bundle exec benedictus "User.active" --analyze --force-analyze

When using --force-analyze, Benedictus prints a prominent warning and waits 3 seconds before executing, giving you a chance to cancel (Ctrl+C).

Version requirement: MySQL >= 8.0.18 is required for ANALYZE support. Earlier versions will be rejected with a clear error message.

SQLite does not support EXPLAIN ANALYZE. The --analyze flag is ignored when using SQLite.

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.

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.

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

MIT. See LICENSE.