# How Ransack Searches Interact With Composite Indexes: Optimization Guide

> Optimize Ransack searches by understanding how they interact with composite indexes. Learn to leverage indexes for faster database queries with this guide.

- Repository: [ActiveRecord Hackery/ransack](https://github.com/activerecord-hackery/ransack)
- Tags: performance
- Published: 2026-02-23

---

**Ransack generates SQL WHERE clauses that combine individual column predicates with AND, allowing the database optimizer to automatically leverage composite indexes when your search keys use the `_and_` combinator and match the index column order.**

The Ransack gem simplifies complex searching in Ruby on Rails applications, but understanding how it interacts with database-level optimizations requires examining its internal query construction. When you search across multiple attributes using the `_and_` combinator, Ransack produces Arel query objects that translate directly to composite-index-friendly SQL. This article examines the source code of `activerecord-hackery/ransack` to explain exactly when and how your multi-column indexes are utilized.

## How Ransack Constructs Multi-Column Queries

Ransack builds **Arel** query objects from the search parameters you provide. When a search key contains multiple attributes (e.g., `first_name_and_last_name_eq`), the gem processes this through several distinct phases in [`lib/ransack/nodes/condition.rb`](https://github.com/activerecord-hackery/ransack/blob/main/lib/ransack/nodes/condition.rb).

First, the `Condition.extract` method splits the key into separate attributes by scanning for `_and_` or `_or` delimiters, building an array of attribute names. Each attribute is then wrapped in an `Attribute` object that references the underlying database column.

Next, the `arel_predicate` method creates a separate condition node for each attribute and reduces the list of predicates using either `:and` or `:or`, depending on the combinator detected in the original key. Finally, Ransack hands the resulting Arel node to ActiveRecord, which renders the final SQL.

Because this process generates **individual column predicates combined with `AND`** (or `OR`), the database query planner can use a composite index only when specific structural requirements are met.

## Requirements for Composite Index Utilization

For the database optimizer to employ a composite (multi-column) index with Ransack-generated queries, four critical conditions must align:

- **The predicate must use `AND`, not `OR`**. Composite indexes are structured to support conjunctions that match the index's left-most columns. When Ransack encounters `first_name_or_last_name_eq`, it generates a SQL `OR` condition that prevents composite index usage.

- **Column order must match the index definition**. PostgreSQL, MySQL, and other relational databases scan composite indexes from left to right. If your index is defined on `(last_name, first_name)` but your Ransack query filters by `first_name_and_last_name_eq`, the optimizer cannot use the index efficiently.

- **No functions or type casts on indexed columns**. Ransack's `attr_value_for_attribute` returns the raw column reference for PostgreSQL adapters. However, for other database adapters or when using case-insensitive predicates (like `_eq_ci`), the method may wrap the column with `.lower`, which prevents index usage unless you have created a corresponding functional index.

- **Simple comparison operators only**. Ransack maps predicates like `eq`, `lt`, `gt`, and `in` directly to underlying Arel methods. These translate to SQL without wrapping values in functions, keeping the query sargable and index-friendly.

## Practical Implementation Examples

### Defining the Composite Index

Before optimizing Ransack queries, ensure your migration defines the index with columns in the order you will query them:

```ruby

# db/migrate/20240223120000_add_composite_index_to_users.rb

class AddCompositeIndexToUsers < ActiveRecord::Migration[7.0]
  def change
    add_index :users, [:first_name, :last_name], name: "index_users_on_first_and_last_name"
  end
end

```

### Optimized AND Queries

Use the `_and_` combinator with equality predicates to generate index-friendly SQL:

```ruby

# In controller or service object

@q = User.ransack(first_name_and_last_name_eq: ["John", "Doe"])
@users = @q.result

```

This generates SQL similar to:

```sql
SELECT "users".* FROM "users"
WHERE ("users"."first_name" = $1 AND "users"."last_name" = $2)

```

Because the `WHERE` clause combines both columns with `AND` in the same order as the composite index, PostgreSQL or MySQL will use `index_users_on_first_and_last_name` to satisfy the query efficiently.

### Suboptimal OR Queries

Contrast this with the OR combinator, which prevents composite index usage:

```ruby
@q = User.ransack(first_name_or_last_name_eq: ["Eve", "Miller"])
@users = @q.result

```

This produces:

```sql
WHERE ("users"."first_name" = $1 OR "users"."last_name" = $2)

```

A composite index cannot optimize this disjunction; the planner will fall back to separate index scans or a sequential scan.

## Edge Cases and Performance Pitfalls

### Case-Insensitive Searches

When using case-insensitive predicates like `first_name_eq_ci`, `attr_value_for_attribute` wraps the column with `.lower` in the generated SQL. This prevents the use of a standard composite index. To maintain performance, create functional indexes that cover the transformed values:

```sql
CREATE INDEX index_users_on_lower_names 
ON users (LOWER(first_name), LOWER(last_name));

```

### Array Predicates and Composite Keys

Ransack handles array predicates like `_in` by building `IN (...)` lists. The database can still utilize composite indexes as long as the `WHERE` clause maintains an `AND` relationship between the predicates:

```ruby

# Generates: WHERE first_name IN (...) AND last_name IN (...)

@q = Person.ransack(first_name_and_last_name_in: ["Alice", "Bob"])

```

### Polymorphic Associations

For polymorphic or association-based predicates, Ransack creates correlated sub-queries. While composite indexes on foreign key columns can still be used inside these sub-queries, the outer query may not benefit directly from your multi-column indexes.

## Summary

- **Ransack has no explicit composite index handling**; it relies on generating clean SQL that the database optimizer can route to existing indexes.
- **Use `_and_` combinators** to create conjunctions that match composite index column order.
- **Avoid `_or_` combinators** when targeting composite indexes, as they generate disjunctions that bypass multi-column index structures.
- **Watch for case-insensitive predicates** that wrap columns in SQL functions, preventing standard index usage unless functional indexes are defined.
- **Reference the actual source** in [`lib/ransack/nodes/condition.rb`](https://github.com/activerecord-hackery/ransack/blob/main/lib/ransack/nodes/condition.rb) to understand how `extract` and `arel_predicate` methods construct your queries.

## Frequently Asked Questions

### Can Ransack automatically detect and use my composite indexes?

No, Ransack does not inspect your database schema or indexes. According to the implementation in [`lib/ransack/nodes/condition.rb`](https://github.com/activerecord-hackery/ransack/blob/main/lib/ransack/nodes/condition.rb), it simply constructs Arel nodes that translate to standard SQL. The database optimizer independently decides whether to use a composite index based on whether the generated `WHERE` clause matches the index structure. You must ensure your search parameters use the `_and_` combinator and match your index column order.

### Why isn't my composite index being used even with an `_and_` query?

Check three common issues: First, verify the column order in your query matches the index definition exactly—databases scan composite indexes left-to-right. Second, ensure you are not using case-insensitive predicates (`_ci` suffix), which wrap columns in `LOWER()` functions. Third, confirm you are not mixing the search with `OR` conditions elsewhere in the query, as seen in [`lib/ransack/nodes/grouping.rb`](https://github.com/activerecord-hackery/ransack/blob/main/lib/ransack/nodes/grouping.rb) when combining multiple conditions.

### Does Ransack support case-insensitive searches with composite indexes?

Only if you create **functional indexes** that match the transformation Ransack applies. When using `eq_ci` predicates, Ransack's `attr_value_for_attribute` method calls `.lower` on the column reference. A standard B-tree index on the raw column will not be used. You must create an index on the lowercase values, or stick to case-sensitive predicates (`_eq`) to utilize standard composite indexes.

### How do I verify that Ransack is generating index-friendly SQL?

Use your database's query execution plan tools. In PostgreSQL, prefix your query with `EXPLAIN (ANALYZE, BUFFERS)` before calling `@q.result.to_a`. Look for "Index Scan" or "Bitmap Index Scan" operations referencing your composite index name. If you see "Seq Scan" (sequential scan) on a large table despite having a composite index, check that your Ransack search keys use `_and_` rather than `_or_` and that the column order aligns with your migration-definition in `add_index`.