Rails makes it easy to write a query and hard to see what it costs. add_index on every foreign key is the standard advice and it’s a decent floor, but it doesn’t help with the query that filters on three columns, sorts by a fourth, and takes eight seconds on the table that grew past two million rows last quarter. Indexing well requires reading the plan the database produces — which is less intimidating than it looks once you know which four numbers matter.

The Model: An Index Is a Sorted Copy

A B-tree index is a sorted structure mapping column values to row locations. The database can binary-search it instead of scanning the table. That single fact explains every rule that follows:

• Sorted order is why an index helps ORDER BY as well as WHERE
• Sorted by the leading column first is why column order in a composite index matters
• A separate structure is why every index slows down writes — each INSERT updates the table and every index on it

Which means indexes are not free and “add one for every query” is as wrong as adding none.

Reading EXPLAIN Without Fear

Rails gives you the plan directly from a relation.

Example:

Order.where(status: "pending", account_id: 42).order(created_at: :desc).limit(20).explain

For PostgreSQL, get the real numbers rather than estimates:

Order.connection.execute(<<~SQL).values
  EXPLAIN (ANALYZE, BUFFERS)
  SELECT * FROM orders
  WHERE status = 'pending' AND account_id = 42
  ORDER BY created_at DESC LIMIT 20
SQL

Output:

Limit  (cost=0.43..8.91 rows=20 width=124) (actual time=0.031..0.094 rows=20 loops=1)
  ->  Index Scan Backward using idx_orders_account_status_created on orders
        (cost=0.43..1204.22 rows=2841 width=124) (actual time=0.029..0.088 rows=20 loops=1)
        Index Cond: ((account_id = 42) AND (status = 'pending'::text))
  Buffers: shared hit=6
Planning Time: 0.184 ms
Execution Time: 0.121 ms

Four things to look at, in order:

1. The scan type. Seq Scan on a large table is the alarm. Index Scan, Index Only Scan, or Bitmap Index Scan mean an index is being used.
2. rows estimated vs rows actual. A 100× gap means the planner’s statistics are stale and it’s choosing badly. ANALYZE orders; fixes it.
3. Index Cond vs Filter. Index Cond means the index narrowed the search. Filter means rows were read and then discarded — the index didn’t cover that predicate.
4. Buffers: shared hit / read. hit is cache, read is disk. A query with high read will behave very differently on a cold cache than in your benchmark.

The cost numbers are arbitrary units for comparing plans, not milliseconds. Ignore them until you’re comparing two plans of the same query.

Composite Indexes and the Leftmost Rule

A composite index on (a, b, c) is sorted by a, then b within equal a, then c. The database can use it for a prefix of those columns, starting from the left.

Example:

add_index :orders, [:account_id, :status, :created_at]

That index serves:

Order.where(account_id: 42)                                   # uses (a)
Order.where(account_id: 42, status: "pending")                # uses (a, b)
Order.where(account_id: 42, status: "pending").order(:created_at)  # uses all three

It does not serve:

Order.where(status: "pending")               # skips the leading column
Order.order(:created_at)                     # skips two

This is why three single-column indexes are not equivalent to one composite index, and why one well-ordered composite index often replaces three separate ones.

Ordering the columns

The heuristic that works: equality columns first, then the range or sort column last.

# Query: WHERE account_id = ? AND created_at > ? ORDER BY created_at
add_index :orders, [:account_id, :created_at]   # correct
# not [:created_at, :account_id]

Once the index hits a range predicate, columns to its right can no longer be used for narrowing — only for ordering. So a range column in the middle wastes everything after it.

Among equality columns, higher selectivity (more distinct values) first is a reasonable tiebreak, though for equality predicates the difference is usually small compared with getting the range column into last position.

Partial Indexes

If you only ever query one slice of the table, index only that slice.

Example:

add_index :orders, [:account_id, :created_at],
          where: "status = 'pending'",
          name: "idx_orders_pending_by_account"

On a table where 2% of rows are pending, that index is roughly a fiftieth the size of the full one. Smaller index means more of it stays in memory, faster scans, and cheaper writes for the 98% of rows that don’t match the predicate.

