The Ultimate Guide to Data Model and Query Optimization in Odoo 19

At some point, all Odoo modules will run into trouble. A list view that was instantaneous during development takes three seconds in production. A dashboard that was fine all of a sudden performs poorly after importing a few thousand records. A scheduled action that would complete within minutes during testing runs for hours and never finishes.

In nine out of ten cases, it's not the server causing the trouble – it's the database access that is.

Odoo's ORM makes writing functional code effortless; however, there's a big difference between something working and performing well. Inefficient queries, excessive use of loops, unnecessary search methods, and index avoidance could all be silently killing your application's performance. With Odoo 19, you're expected to process more data and support more simultaneous users.

In this blog, we'll look into the true culprits behind slow Odoo queries and how to improve them.

How Odoo Performs Queries

Whenever you deal with any model in Odoo, it converts that action to SQL and sends it to PostgreSQL. Developers typically work with Python and ORM calls — all the database magic goes on in the background.

Consider this example:

partners = self.env['res.partner'].search([
    ('customer_rank', '>', 0)
])

Odoo performs SQL generation behind the scenes. The ORM takes care of many aspects for us — caching, prefetching, access control, multi-company management, and relation handling. This is very practical indeed. However, the abstraction layer doesn't turn bad practices into good ones. It simply buries them under the surface until you have enough data to notice them.

Common Performance Problems in Odoo Models

1. Running Queries Inside Loops

This is probably the most common mistake in Odoo development. It looks harmless, but it's not:

# Don't do this
for partner in partners:
    sale_orders = self.env['sale.order'].search([
        ('partner_id', '=', partner.id)
    ])
    partner.sale_count = len(sale_orders)

With 500 partners, that's 500 separate database queries. One per partner, every time.

The fix is to do it in one shot:

sale_data = self.env['sale.order'].read_group(
    [('partner_id', 'in', partners.ids)],
    ['partner_id'],
    ['partner_id']
)
mapped_data = {
    data['partner_id'][0]: data['partner_id_count']
    for data in sale_data
}
for partner in partners:
    partner.sale_count = mapped_data.get(partner.id, 0)

One query, regardless of how many partners you have.

2. Using read_group() for Aggregations

Whenever you're counting, summing, or grouping records, read_group() should be your first instinct. It pushes the aggregation work into PostgreSQL instead of pulling everything into Python first.

invoice_totals = self.env['account.move'].read_group(
    [('move_type', '=', 'out_invoice')],
    ['partner_id', 'amount_total:sum'],
    ['partner_id']
)

Doing this in Python record by record is always slower, and the gap widens as the dataset grows.

3. Avoiding Unnecessary browse() Calls

Some developers browse records one at a time even when they could batch them:

# Unnecessarily slow
for record_id in record_ids:
    record = self.env['sale.order'].browse(record_id)
    print(record.name)

Just browse them all together:

records = self.env['sale.order'].browse(record_ids)
for record in records:
    print(record.name)

Batch browsing reduces ORM overhead and keeps prefetching working the way it's supposed to.

Understanding Prefetching in Odoo

Odoo is smart about fetching fields — it doesn't query field by field, it batches them:

partners = self.env['res.partner'].search([])
for partner in partners:
    print(partner.name)
    print(partner.email)

This doesn't fire a separate query for every partner.name and partner.email. Odoo fetches them in batches automatically.

But that batching breaks down when records are isolated individually:

# This kills prefetching
for partner_id in partner_ids:
    partner = self.env['res.partner'].browse(partner_id)
    print(partner.name)

Each browse on a single ID creates a separate record set, so the batch logic never kicks in.

Optimizing Computed Fields

Computed fields are frequently the culprit behind slow list views and reports. Here's a simple one that looks fine:

@api.depends('order_line')
def _compute_total_qty(self):
    for order in self:
        total = 0
        for line in order.order_line:
            total += line.product_uom_qty
        order.total_qty = total

It works. But across thousands of records, nested loops like this accumulate quickly. A cleaner version:

@api.depends('order_line.product_uom_qty')
def _compute_total_qty(self):
    for order in self:
        order.total_qty = sum(
            order.order_line.mapped('product_uom_qty')
        )

The @api.depends declaration also matters — being specific about which sub-fields trigger recomputation avoids unnecessary recalculations.

Storing Computed Fields When It Makes Sense

Non-stored computed fields recalculate every time they're accessed. If a field shows up in list views, search filters, or reports, that adds up.

# Recalculated every access
amount_total = fields.Float(
    compute='_compute_amount_total'
)

If the calculation is expensive and the field is read often, storing it is usually worth it:

# Calculated once, stored
amount_total = fields.Float(
    compute='_compute_amount_total',
    store=True
)

The tradeoff is that stored fields write to the database whenever their dependencies change. Whether that's acceptable depends on the read/write ratio and how expensive the calculation is.

Using Database Indexes Properly

Without an index, PostgreSQL scans the entire table to find matching rows. For small tables, this is fine. For large ones, it's a serious problem.

Adding an index on a field is straightforward:

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

If state and date_order appear constantly in large-table queries, not having indexes on them will eventually hurt.

Avoiding search_count() in Loops

search_count() is fine in isolation, but calling it in a loop creates the same problem as searching in a loop:

# One query per partner
for partner in partners:
    count = self.env['sale.order'].search_count([
        ('partner_id', '=', partner.id)
    ])

