Prefetch Patterns in Odoo 18: ORM Performance Guide
By Braincuber Team
Published on January 17, 2026
Database queries are the silent bottleneck in most ERP systems. When you loop through 1,000 sale orders and access each partner's name, a naive implementation fires 1,001 queries—one for the orders, then one per partner. This is the infamous N+1 problem, and it's exactly what Odoo's prefetching mechanism solves.
This tutorial explains how prefetching works in Odoo 18, how to leverage it in your custom modules, and the patterns that separate efficient code from code that grinds to a halt on production data.
What is Prefetching? Prefetching loads related data in advance, anticipating future needs. Instead of fetching one partner at a time, Odoo's ORM fetches all partners for a recordset in a single query.
The N+1 Query Problem
Consider this common pattern—it looks innocent but performs terribly:
# Fetches 1000 invoices
invoices = self.env['account.move'].search([('move_type', '=', 'out_invoice')])
# For each invoice, this triggers a separate query for partner_id
for invoice in invoices:
print(invoice.partner_id.name) # 1000 additional queries!
# Total: 1001 queries (1 for invoices + 1000 for partners)
Performance Impact: On 10,000 records, this can take 30+ seconds. With prefetching, the same operation completes in under 1 second.
How Odoo's Prefetching Works
Odoo's ORM automatically prefetches when you access fields. When the first partner_id is accessed, the ORM loads all partner IDs for the entire recordset in one query:
Automatic Prefetching
When iterating a recordset, accessing a field on one record triggers loading that field for all records in the set.
Lazy Loading
Fields are only loaded when first accessed. Unused fields don't consume memory or query time.
Recordset Caching
Once fetched, data is cached in the recordset. Subsequent accesses return cached values instantly.
Explicit Prefetching with mapped()
The mapped() method is your primary tool for explicit prefetching. It accesses a field across all records, loading them in bulk:
# Fetch all invoices
invoices = self.env['account.move'].search([('move_type', '=', 'out_invoice')])
# Prefetch all partners in ONE query
partners = invoices.mapped('partner_id')
# Now loop—partner data is already cached
for invoice in invoices:
print(invoice.partner_id.name) # No additional queries!
# Total: 2 queries (1 for invoices, 1 for all partners)
Result: 2 queries instead of 1001. The loop runs 500x faster on large datasets.
Multi-Level Prefetching
When you need data several relationships deep, chain your mapped() calls:
# Fetch purchase orders
orders = self.env['purchase.order'].search([('state', '=', 'purchase')])
# Level 1: Prefetch vendors
vendors = orders.mapped('partner_id')
# Level 2: Prefetch vendor countries
countries = vendors.mapped('country_id')
# Level 3: Prefetch order lines
lines = orders.mapped('order_line')
# Level 4: Prefetch products from lines
products = lines.mapped('product_id')
# Now access any of this data without additional queries
for order in orders:
vendor_country = order.partner_id.country_id.name
for line in order.order_line:
product_name = line.product_id.name
# All data is cached—zero extra queries
Using with_prefetch()
For fine-grained control, use with_prefetch() to specify exactly which record IDs to include in prefetch operations:
# Get specific IDs to prefetch priority_order_ids = [101, 102, 103, 104, 105] # Create a recordset with a specific prefetch pool orders = self.env['sale.order'].with_prefetch(priority_order_ids).browse(priority_order_ids) # Alternatively, share prefetch context between recordsets prefetch_ids = self.env['sale.order'].search([]).ids order_subset = self.env['sale.order'].with_prefetch(prefetch_ids).browse([101, 102]) # Accessing fields on order_subset will prefetch for ALL records in prefetch_ids
Prefetching with filtered()
The filtered() method maintains prefetch context, making it safe for performance:
# Fetch all products products = self.env['product.product'].search([]) # Filter to only saleable products—prefetch context is maintained saleable = products.filtered(lambda p: p.sale_ok) # Filter to only products with stock in_stock = products.filtered(lambda p: p.qty_available > 0) # Both filtered recordsets still benefit from the original prefetch
Best Practices
Prefetching Guidelines:
- Use mapped() proactively: Before looping, call
mapped()on fields you'll access. - Prefetch in bulk operations: Scheduled actions, data imports, and reports benefit most.
- Avoid breaking recordsets: Methods like
browse([id])on single IDs create new prefetch groups. - Watch memory usage: Prefetching trades memory for speed—don't prefetch fields you won't use.
- Profile with realistic data: Test with production-sized datasets to see real improvements.
Anti-Pattern: Don't use for record in self.browse(id) for id in ids. This creates separate prefetch contexts for each ID, defeating the purpose. Use self.browse(ids) to get a single recordset.
Conclusion
Prefetching is Odoo's answer to the N+1 query problem. By understanding how the ORM batches queries and using mapped(), filtered(), and with_prefetch() intentionally, you can write custom modules that scale from 100 records to 100,000 without performance degradation. The key is to think in recordsets, not individual records.
Key Takeaway: Use mapped() to prefetch related fields before looping. Use filtered() to maintain prefetch context. Avoid breaking recordsets into individual records. Profile with production data to measure improvements.