Rails Query Optimization: Part 2— Advanced Indexing and Query Refactoring
Blog Author : Anamika Rajput
In Part 1, we explored essential query optimization techniques and tools like Bullet and Prosopite gem. In this part, we delve into advanced indexing strategies and query refactoring methods that can significantly boost performance.
1. Advanced Indexing Techniques
Indexes speed up query execution by allowing the database to find rows faster. However, adding unnecessary indexes can slow down write operations.
1.1 Composite Indexes
Composite indexes are multi-column indexes that enhance performance for queries filtering on multiple columns.
Example — Adding a Composite Index:
This index speeds up queries like:
Best Practice: Place the most selective column first (i.e., the column with the highest number of unique values).
1.2 Partial Indexes
Partial indexes improve performance by indexing only a subset of rows that match a condition.
Example — Indexing Active Users Only:
This index is used only when querying active users:
Benefits: Reduced index size and faster writes.
1.3 Indexing JSONB Columns
For PostgreSQL, indexing JSONB columns can significantly enhance performance when querying JSON data.
Example — JSONB Index:
This index is used when querying JSONB fields:
1.4 Covering Indexes
A covering index includes all the columns required for a query, eliminating the need to access the table rows.
Example — Covering Index:
This index covers the following query:
Benefit: Reduced I/O operations, improving performance.
2. Query Refactoring Techniques
Refactoring queries can lead to significant performance improvements by reducing complexity and making better use of database features.
2.1 Using Subselects Efficiently
Subselects are useful for filtering data before joining tables, reducing the number of rows scanned.
Example — Refactored Subselect:
This approach is more efficient than using or .
2.2 Avoiding SELECT * in Queries
Fetching unnecessary columns increases memory usage and slows down queries.
Example — Optimized Query:
Benefit: Reduced memory usage and faster query execution.
2.3 Using pluck Instead of map
Use to retrieve specific columns directly from the database, avoiding unnecessary object creation.
Example — Efficient Column Selection:
Benefit: Lower memory usage and faster query execution.
2.4 Refactoring Complex Conditions with merge
Using keeps queries DRY and maintainable by reusing query scopes.
Example — Using merge for Complex Queries:
Benefit: Reusable and cleaner query logic.
3. Using Database Views and Materialized Views
Views and materialized views are powerful tools to optimize complex queries and enhance performance.
3.1 Database Views
Views are virtual tables defined by a query. They simplify complex joins and calculations.
Example — Creating a View:
In Rails, use the gem to manage views:
Then, create a view migration:
3.2 Materialized Views
Materialized views store query results physically, improving performance for complex calculations.
Example — Creating a Materialized View:
Refreshing the View:
In Rails, trigger refreshes using callbacks or background jobs.
When to Use:
Use database views for frequently used complex joins.
Use materialized views for expensive aggregations that don’t need real-time updates.
4. Real-World Query Refactoring Examples
Example 1 — Optimizing N+1 Queries
Before:
After:
Example 2 — Refactoring Complex Joins
Before:
After — Using :
Example 3 — Efficient Counting
Before:
After — Using :
5. Performance Monitoring Tools
Tracking performance metrics helps identify bottlenecks and optimize application performance. Here are some recommended tools:
5.1 Rack Mini Profiler
provides real-time performance metrics, including SQL queries, rendering time, and memory usage.
Installation:
Add to your Gemfile:
Run:
Usage:
Displays performance information as an overlay in the browser.
Identifies slow queries, N+1 queries, and memory bloat.
5.2 ScoutAPM
ScoutAPM provides in-depth insights into application performance, including detailed SQL analysis.
Features:
SQL query breakdown with execution times.
Historical performance trends.
Memory leak detection.
Installation:
Add to your Gemfile:
Set up environment variables and run:
5.3 Skylight
Skylight is another powerful APM tool that tracks endpoint performance, database queries, and external API calls.
Features:
Endpoint-specific query analysis.
Query breakdowns with detailed timing.
Historical performance metrics.
Installation:
Run:
6. Identifying Memory Leaks in Queries
Memory leaks can occur when queries load large datasets or Active Record objects unnecessarily.
6.1 Using the Memory Profiler
Memory Profiler helps identify memory bloat caused by inefficient queries.
Installation:
Usage:
Output Analysis
Look for high memory allocation related to Active Record models.
Consider using or to optimize memory usage.
6.2 Using Rack Mini Profiler for Memory Leaks
Rack Mini Profiler also tracks memory usage by displaying detailed memory reports in the browser.
Example — Memory Optimization:
6.3 Optimizing Memory Usage with Batch Processing
For large datasets, use or to avoid loading all records at once.
Example — Using :
Benefits:
Efficient memory usage.
Reduced risk of memory leaks.
Summary
Advanced indexing and query refactoring techniques can significantly enhance Rails application performance. Use composite, partial, JSONB, and covering indexes to optimize queries while minimizing unnecessary indexing. Refactor queries with subselects, , and to reduce memory usage and improve efficiency. Leverage database views and materialized views for complex queries, and monitor performance with tools like Rack Mini Profiler, ScoutAPM, and Skylight. Finally, prevent memory leaks by using batch processing and memory profiling tools to optimize large dataset handling.