Odoo ORM Performance: How to Avoid N+1 Queries and Loops

There is no shortage of coding guidelines for Odoo. However, one issue that frequently comes up during code reviews is the need to avoid executing one database query per record when iterating over a recordset. This problem, known as the N+1 query issue, is one of the most common causes of performance problems in Odoo. A feature that works well with 20 records in a test environment may become significantly slower in production, where the data volume can reach 20,000 records.

This blog will explain what N+1 looks like in the Odoo world, how to find it in practice, and how to fix it properly.

Version note: all code samples are meant for Odoo 17/18. Since the grouping functionality of the Odoo ORM underwent some changes, we'll use the new syntax (_read_group) instead of the deprecated read_group. The newest Odoo 19 version introduces constraints/indexes defined on models as attributes.

1. Why This Is the #1 Code Review Flag

N+1 queries are dangerous because they're invisible in development. Your local database has a handful of records, the page loads instantly, and the code review passes. Then it ships, the client imports their real data, and suddenly a "simple" list view takes 20 seconds to load, or a scheduled report times out and never finishes.

The pattern is always the same: 1 query to fetch a set of records, then N more queries - one per record - to fetch something related to each of them. Get 500 partners and check each one's order count one at a time, and you've just made 501 round trips to the database for something that should have taken one or two.

The fix is almost always available in Odoo's ORM already. The problem is knowing where to look and what to swap it for.

2. What N+1 Looks Like in Odoo

A classic example of an anti-pattern is using a Python loop, where search() is called within the body of the loop:

@api.depends('sale_order_ids')
def _compute_order_count(self):
    for partner in self:
        orders = self.env['sale.order'].search([('partner_id', '=', partner.id)])
        partner.order_count = len(orders)

For instance, if self contains 1,000 records, this code will make 1,000 independent calls to search(), and each one will instantiate all records for the orders just to do a len(). (If you really have no choice but to do a per-record query, at least try search_count(), which performs a COUNT(*), not record instantiation. As of Odoo 16, it also respects the limit, which is useful if an incomplete count is sufficient).

This can be confirmed in the query log during a slow request, where the same pattern of query gets repeated again and again, just with a different id each time. This is N+1.

Here’s the solution, using grouped aggregation rather than a loop:

# GOOD - one query total (Odoo 17+)

@api.depends('sale_order_ids')
def _compute_order_count(self):
    groups = self.env['sale.order']._read_group(
        domain=[('partner_id', 'in', self.ids)],
        groupby=['partner_id'],
        aggregates=['__count'],
    )
    count_map = {partner.id: count for partner, count in groups}
    for partner in self:
        partner.order_count = count_map.get(partner.id, 0)

In Odoo versions up to 16, it's the same with the legacy API:

data = self.env['sale.order'].read_group(
    domain=[('partner_id', 'in', self.ids)],
    fields=['partner_id'],
    groupby=['partner_id'],
)
count_map = {d['partner_id'][0]: d['partner_id_count'] for d in data}