Two frequent, high-value cases:

# Soft deletes — nearly every query excludes deleted rows
add_index :users, :email, unique: true, where: "discarded_at IS NULL"

# Sparse columns — most rows are NULL
add_index :accounts, :stripe_subscription_id, where: "stripe_subscription_id IS NOT NULL"

That first one is also the correct way to do a uniqueness constraint alongside soft deletes — a plain unique index blocks a user from re-registering with the email of a deleted account.

The catch: PostgreSQL only uses a partial index when it can prove the query’s WHERE clause implies the index predicate. where("status = 'pending'") matches; where(status: params[:status]) with a bind parameter generally does not, because the planner can’t know the value at plan time.

Expression and Covering Indexes

Example:

# Case-insensitive lookup
add_index :users, "LOWER(email)", name: "idx_users_lower_email", unique: true

User.where("LOWER(email) = ?", email.downcase)   # uses it
User.where(email: email)                          # does not

The query has to match the indexed expression exactly, which is a maintenance hazard — one developer writing where(email:) bypasses the index silently. citext is often the better answer.

Example:

# Covering index — include extra columns so the heap isn't touched
add_index :orders, [:account_id, :status], include: [:total_cents, :created_at]

An Index Only Scan reads everything from the index without visiting the table. include: adds payload columns that can’t be searched but can be returned. Worth it for a hot read path with a narrow projection; wasteful otherwise, since it inflates index size.

Adding Indexes Safely on a Live Table

CREATE INDEX takes a lock that blocks writes for the duration. On a large table that’s an outage.

Example:

class AddIndexToOrders < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def change
    add_index :orders, [:account_id, :status, :created_at],
              algorithm: :concurrently,
              name: "idx_orders_account_status_created"
  end
end

disable_ddl_transaction! is mandatory — CONCURRENTLY cannot run inside a transaction. Concurrent builds are slower and can fail, leaving an invalid index behind; check for those after deploying:

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

Drop and rebuild any that show up.

Pro-Tip: Before adding an index, check whether you already have one that’s redundant. An index on (account_id) is completely covered by an existing index on (account_id, status) — the leftmost rule means the composite serves every query the single-column one does. Redundant indexes cost write throughput and memory for no read benefit, and they accumulate quietly because nobody ever removes one. This query lists candidates, and on a mature Rails app it usually finds several: SELECT indexrelid::regclass AS idx, pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;. Zero scans since the last stats reset, on a table that gets plenty of traffic, means the index is dead weight — verify against your replica’s stats too before dropping.

Conclusion

Indexing is a small set of rules applied to evidence you can read directly. Composite indexes serve leftmost prefixes, so order equality columns first and the range or sort column last. Partial indexes buy large savings on tables with a hot slice. Build concurrently on anything live. And measure with EXPLAIN (ANALYZE, BUFFERS) rather than guessing — the plan will tell you whether the index was used, whether the planner’s row estimates are sane, and whether the query is reading from cache or disk. The habit worth building is checking the plan before adding the index, not after the page gets slow.

FAQs

Q1: Does Rails add an index on foreign keys automatically?
references in a migration adds one by default; add_column :orders, :account_id, :bigint does not. belongs_to in the model adds no index at all — that’s purely an application-level association.

Q2: How many indexes is too many on one table?
There’s no fixed number, but every index adds write cost. If a table has more indexes than columns you actually filter on, audit for redundancy and unused entries. Write-heavy tables warrant a stricter budget than read-heavy ones.

Q3: Why is the database ignoring my index?
Common causes: the query doesn’t match the leftmost prefix, a function is applied to the column, the planner estimates the index scan would return most of the table anyway, or statistics are stale. EXPLAIN plus ANALYZE <table> resolves most of these.

Q4: Do indexes help ORDER BY without a WHERE?
Yes — the index is already sorted, so the database can walk it in order and skip the sort step entirely. That’s why a plain ORDER BY created_at DESC LIMIT 20 on an indexed column is fast even on a huge table.

Q5: Should I index a boolean column?
Rarely on its own; a boolean has two values so it’s poorly selective. As the trailing column of a composite index, or as the predicate of a partial index, it’s often exactly right.