ActiveRecord Performance Tips: Debugging Slow

Start with the Plan (EXPLAIN Mindset)

Before you optimize anything, understand what the database is actually doing. Don't guess.

Quick EXPLAIN Check in Rails Console

# PostgreSQL
relation = User.joins(:posts).where(published: true)
puts relation.explain

# MySQL
puts relation.explain(:analyze)

# Look for:
# - "Seq Scan" (table scan - usually bad for large tables)
# - "Index Scan" or "Index Only Scan" (good)
# - "Nested Loop" vs "Hash Join" (depends on data size)
# - Row estimates vs actual rows (if way off, indexes may help)

What to Look For

🚨 Red Flags

• Seq Scan on tables > 10k rows
• Row estimate off by 100×+
• Nested Loop joining 1M rows
• Multiple table scans in one query
• Sort operation on huge result set

✓ Good Signs

• Index Scan or Index Only Scan
• Row estimate close to actual
• Hash Join for large tables
• Bitmap Index Scan (PostgreSQL)
• Covering index being used

⚠️ Don't Optimize Without Measuring

Run EXPLAIN ANALYZE on production-sized data. A query that's fast on 100 rows in development can timeout on 100,000 rows in production due to missing indexes or wrong query plan.

Index Rules That Prevent Outages

Missing or wrong indexes are the #1 cause of production slowdowns. Here's how to get them right.

Rule 1: Index Foreign Keys

Why: Every belongs_to association needs an index on the foreign key column, or JOINs will scan the entire table.

# In migration
add_index :posts, :user_id
add_index :comments, :post_id
add_index :orders, :user_id

Verify: Run EXPLAIN on queries with JOINs. If you see "Seq Scan" on the joined table, you're missing an index.

Rule 2: Composite Indexes for Common WHERE Clauses

Why: Single-column indexes don't help queries that filter on multiple columns. Order matters!

# Query: User.where(company_id: 123, active: true)
# Good: Index that covers both columns
add_index :users, [:company_id, :active]

# Query: Post.where(user_id: 456, published: true).order(created_at: :desc)
# Good: Composite index with order column last
add_index :posts, [:user_id, :published, :created_at]

Rule of thumb: Equality filters first, range/order columns last.

Rule 3: Partial Indexes for Skewed Data

Why: If you only query active/published records, index only those rows to save space and improve speed.

# Query: Post.where(published: true).order(created_at: :desc)
# Good: Partial index (PostgreSQL)
add_index :posts, :created_at, where: "published = true"

# Query: User.where(active: true, company_id: 123)
add_index :users, [:company_id, :active], where: "active = true"

When to use: When filtered column has < 10% true values (e.g., is_admin, deleted_at IS NULL).

Rule 4: Index Order Matters for ORDER BY

Why: Indexes have a direction (ASC/DESC). If your query sorts DESC, create the index DESC.

# Query: Post.order(created_at: :desc).limit(10)
# Good: Descending index
add_index :posts, [:created_at], order: { created_at: :desc }

# Query: Post.where(user_id: 123).order(created_at: :desc)
add_index :posts, [:user_id, :created_at], order: { created_at: :desc }

PostgreSQL note: Supports index direction. MySQL may not respect it.

Rails Query Traps That Look Correct But Perform Badly

Trap #1: The N+1 Classic

Problem: Loading a collection, then accessing an association on each item = 1 query + N queries.

❌ Bad (N+1):

users = User.limit(100)
users.each do |user|
  puts user.posts.count  # N queries!
end

✓ Good:

users = User.includes(:posts).limit(100)
users.each do |user|
  puts user.posts.size  # No extra queries
end

Detect in development: Add gem 'bullet' to get warnings when N+1 queries occur.

Trap #2: DISTINCT Masking Cartesian Explosions

Problem: Multiple JOINs create duplicate rows. You add .distinct, which works but fetches 100× more rows than needed.

⚠️ Problematic:

User.joins(:posts).joins(:comments)
    .distinct
# Fetches 10,000 rows, deduplicates to 100

✓ Better:

User.where(id: Post.select(:user_id))
    .where(id: Comment.select(:user_id))
# Fetches 100 rows via subqueries

Check: Run .count vs .distinct.count. If they differ by 10×+, refactor to subqueries.

Trap #3: OR Conditions Kill Index Usage

Problem: OR often prevents the database from using indexes efficiently.

❌ Slow:

Post.where("user_id = ? OR published = ?", 123, true)
# May scan entire table

✓ Faster:

Post.where(user_id: 123)
    .or(Post.where(published: true))
# Rails 5+ can use indexes on each side

Or even better: UNION if the conditions are completely separate.

Trap #4: Big IN Clauses (> 1000 Items)

Problem: WHERE id IN (1, 2, 3, ..., 5000) can timeout or cause plan cache bloat.

❌ Risky:

huge_id_list = [1, 2, ..., 10000]
User.where(id: huge_id_list)
# Can timeout on MySQL/Postgres

✓ Safe:

huge_id_list.each_slice(1000) do |batch|
  User.where(id: batch).find_each { ... }
end
# Process in batches

Limit: Keep IN clauses under 1000 items. Use temp tables or batch processing for larger sets.

Trap #5: .includes When You Mean .joins

Problem: .includes loads associations (good for N+1), but if you don't use them, it's wasted work.

❌ Wasteful:

User.includes(:posts)
    .where(posts: { published: true })
# Loads all posts for matching users

✓ Lean:

User.joins(:posts)
    .where(posts: { published: true })
# Only filters, doesn't load posts

Rule: Use .joins if you only need to filter. Use .includes if you'll access the association later.

Verification Workflow: Measure, Compare, Protect

Don't ship performance regressions. Lock in good performance with tests and monitoring.

3-Step Verification Process

1. Measure Current Performance

require 'benchmark'

puts Benchmark.measure {
  User.joins(:posts).where(posts: { published: true }).to_a
}

# Also check query count
ActiveRecord::Base.logger = Logger.new(STDOUT)
# Run your code, count SELECT statements

2. Compare SQL Before and After

# Before optimization
old_query = OldScope.to_sql

# After optimization
new_query = NewScope.to_sql

# Run EXPLAIN on both
puts ActiveRecord::Base.connection.execute("EXPLAIN ANALYZE #{old_query}")
puts ActiveRecord::Base.connection.execute("EXPLAIN ANALYZE #{new_query}")

# Compare execution time and index usage

3. Add Performance Regression Spec

RSpec.describe "User query performance" do
  it "uses index on user_id when filtering posts" do
    relation = User.joins(:posts).where(posts: { published: true })

    explain = relation.explain
    expect(explain).to include("Index Scan")
    expect(explain).not_to include("Seq Scan on posts")
  end

  it "doesn't cause N+1 when loading user posts" do
    users = User.includes(:posts).limit(10)

    expect {
      users.each { |u| u.posts.map(&:title) }
    }.to make_database_queries(count: 2)  # 1 for users, 1 for posts
  end
end

⚠️ Production Monitoring

Use tools like Scout APM, New Relic, or Skylight to catch slow queries in production. Set alerts for queries > 100ms or N+1 patterns.

ActiveRecord Performance Tips (Root Causes, Not Folklore)