How to Choose and Test an Index for a Rails Query

This guide shows you how to pick the right database index for a slow Rails query, then verify it actually helps. You’ll learn to read the query plan, choose a composite index, and measure the improvement without guessing.

What You’ll Need

• A Rails app (Rails 5 or newer recommended) with PostgreSQL, MySQL, or SQLite (PostgreSQL used in examples).
• Access to the Rails console (rails console) and the database console (psql for Postgres, mysql for MySQL).
• A query that is noticeably slow in development or production logs.
• Basic familiarity with ActiveRecord and SQL.

Step-by-Step

1. Identify the slow query. Look in the Rails log (log/development.log or log/production.log) for lines that say SELECT with a high duration, or use the ActiveRecord::Base.connection.execute to run a query and time it. For example, if you have a Post model and you often call Post.where(author_id: 1).order(created_at: :desc), that’s your candidate.
2. Get the query plan before indexing. In your database console, run EXPLAIN ANALYZE with the exact query. For Postgres:

   EXPLAIN ANALYZE SELECT * FROM posts WHERE author_id = 1 ORDER BY created_at DESC;

   Note the Seq Scan (sequential scan) and the Execution Time. This is your baseline.

3. Decide what to index. Look at the WHERE and ORDER BY clauses. For equality conditions (author_id = ?), an index on that column helps. For sorting, the index should include the sort column after the equality column. So a composite index on (author_id, created_at) is ideal because it lets the database find rows by author and return them already sorted.
4. Add the index via a migration. In Rails, generate a migration:

   rails generate migration AddIndexToPostsAuthorCreatedAt

   Edit the migration to add the composite index:

   add_index :posts, [:author_id, :created_at]

   Run rails db:migrate.

5. Re-run the query plan. In the database console, run the same EXPLAIN ANALYZE again. You should see Index Scan or Index Only Scan instead of Seq Scan. Note the new execution time.
6. Measure the real-world improvement. In Rails console, time the query before and after (if you can revert, or compare with a similar query). Use Benchmark.measure:

   Benchmark.measure { Post.where(author_id: 1).order(created_at: :desc).to_a }

   Run it a few times to get a stable average. Compare to the baseline.

7. Check for over-indexing. Each index slows down writes (INSERT, UPDATE, DELETE) and takes disk space. If the query is already fast enough (under ~10ms), consider not adding an index. Also, avoid duplicate indexes—e.g., if you already have an index on author_id alone, the composite index might make it redundant, but it’s not always harmful.

Common Mistakes

• Indexing the wrong column. If you index only created_at but the query filters by author_id first, the database may still do a sequential scan.
• Forgetting the sort order. For ORDER BY created_at DESC, a composite index on (author_id, created_at) works, but if you had ORDER BY created_at ASC in some queries and DESC in others, the index can be scanned backward, so it’s fine.
• Using a function in the WHERE clause. For example, WHERE lower(email) = 'x' won’t use a regular index on email; you need an expression index.
• Not analyzing the table. After adding an index, run ANALYZE (Postgres) or OPTIMIZE TABLE (MySQL) to update statistics so the planner uses the index.

Tips

• Use a covering index if the query only selects a few columns. For example, if you only need id and title, an index on (author_id, created_at, title) could allow an index-only scan.
• Test with realistic data. A query that is fast on 100 rows may be slow on 1 million. Use production-like data volumes.
• Use EXPLAIN (without ANALYZE) for a quick check—it doesn’t execute the query, so it’s safe on production.
• Consider partial indexes for queries that always have a condition like WHERE deleted_at IS NULL.

Troubleshooting

Why is the index not being used? Check that the query’s WHERE clause matches the index’s leftmost column. If you query WHERE created_at > ? but the index is on (author_id, created_at), the database can’t use it because author_id is not constrained. Also, ensure the column types match—e.g., if author_id is a string and you pass an integer, the index may be ignored.

The query is still slow after adding the index. Run EXPLAIN ANALYZE again and look for Bitmap Heap Scan or Sort steps. If you see a Sort, the index isn’t covering the ORDER BY. Try adding the sort column to the index. Also, check if the table has many updates that cause index bloat—run VACUUM (Postgres) or OPTIMIZE TABLE (MySQL).

Migration fails because index name is too long. Rails auto-generates a name like index_posts_on_author_id_and_created_at which is fine, but if you have a long table name, you can specify a shorter name with name: option.

I added an index but the query plan still shows Seq Scan on a small table. For very small tables (e.g., a few hundred rows), the database may prefer a sequential scan because it’s faster than using an index. That’s normal—don’t force it.

Frequently Asked Questions

Q: How do I know which queries need an index? A: Look for queries that appear frequently in logs with high duration. Use EXPLAIN to see if they do sequential scans on large tables.

Q: Can I have too many indexes? A: Yes, each index adds overhead on writes and uses disk space. Aim for indexes that serve multiple queries, and remove unused ones (check with pg_stat_user_indexes in Postgres).

Q: What’s the difference between a single-column and a composite index? A: A composite index on (a, b) can speed up queries that filter by a alone, by a and b, or by a and sort by b. A single-column index on a only helps queries filtering by a.

Q: Should I index boolean columns? A: Usually not, because the selectivity is low—the database may still scan most rows. Only index if the boolean is true for a tiny fraction of rows and you filter on it often.

Q: How do I test an index without affecting production? A: Create the index in a staging environment with production-like data, run the query, and compare plans. Or use EXPLAIN on production (without ANALYZE) to see if the index would be used—but be careful not to run heavy queries.

Q: My query uses a JOIN, how do I index that? A: Index the foreign key columns used in the JOIN condition. For example, if you join posts to authors on author_id, ensure author_id is indexed in both tables.

Related

• OLED Laptops in India: Prices & Configs Compared
• Zimbra Backup Mailbox: Full Guide
• Brexit Divide in UK Tech: How It Split the Industry