(The <field>_count key is only used with the default lazy=True and one groupby. If lazy=False, several groupbys are used; the count will come through under __count.

The same results, but in one query instead of 1,000.

One should keep in mind that a partner in a self-loop at the end of the function call is free in this case since it is a compute method: writing into a field from within a compute function stores the value in the ORM cache rather than writing it to the database.

Prefetching already covers a lot of cases

N+1 is prevented by Odoo's ORM for almost all typical cases of field access via prefetching. When you access a field from an individual record of a recordset, Odoo will not only load that specific field but also the additional fields that can be fetched in a batch:

for partner in partners:

print(partner.name) # This will prefetch 'name' and some others from the batch

print(partner.lang) # Will use cached value — no additional query needed

Two facts to note:

• Prefetching is not confined to just one level. When reading partner.country_id.name in a loop, it does not take one query per partner: access to the relational field through the first record pre-fetches it for all the records in the batch, and those records have their own set of pre-fetches, so the same goes for the second hop down the line, and so forth. This way, the cost depends on the number of hops in the chain, not the number of records, unless you follow the same path within each recordset that was fetched in one go.
• Prefetching occurs in batches of 1,000 records at a time by default. Iterating through a recordset of 20,000 records takes about 20 queries for each group of fields, not one. It's still okay, but in the case of very large recordsets, "one query" is an approximation, and here is yet another reason to process the data in batches

However, N+1 becomes problematic again in scenarios when search() is invoked manually in a loop, browse(id) is invoked by ID rather than the entire group, or when write()/create() is invoked one record at a time.

3. Tools to Detect It

The built-in Odoo Profiler (first choice)

Modern Odoo comes with the built-in Profiler that records all SQL queries and stack traces for a session or for any particular snippet of code and allows reviewing its output in the form of a flame graph (by means of the built-in Speedscope visualizer) or just a list of queries.

The usage procedure is as follows:

1. Switch developer mode on.
2. Activate profiling on the entire database. This can be done either via developer mode tools by switching on the Enable profiling switch—here the wizard will offer various values of the expiration time—or via Settings > General Settings > Performance settings, setting up the value in the Enable profiling until field.
3. Turn the Enable profiling switch on via developer mode tools for your own session. Recommended settings: Record sql and Record traces are selected by default.
4. Trigger the action causing slowness (display the report, load the list view, run the cron job).
5. Review the profiling data from developer mode tools — each session is stored in ir.profile record and allows drilling down to the actual queries performed and their count.

Profiling can also be invoked programmatically, in the case of automation testing, for example:

with self.profile():
    with self.assertQueryCount(__system__=50):
        do_stuff()

Nesting order matters and is recorded: the Profiler must go outside of assertQueryCount because it needs to catch queries issued during execution of the inner context manager, like flush.

assertQueryCount deserves special mention itself: it lets you specify a hard limit on the number of queries that some code can execute so that a future bug where you re-introduce the N+1 problem makes your test suite fail rather than be shipped. The keyword arguments for it are checked against the login of the test-running user (admin=3, demo=5); __system, which is simply the login name of the root user (uid=1), and that's who your tests run as by default.

A note about setting the ceiling on the query count: it won't be reliable unless you've warmed up your ORM caches. The actual assertion will only be applied during the warm phase, so use the @warmup decorator (usually along with @users) and make it run the method twice — first without asserting the limit, to warm caches, then for real:

from odoo.tests import users, warmup
@users('__system__')
@warmup
def test_order_count_queries(self):
    with self.assertQueryCount(__system__=5):
        self.partners._compute_order_count()

The test becomes unreliable since the count will vary depending on what was run beforehand, and you're chasing down an unreliable test rather than a regression.

Note: this profiling tool doesn't work for Odoo Online (SaaS) databases – you need either Odoo.sh, on-premise, or development installation.

--log-sql for quick unformatted queries list

To get all queries hit in the database without profiling, start the server with SQL-level debugging enabled:

odoo-bin --log-sql -d your_db

(--log-sql is equivalent to --log-handler odoo.sql_db:DEBUG.) It will produce a lot of noise, i.e., each query that is executed by the server, so it can be used only for a fast sanity check of whether a single action executes hundreds of queries.

odoo-bin shell for isolating suspicious methods

If you've already identified which method or computed field causes trouble, the odoo-bin shell allows you to call it directly without running the application:

$ odoo-bin shell -d your_db
>>> partners = env['res.partner'].search([('customer_rank', '>', 0)])
>>> partners._compute_order_count()  # call the suspected method directly

Combine with a context manager from the profiling tool for isolated measurement of that particular method.

4. Fixes

Fetch the batch once, then iterate

Prefetching is useful only if you are iterating through a record set that has been prefetched. Requerying for each record one by one within a loop will defeat the purpose completely:

# BAD — one query per id, prefetching can't help

for partner_id in partner_ids:
    partner = self.env['res.partner'].browse(partner_id)
    do_something(partner.email)

# GOOD — one browse, one prefetch batch

for partner in self.env['res.partner'].browse(partner_ids):
    do_something(partner.email)

This is how "don't query in a loop" ends up costing you extra queries, and it can happen quite easily in case the ids come from an external call or JSON data.

Use mapped() for readability

emails = partners.mapped('email')

Just for you to know what benefit you get here: mapped() doesn't help with reducing the number of queries executed. A simple for loop for partners in partners: emails.append(partner.email) executes the same number of queries due to prefetching. Use mapped() for code readability, brevity, and slightly better performance in Python, but don't use it thinking it saves database hits.

It allows chaining calls easily across relations without explicitly using a loop:

# Countries of all the commercial partners of all the partners in one line

countries = partners.mapped('commercial_partner_id.country_id')

One important difference in behaviour: on a relational field, the mapped() method produces a recordset which is a union of all the records, where duplicates are filtered out; according to Odoo documentation, the order of this recordset is undefined. On a scalar field, it produces a simple list of values, where duplicates are kept, and one value is produced per record in order.

Use grouped aggregation instead of Python loops

Whenever you find yourself ready to iterate through records just for the purpose of counting, adding up, or grouping something, chances are that the ORM’s grouping API does the same thing using one SQL GROUP BY command:

# Odoo 17+

groups = self.env['sale.order']._read_group(domain=[('state', '=', 'sale')],
    groupby=['partner_id'],
    aggregates=['amount_total:sum'],
)

# Odoo 16 and earlier

data = self.env['sale.order'].read_group(
    domain=[('state', '=', 'sale')],
    fields=['amount_total:sum'],
    groupby=['partner_id'],
)

Returns a list of tuples, first the groups (recordsets in case of relational fields) and then the aggregations, unlike the old _read_group that used to return a list of dictionaries.

Format values using read_group

The public API on Odoo 19 is formatted_read_group when you want the formatted values.

Batch write and create as well

Create takes a list of dictionaries, so multi-create is easy:

# BAD

for vals in vals_list:
    self.env['sale.order.line'].create(vals)

# GOOD — one call

self.env['sale.order.line'].create(vals_list)

write() is more difficult since each write call uses the same values for all the records within a recordset. One can only batch records which have the same target values, meaning that the real way to do it would be to first group your records by the values you are going to write,

# GOOD - one write call per state, not per record

for state in ('sale', 'cancel'):
    orders.filtered(lambda o: should_be(o) == state).write({'state': state})

filtered() operates in Python on already fetched records and therefore makes no additional queries. In cases where the set of values is unknown beforehand, construct the {value: recordset} mapping in one pass and call write() only once per entry. (Note that using the values dictionary itself as a map key might be problematic because x2many command lists are not hashable.)

In cases when every record requires its own unique value, the record-based write() call is inevitable — and this is typically an indication that the data should either be a computed field or should reside in another model structure altogether.

Computed fields: store=True vs. on-the-fly compute

When computing a field and setting store=False, the field will be recomputed each time it is accessed – great for seldom used calculations, terrible when it appears on a list view that gets rendered hundreds of times. When setting store=True, the computation is cached and updated only when one of its @api.depends changes, at the expense of one write.

Store the computation of a field if it appears in list views or if it is read more often than its dependencies change. Two important things to keep in mind before deciding:

• Not all computes can be stored. Everything relies on the current user, the request context, and fields.Date.today(), or any value that can’t be put inside @api.depends, becomes stale without noticing it. Just leave it unstored.
• But writing to it may become expensive sometimes. When there’s a large table, with a long dependency tree, changing one of the dependencies can cause a cascade of recomputation operations on many lines of the table, making the write operation slow. Evaluate both sides; don’t evaluate only the right side.

Finally, bear in mind that searching/sorting on a computed field requires either storing the computed field or defining a search() function for it.

5. Indexing

Addressing the query count is just half the problem — if the queries left are still slow because they involve scanning through an entire table without an index, then you have simply replaced N+1 slow queries with one big slow query.

Adding an index to the database to a custom field that is commonly filtered, searched for, or grouped by:

partner_id = fields.Many2one('res.partner', index=True)

The index has a value instead of just a Boolean value, and it makes a difference:

• True or "btree" – mentioned in the field reference as the default index, suitable for a many2one.
• "btree_not_null" – the btree index without NULLs, applicable when almost all values are NULLs or NULL is not supposed to be searched.
• "trigram" – the GIN index with trigrams, useful for text search. Trigram functionality can be activated in Odoo only if there is a word_similarity function in the database, which is provided by PostgreSQL's pg_trgm package.

Two warnings. First, indexes come at a cost. They increase write performance and use space on your hard drive, so add them to those places that have been tested for slow queries, not everywhere. Also, according to the field documentation, indexing has no impact on non-stored or computed fields.

As for constraints specific to your own business rules or uniqueness requirements, _sql_constraints will enforce them directly in the database rather than in Python code (and as a bonus, the index Postgres will be created for it):

_sql_constraints = [
    ('unique_reference', 'unique(reference)', 'Reference must be unique!'),
]

This changes in Odoo 19 to declare the constraints and indices as model attributes; the list of tuples format shown above is valid up to 18.

A good technique for discovering where a performance improvement through an index could be done is enabling Record SQL in the profiler, observing the queries taking most of the cumulative time, and seeing if those queries filter/join using an unindexed field. The EXPLAIN ANALYZE from Postgres of that query would confirm if it is performing a sequential scan.

6. Before/After: What This Actually Looks Like

Consider the following partner order count example that was discussed before, executed on the dataset with 1,000 partners:

• Search for partner loop: roughly 1,000 searches, some seconds of loading, and it becomes progressively worse with an increase in the number of partners.
• Aggregation by group: only 1 search, be it 1,000 partners or 100,000 partners.

The number of queries remains constant; however, each individual query becomes slower due to the fact that the sorting of 100k orders requires much more resources than the sorting of 1k orders. This is the curve which can be smoothed out using indexes, whereas the linear round trip curve cannot be fixed.

This is what all fixes for the N+1 problem look like. The actual values might differ according to the specific data set and hardware used; however, the general picture – when a linear number of queries is turned into a constant one – should be visible in the query count in the profiler.

7. ORM Performance Audit Checklist

Before shipping any program that uses a recordset with more than a few records, run through all this:

• [] Is there any search() or browse(id) query in a for loop? Pull it out of the loop and use batching.
• [] Is there any aggregation (count, sum, average) calculated in a Python loop when it should be done in a single grouped query?
• [] Is there a call to create() done record by record when it should be done once per a list of dictionaries?
• [] Is there a call to write() done record by record where the records can be batched on identical values?
• [] Any search() plus len() which should be search_count()?
• [] Computed fields shown in list/tree views: is store = True, and is it safe to store them?
• [] Any custom fields used in filters, domains, or sorting – do they have an appropriate index?
• [] Have you tried the built-in Profiler (at least –log-sql) on this feature with a realistic (not test-sized) dataset?
• [] Is this a feature that might be worked on again in the future – does it have an assertQueryCount test to protect against regressions?

By checking all the boxes, you can avoid what is the biggest reason for Odoo customization failure in production.

Optimization of Odoo apps through N+1 Query avoidance is paramount in ensuring scalability and efficiency. With the help of ORM prefetching, grouped aggregation, batching, use of proper indexes, and the inbuilt profiler, one is able to limit unneeded database calls. It is important to always test customizations against realistic data sets.

To read more about Overview of Data Model Optimization in Odoo 19 (Avoiding ORM N+1 Queries), refer to our blog, Overview of Data Model Optimization in Odoo 19 (Avoiding ORM N+1 Queries).