Odoo Prefetching and Lazy Loading: How the ORM Optimizes Queries

How Odoo Lazy Loading Works

When you access a field on an Odoo record, the ORM does not immediately query the database for just that field. Instead, it uses a lazy loading mechanism: the first field access on a record triggers a query that fetches ALL fields of that record and all other records in the same prefetch group.

orders = self.env['sale.order'].search([('state', '=', 'sale')], limit=100)

# First access triggers prefetch of ALL 100 orders' fields
name = orders[0].name  # SQL: SELECT id, name, state, partner_id, ... FROM sale_order WHERE id IN (1,2,3,...,100)

# Subsequent accesses use cache — no SQL
state = orders[0].state  # from cache
name2 = orders[1].name   # from cache

This is why Odoo is faster than you might expect when iterating over recordsets — the first access loads everything, and subsequent accesses hit the in-memory cache.

Prefetch Groups

A prefetch group is a set of record IDs that share the same prefetch context. When you search for records, all returned IDs form a prefetch group. When you access any field on any record in the group, Odoo fetches that field for ALL records in the group.

# These 50 records share a prefetch group
partners = self.env['res.partner'].search([], limit=50)

# Accessing .name on one partner loads name for all 50
partners[0].name  # loads all 50 names
partners[25].name  # from cache

However, if you browse individual records separately, they may get separate prefetch groups:

# These have SEPARATE prefetch groups
p1 = self.env['res.partner'].browse(1)
p2 = self.env['res.partner'].browse(2)
p1.name  # loads only record 1
p2.name  # loads only record 2 — separate query!

Combining Prefetch Groups

Use with_prefetch() to force records into the same prefetch group:

# Force shared prefetch
prefetch_ids = [1, 2, 3, 4, 5]
records = self.env['res.partner'].browse(prefetch_ids)
# All 5 share a prefetch group — accessing one loads all

Or combine recordsets:

all_partners = p1 | p2 | p3  # merged recordset, shared prefetch
all_partners[0].name  # loads all three

Many2one Prefetching

Many2one fields get special treatment. When Odoo prefetches a Many2one field, it loads the foreign key IDs but NOT the related record data. The related record data is fetched lazily when you access a field on the related record:

orders = self.env['sale.order'].search([], limit=100)

# This loads partner_id (FK integer) for all 100 orders
orders[0].partner_id  # SQL: fetches all order fields including partner_id FK

# This loads partner name for ALL partners referenced by the 100 orders
orders[0].partner_id.name  # SQL: SELECT ... FROM res_partner WHERE id IN (partner_ids)

The second query fetches data for all unique partners referenced by the 100 orders in a single query — not one query per order.

One2many and Many2many Prefetching

One2many and Many2many fields are prefetched differently. Accessing them loads the related IDs for all records in the prefetch group:

orders = self.env['sale.order'].search([], limit=50)

# Loads order_line_ids for ALL 50 orders in one query
orders[0].order_line_ids  # SQL: SELECT ... FROM sale_order_line WHERE order_id IN (...)

# Accessing a field on the lines triggers another prefetch
orders[0].order_line_ids[0].price_unit  # loads all line fields for all related lines

When Prefetching Hurts

Prefetching is not always beneficial. It wastes resources when:

• You only need one field from a large recordset — it loads all fields
• You only need one record but it is part of a large prefetch group
• You access a Binary field (file content) that should not be bulk-loaded

Controlling What Gets Prefetched

# read() fetches only specified fields — no prefetch overhead
data = orders.read(['name', 'amount_total'])
# Returns list of dicts, does NOT use prefetch mechanism

# Or use mapped for a single field
names = orders.mapped('name')  # efficient single-field access

The Prefetch Cache

Prefetched data lives in the environment cache (self.env.cache). It is per-environment and per-transaction:

• Cache is invalidated by write(), create(), unlink()
• Cache is cleared by self.env.invalidate_all()
• Cache does not survive across transactions or requests
• Each sudo() or with_user() gets a shared cache but different access rights

Binary Fields and Prefetch

Binary fields (file attachments, images) are excluded from automatic prefetching since Odoo 13. You must access them explicitly:

class ProductTemplate(models.Model):
    _name = 'product.template'

    # Binary fields have prefetch=False by default
    image_1920 = fields.Binary(attachment=True)

    # Accessing .image_1920 on one record does NOT load images for all records
    # This prevents loading megabytes of image data into memory

You can control this for custom fields:

# Opt out of prefetching for expensive fields
large_data = fields.Text(prefetch=False)
blob = fields.Binary(prefetch=False)  # default for Binary

Debugging Prefetch Behavior

Enable SQL logging to see exactly what queries the prefetch mechanism generates:

# In odoo.conf
log_handler = odoo.sql_db:DEBUG

# Or programmatically
import logging
logging.getLogger('odoo.sql_db').setLevel(logging.DEBUG)

Watch for patterns like:

• Single-record queries when you expected batch — broken prefetch group
• Queries loading all fields when you only need one — consider read()
• Repeated queries for the same data — cache invalidation issue

Best Practices

• Use search() to get recordsets with shared prefetch groups
• Avoid browse(single_id) in loops — collect IDs and browse once
• Use read(['field1', 'field2']) when you need specific fields without full prefetch
• Use mapped('field') for extracting a single field efficiently
• Set prefetch=False on large Text or Binary fields
• Do not call invalidate_all() unless you have used raw SQL