Use grouped aggregation instead:

data = self.env['sale.order'].read_group(
    [('partner_id', 'in', partners.ids)],
    ['partner_id'],
    ['partner_id']
)

Using limit Properly

Fetching everything when you only need one record is a waste:

# Loads everything, then takes the first
orders = self.env['sale.order'].search([])
latest_order = orders[0]

Tell the database what you actually want:

latest_order = self.env['sale.order'].search(
    [],
    limit=1,
    order='id desc'
)

Reducing ORM Calls with mapped()

mapped() is a clean way to extract values across a record set without writing explicit loops:

# With mapped()
partner_names = orders.mapped('partner_id.name')
# Without it
partner_names = []
for order in orders:
    partner_names.append(order.partner_id.name)

Both produce the same result, but mapped() also plays well with Odoo's prefetching.

Using sudo() Carefully

sudo() bypasses access rules. That means it can load more records than the current user would normally see — and that has performance implications, not just security ones.

# Only use sudo() when you actually need elevated access
records = self.env['sale.order'].sudo().search([])

If you're using sudo() as a shortcut to avoid permission errors, it's worth revisiting whether you need it at all.

SQL Queries vs ORM

The ORM should be your default. It handles security rules, access rights, caching, multi-company logic, and automatic joins — all things you'd have to handle manually with raw SQL.

That said, there are situations where raw SQL is the right call:

self.env.cr.execute("""
    SELECT partner_id, COUNT(*)
    FROM sale_order
    GROUP BY partner_id
""")
results = self.env.cr.fetchall()

Direct SQL makes sense for heavy analytics, complex reporting, bulk migrations, or aggregations across very large datasets. Just know that you're working outside the ORM's safety net.

Bulk Create and Write Operations

Creating or updating records one at a time is much slower than doing it in batch.

# Slow -- one INSERT per iteration
for vals in data_list:
    self.env['custom.model'].create(vals)
# Fast -- single batch operation
self.env['custom.model'].create(data_list)

The same applies to writes:

# Slow -- one UPDATE per record
for record in records:
    record.write({'state': 'done'})
# Fast -- single UPDATE
records.write({'state': 'done'})

Monitoring Queries in Odoo

You can't fix what you can't see. Odoo provides a few useful tools here.

Enable SQL logging by starting Odoo with:

--log-level=debug_sql

This prints every executed SQL query to the logs. It's noisy, but useful for pinpointing problems.

For slow queries, PostgreSQL's EXPLAIN ANALYZE shows exactly what the database is doing:

EXPLAIN ANALYZE
SELECT *
FROM sale_order
WHERE state = 'sale';

It will tell you if it's doing sequential scans (bad on large tables), whether indexes are being used, where joins are expensive, and what's eating the most time.

Understanding ORM Cache

Odoo maintains an in-memory ORM cache to avoid hitting the database repeatedly for the same data. Manually invalidating it when you don't need to can actually hurt performance:

# Only do this when data consistency genuinely requires it
record.invalidate_cache()

Optimizing Many2many and One2many Relations

Nested relational access on large datasets is where a lot of hidden performance problems live:

for order in orders:
    for line in order.order_line:
        print(line.product_id.name)

On a dataset with thousands of orders and lines, this can generate an enormous number of queries. Batching and proper prefetching help significantly  and it's worth thinking about relational access patterns at the design stage, not just when things slow down.

Here's the updated section:

Using Auto Join

In Odoo 18 and earlier, you could hint the ORM to use SQL JOINs on a relation by setting auto_join=True on a Many2one field:

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

This was removed in Odoo 19. The ORM now handles join strategy internally, so there's no field-level flag to set. If you're migrating a module from v18, you can safely drop any auto_join=True declarations.

Query Optimization in Scheduled Actions

Cron jobs commonly process large datasets, and the same patterns that hurt performance in regular code hurt more when applied to millions of records overnight.

A common mistake is loading the entire table:

# Don't do this in a cron job
records = self.search([])

Filter what you actually need, and process in batches:

records = self.search([
    ('state', '=', 'pending')
], limit=500)

For very large datasets, paginate:

offset = 0
limit = 1000
while True:
    records = self.search([], limit=limit, offset=offset)
    if not records:
        break
    records.process_records()
    offset += limit

This keeps both memory usage and query times manageable.

Best Practices for Query Optimization in Odoo 19

• Use batch operations where possible

Do not use create, write, or search operations in a loop.

• Opt for read_group() for aggregate queries

Let the database do the counting and grouping.

• Index your frequently filtered columns

Indices are mandatory in big production databases.

• Minimize unnecessary loading of records

Use limit, filter domains, and batch.

• Do not use computed fields too much

Computed fields without storing are especially problematic in big tables.

• Watch out for slow SQL queries

Development databases are tiny, and slow queries are hard to spot.

• Use the ORM wisely

The ORM is very powerful; however, knowing what is generated is key.

Query optimisation in Odoo 19 is not about replacing ORM with SQL or some arbitrary hacks to boost performance. It means understanding how the ORM talks to the database and writing code that avoids any unnecessary calls to the database.

Almost all performance problems in Odoo apps are caused by too many repeated queries, too few batch operations, not enough indexes, and inefficient calculation methods. Even small improvements in this respect can yield large gains.

As databases grow larger, query optimisation becomes increasingly critical. It is quite possible that a module that works fine with 500 records behaves totally different with 5 million records.

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).