A computed field that calls search_count once per record behaves in a demo database and stalls in production. The query count grows with the row count, so a list view of 5,000 partners fires 5,000 extra queries. This N+1 pattern appears wherever Odoo development services add per-record aggregates to standard models, such as shipment counts, open balances, or overdue totals. This article shows how to find the pattern with the built-in profiler and replace it with one grouped query. It targets Odoo 17 and later on PostgreSQL, and every snippet lives in a custom module. For wider context, see how Odoo development services handle logistics workloads.
Context and Setup
In Odoo Development Services The N+1 problem means one query loads N records, then N more queries read something about each one. Cybrosys’s Odoo ORM performance guide calls it one of the most common causes of Odoo performance problems. Odoo softens it with prefetching. The Odoo 17 source (odoo/models.py) sets PREFETCH_MAX to 1000 records per prefetch batch and INSERT_BATCH_SIZE to 100 for batch creates. Prefetch only batches field reads, though. It cannot batch a search_count() that you wrote inside a loop.
Our scenario is a custom logistics_shipment module. It defines a logistics.shipment model with a partner_id many2one, and a shipment_count field on res.partner that a list view displays. You need Odoo 17 or later, developer mode, and realistic data volume.
The Odoo Development Services Fix: One Grouped Query
The fix is one GROUP BY that counts every partner’s shipments, with results handed back to the recordset.
Step 1: Measure before changing anything
Profile first, because guessing the slow field is unreliable. The built-in profiler ships with Odoo 16 and later.
Activate developer mode.
Enable profiling from the developer tools menu.
Open the partner list view once.
Read the profiler output and look for the same SQL repeated with a different partner_id each time.
Step 2: Group in the database
Here is the slow version most modules start with.
python
from odoo import api, fields, models
class ResPartner(models.Model):
_inherit = "res.partner" # Why: extend the model, never edit core code
shipment_ids = fields.One2many("logistics.shipment", "partner_id")
shipment_count = fields.Integer(compute="_compute_shipment_count")
@api.depends("shipment_ids")
def _compute_shipment_count(self):
for partner in self:
# Slow: one COUNT query per partner, so N+1 under a list view
partner.shipment_count = self.env["logistics.shipment"].search_count(
[("partner_id", "=", partner.id)]
)
Replace the loop body with a grouped read.
python
@api.depends("shipment_ids")
def compute_shipment_count(self):
# Why: one GROUP BY query covers the whole recordset
groups = self.env["logistics.shipment"]._read_group(
domain=[("partner_id", "in", self.ids)],
groupby=["partner_id"],
aggregates=["_count"],
)
counts = {partner.id: count for partner, count in groups}
for partner in self:
# Why: partners without shipments are absent from the result, so default to 0
partner.shipment_count = counts.get(partner.id, 0)
Also set index=True on partner_id in the shipment model. The grouped query filters on that column, so the index keeps it fast as the table grows.
Step 3: Trade-offs for Odoo development services at scale
Pick the option that matches how the field is used.
Non-stored compute with _read_group (shown above): adds no write overhead and costs one query per read of the field. It suits fields that only appear in forms and list views.
store=True: the value is precomputed, searchable, and groupable. Every shipment write now triggers a recompute on the partner, so writes get slower. Choose it for read-heavy dashboards.
Raw SQL: fastest for very large aggregates, but it bypasses record rules and access rights. Use it only when profiling shows the grouped ORM read is the bottleneck.
Real-World Application
In one of our Odoo development services projects at Oodles, a logistics provider’s partner and shipment lists slowed as volume grew.
Key Takeaways
Prefetching batches field reads only, so any search() or search_count() inside a loop still produces N+1 queries.
Profile first and look for repeated identical SQL, then fix only the fields the profiler flags.
Use _read_group with __count and a dictionary lookup to turn N queries into one.
Choose stored fields only for read-heavy data, because they move the cost to every write.
Index the grouping column, and treat raw SQL as a last resort because it skips record rules.
Discuss It
Hit a slow computed field in your own module? Post the profiler output in the comments, or talk to us about Odoo development services.
What is the N+1 query problem in Odoo?
It occurs when code runs one query to fetch N records, then one extra query per record to read related data or aggregates. Query count scales with record count, so views that look fine on small test data slow down in production.
Does Odoo prefetching fix N+1 queries automatically?
Only for field reads on records in the same recordset. Prefetch loads related fields in batches, but it cannot merge a search(), search_count(), or single-record browse() call that you place inside a loop.
Should I use read_group or _read_group?
Use read_group() when a client consumes dictionaries, such as an RPC call. Use _read_group() inside compute methods for tuple results. Signatures changed between releases, so confirm the one in your Odoo version before copying code.
How do I find slow computed fields in Odoo?
Enable developer mode and the built-in profiler, then load the slow view. Repeated identical SQL statements that differ only by record id point to a per-record compute or search that needs batching.
When do Odoo development services need raw SQL?
Rarely. Reach for it only after profiling shows a grouped ORM read is the bottleneck, such as aggregates over millions of rows. Raw SQL bypasses record rules and access rights, so add company and security filters yourself.
Top comments (0)