How to Use Prefetch Patterns in Odoo 18: Complete Tutorial
By Braincuber Team
Published on April 13, 2026
Caching is a silent hero in any ORM, and in Odoo, that hero is called prefetching. Odoo 18 pushes the idea of prefetching even further with clearer patterns, guard-rails, and measurable speed-ups. This complete tutorial shows you how to leverage prefetch patterns to create efficient and scalable Odoo applications. If you routinely write custom modules or large-scale data-processing scripts, understanding prefetch patterns is the difference between code that merely works and code that scales.
What You'll Learn:
- Understanding prefetching and its importance in Odoo ORM
- How prefetch works in Odoo's Object-Relational Mapping
- Identifying and avoiding N+1 query problems
- Custom prefetch patterns for optimal performance
- Using mapped() and filtered() methods
- Multi-level prefetching techniques
- Custom prefetch context configuration
What is Prefetching?
Prefetching is a technique where related data is loaded in advance, anticipating future needs. In Odoo's ORM context, prefetching helps minimize the number of database queries by fetching related records in bulk rather than one at a time. This is especially important when working with large datasets or complex relationships between models. This beginner guide covers all the essential techniques you need to know.
Reduces Database Queries
Fetches related records in bulk instead of one at a time, dramatically reducing database load.
Improves Performance
Optimizes data access patterns making your modules significantly faster.
Scales with Data
Essential for large datasets and complex operations that need to scale.
Memory Efficient
Loads data strategically balancing performance with memory usage.
Understanding the N+1 Query Problem
The N+1 query problem is a common performance issue in ORMs where accessing related records triggers separate queries for each record. This beginner guide shows you how to identify and fix this issue in Odoo 18.
N+1 Query Problem
When you iterate over records and access related fields, Odoo may execute one query to fetch the records plus N queries to fetch each related record. For 100 orders, this means 101 queries instead of 2.
Basic Prefetch Example
Without explicit prefetching, accessing related fields can trigger the N+1 problem. This step by step guide shows you how to identify and fix it.
# Basic record access without explicit prefetch
orders = self.env['sale.order'].search([('state', '=', 'confirmed')])
for order in orders:
print(order.partner_id.name) # Potential N+1 query problem
Custom Prefetch Patterns
This complete tutorial section shows you how to implement explicit prefetch patterns to optimize your Odoo 18 code and avoid the N+1 problem.
Using with_prefetch()
Use with_prefetch() to preload specific records by their IDs before searching. This gives you fine-grained control over which records are prefetched.
Using mapped() Method
The mapped() method automatically handles prefetching when you access related fields. This is the simplest way to prefetch related records.
Using filtered() Method
The filtered() method efficiently filters records while maintaining prefetch context, allowing for performant chain operations.
# Explicit prefetch example
orders = self.env['sale.order'].with_prefetch(
prefetch_ids=confirmed_order_ids
).search([('state', '=', 'confirmed')])
# Prefetch related models
orders = self.env['sale.order'].search([('state', '=', 'confirmed')])
orders.mapped('partner_id') # Prefetches all partners
Best Practices for Prefetching
Follow these best practices to ensure optimal performance in your Odoo 18 modules. This beginner guide covers the essential techniques for effective prefetching.
| Practice | Description |
|---|---|
| Analyze Query Patterns | Use Odoo's logging or database monitoring tools to identify N+1 query problems |
| Prefetch in Bulk Operations | Always implement prefetching when processing large recordsets |
| Leverage mapped() and filtered() | These methods automatically handle prefetching efficiently |
| Consider Field Attributes | Use related and compute attributes properly to optimize data access |
| Test with Realistic Data | Prefetch optimizations should be validated with production-like data volumes |
Advanced Techniques
Master these advanced prefetch techniques to handle complex relationships and maximize performance in your Odoo applications.
Multi-Level Prefetching
Multi-level prefetching allows you to load related records across multiple relationship levels efficiently. This step by step guide shows you how to implement it.
# Multi-level prefetching
orders = self.env['sale.order'].search([('state', '=', 'confirmed')])
partners = orders.mapped('partner_id')
countries = partners.mapped('country_id')
Custom Prefetch Context
You can specify which fields to preload using custom prefetch context. This gives you precise control over what data is loaded.
# Custom prefetch context
self.env.context = dict(
self.env.context,
prefetch_fields={
'sale.order': ['partner_id', 'date_order'],
'res.partner': ['name', 'email']
}
)
Memory Considerations
Prefetch optimization should be balanced with memory usage considerations, as excessive prefetching can lead to higher memory consumption. Always profile your changes to ensure they provide the intended performance benefits.
Frequently Asked Questions
What is prefetching in Odoo 18?
Prefetching is a technique that loads related data in advance to minimize database queries. It helps avoid the N+1 query problem by fetching related records in bulk.
How do I use mapped() for prefetching?
Use orders.mapped('partner_id') to prefetch all related partner records in a single query instead of accessing them in a loop.
What is the N+1 query problem?
The N+1 problem occurs when accessing related records triggers separate queries for each record. For 100 orders, this means 101 queries instead of 2.
How does with_prefetch() work?
with_prefetch() allows you to preload specific records by their IDs before searching, giving fine-grained control over which records are prefetched.
Does prefetching increase memory usage?
Yes, excessive prefetching can lead to higher memory consumption. Balance prefetch optimizations with memory usage and always test with production-like data volumes.
Need Help with Odoo 18 Performance?
Our experts can help you optimize your Odoo modules with prefetch patterns and other performance techniques. Get free consultation today.