Performance Optimization in Odoo: From Slow Queries to Sub-Second Reports

Every Odoo implementation starts fast. But as data volumes grow, users increase, and customizations become more complex, even well-built modules can begin to slow down. Reports take longer to generate, dashboards become sluggish, and routine operations that once completed in seconds start affecting overall productivity.

In most cases, the problem isn’t Odoo ERP. It’s how custom code interacts with the ORM, database, and framework. Small inefficiencies such as N+1 queries, unnecessary database calls, missing indexes, or poorly designed computed fields can quickly add up, creating significant performance bottlenecks in production.

Drawing on 14+ years of Odoo engineering experience, we have drafted this guide that explores proven techniques to optimize Odoo applications for speed and scalability. From ORM best practices and PostgreSQL optimization to profiling and reporting strategies, these practical insights will help developers build Odoo solutions that continue to perform efficiently, even with millions of records and enterprise-scale workloads.

Why Performance Optimization Matters in Odoo

Performance optimization isn’t just about making screens load faster. It’s about ensuring your ERP can scale with your business.

A poorly optimized customization may work perfectly during development with a few hundred records, but production environments tell a very different story. As transaction volumes increase, warehouses expand, users grow, and reports become more complex, inefficient code compounds into noticeable bottlenecks.

Common business impacts include:

• Slow dashboards delaying executive decisions.
• Long report generation times affecting finance teams.
• Increased PostgreSQL load causing system-wide slowdowns.
• Poor user experience leading to reduced productivity.
• Higher infrastructure costs due to unnecessary server upgrades.

Most performance issues stem from a handful of recurring development patterns. Once you understand how Odoo processes requests internally, identifying and fixing these bottlenecks becomes significantly easier.

Understanding How Odoo Processes a Request

Before optimizing code, it’s important to understand what happens behind the scenes whenever a user clicks a button or opens a record.

A simplified request lifecycle looks like this:

User Action
     │
     ▼
Controller / RPC Call
     │
     ▼
Business Logic (Models)
     │
     ▼
Odoo ORM
     │
     ▼
Prefetch & Cache
     │
     ▼
PostgreSQL
     │
     ▼
Response to User

At every stage, inefficient code can introduce latency. For example:

• A poorly written domain filter may generate expensive SQL queries.
• Excessive ORM calls can overwhelm PostgreSQL.
• Missing indexes force sequential table scans.
• Unstored computed fields repeatedly execute business logic.
• Inefficient loops trigger hundreds or thousands of unnecessary database queries.

Odoo ORM: Powerful, but Not Magic

The Odoo Object Relational Mapper (ORM) is one of the framework’s greatest strengths. It abstracts SQL complexity, enforces security rules, manages relationships, handles caching, and significantly accelerates application development.

However, convenience comes at a cost. Every seemingly simple operation can translate into one or more SQL queries. Without understanding what’s happening beneath the surface, developers often write elegant-looking Python code that performs poorly at scale.

Some common reasons include:

• Lazy loading of relational fields.
• Searching inside loops.
• Repeated write operations.
• Loading entire recordsets when only a few fields are required.
• Ignoring prefetching and caching behavior.

Performance Optimization in Odoo

1. The N+1 Query Problem: Odoo’s Most Common Performance Killer

The N+1 query problem happens when you iterate through records and access a relational field inside the loop, triggering a separate SQL query for every record. This is arguably the most common performance issue found in custom Odoo modules.

The Problem Pattern

for order in self.env['sale.order'].search([]):
    print(order.partner_id.name)

If there are 1,000 sales orders, Odoo executes one query to retrieve all sales orders and one additional query for each related partner. That’s 1,001 SQL queries instead of just a handful.

Nested N+1 — Even Worse

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

Depending on the cache state, this can quickly result in thousands of database calls.

The Better Approach: Use mapped()

orders = self.env['sale.order'].search([])
partner_names = orders.mapped('partner_id.name')

Instead of fetching each partner individually, mapped() batches relational lookups into optimized queries, reducing query counts from thousands to single digits.

Avoid Searching Inside Loops

# Avoid this — one search per partner
for partner in partners:
    invoices = self.env['account.move'].search([
        ('partner_id', '=', partner.id)
    ])

# Do this instead — fetch everything at once
invoices = self.env['account.move'].search([
    ('partner_id', 'in', partners.ids)
])

How to Detect N+1 Queries

• SQL query count grows linearly with the number of records.
• The same SQL statement appears hundreds of times in logs.
• Page response time increases dramatically as data volume grows.
• Flamegraphs show repeated ORM calls.

Best Practices to Avoid N+1 Queries

• Use mapped() for relational fields.
• Fetch related records in batches.
• Avoid search() inside loops.
• Use read_group() for aggregation.
• Let PostgreSQL perform grouping and calculations.
• Always test custom code with production-sized datasets, not demo databases.

2. ORM Best Practices That Instantly Improve Performance

Batch Operations Instead of Repeated Writes

# Avoid this
for order in orders:
    order.write({'state': 'done'})

# Do this instead
orders.write({'state': 'done'})

Batch Create

# Avoid this
for vals in values:
    self.env['sale.order'].create(vals)

# Do this instead
self.env['sale.order'].create(values)

Batch creation significantly reduces database overhead and improves transaction performance.

Avoid Unnecessary sudo()

• Bypasses record rules.
• Increases security risks.
• May reduce cache effectiveness.
• Hides underlying access-control issues.

Use it only where elevated permissions are genuinely required.

3. Choosing the Right ORM Method

search() — for business logic on full records.

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

search_count() — for counting records (uses COUNT(*), much faster).

order_count = self.env['sale.order'].search_count([('state', '=', 'sale')])

search_read() — for reports, APIs, dashboards (fetches only requested fields).

orders = self.env['sale.order'].search_read(
    [('state', '=', 'sale')],
    ['name', 'partner_id', 'amount_total']
)

read_group() — for grouped reports and aggregations.

sales = self.env['sale.order'].read_group(
    [('state', '=', 'sale')],
    ['amount_total:sum'],
    ['partner_id']
)

Quick Comparison

4. Database Indexing: The Most Underused Optimization

Add indexes to custom fields used frequently in domain filters, search views, Group By operations, reporting filters, and foreign key lookups.

class SaleOrder(models.Model):
    _inherit = 'sale.order'

    custom_ref = fields.Char(
        string='Custom Reference',
        index=True
    )

Be selective — adding indexes to every field increases storage usage and slows down write operations.

5. Leverage ORM Cache and Prefetching

# Efficient — ORM can prefetch related data in batches
orders = self.env['sale.order'].search([])
orders.mapped('partner_id.name')

Work with recordsets instead of single records, batch operations together, and avoid repeated searches inside loops to maximize prefetch benefits.

6. Profiling Odoo: Find the Real Bottleneck

Odoo 16+ includes a built-in profiler. Enable it at Settings → Technical → Profiling, reproduce the slow action, and review the flamegraph for repeated SQL statements, high query counts, long-running ORM methods, and N+1 patterns. For deeper analysis, use EXPLAIN ANALYZE in PostgreSQL.

7. Optimize SQL Before Adding More Hardware

Before upgrading your server:

• Check whether indexes are being used.
• Review execution plans using EXPLAIN ANALYZE.
• Eliminate unnecessary joins.
• Reduce duplicate ORM calls.
• Fetch only the fields you actually need.

8. Computed Fields: Store or Compute on Demand?

Use store=True When: the value changes infrequently, the field appears in reports or list views, or the field needs to be searchable. Use store=False When: the value changes frequently, real-time calculations are required, or the field is accessed occasionally.

9. Build Reports That Scale

For dashboards and analytical reports, use SQL-backed models so PostgreSQL handles the aggregation — not Python.

class SaleReport(models.Model):
    _name = 'sale.report'
    _auto = False

    def init(self):
        self._cr.execute(
            "CREATE OR REPLACE VIEW sale_report AS ("
            "    SELECT id, partner_id,"
            "    SUM(amount_total) AS total"
            "    FROM sale_order"
            "    WHERE state = 'sale'"
            "    GROUP BY id, partner_id"
            ")"
        )

Using SQL views for reporting reduces processing time, minimizes ORM overhead, and delivers faster dashboards.

10. Optimize the User Interface

• Avoid displaying too many computed fields in list views.
• Limit the number of records loaded by default.
• Use filters instead of loading complete datasets.
• Remove unnecessary widgets from frequently accessed views.
• Optimize Kanban and dashboard views with only essential information.

Common Odoo Performance Anti-Patterns

• Executing search() inside loops.
• Loading full recordsets when only a few fields are needed.
• Missing indexes on frequently searched fields.
• Performing repeated write() operations instead of batch updates.
• Using Python loops for data aggregation instead of read_group().
• Displaying expensive computed fields in list views.
• Overusing sudo() without necessity.
• Optimizing without first profiling the application.

Odoo Performance Optimization Checklist

• ✔ Eliminate N+1 query patterns.
• ✔ Use search_read() or read_group() where appropriate.
• ✔ Batch create() and write() operations.
• ✔ Add indexes to frequently searched fields.
• ✔ Profile slow actions before optimizing.
• ✔ Store expensive computed fields only when beneficial.
• ✔ Optimize reports using SQL views where applicable.
• ✔ Test with production-sized data, not demo databases.
• ✔ Review PostgreSQL execution plans for slow queries.
• ✔ Benchmark improvements before and after optimization.

Turn Odoo Performance Bottlenecks into a Competitive Advantage with Ksolves

Slow reports, long-running queries, sluggish dashboards, and inefficient custom modules don’t just impact system performance — they affect user productivity, business decisions, and operational costs.

As an Odoo Gold Partner, Ksolves has helped businesses worldwide optimize complex Odoo environments across manufacturing, retail, wholesale, finance, healthcare, and more. From eliminating ORM bottlenecks and optimizing PostgreSQL performance to fine-tuning custom modules and large-scale reporting, our experts build Odoo solutions designed to scale with your business.

With 150+ certified Odoo professionals, 14+ years of ERP engineering expertise, a 99% on-time delivery rate, and successful projects across 80+ countries, we engineer Odoo environments that remain fast, reliable, and future-ready.

Conclusion

By combining efficient ORM usage, thoughtful database design, proper profiling, and scalable reporting strategies, developers can build Odoo applications that continue to deliver consistent performance in production environments. The objective isn’t simply to make Odoo faster — it’s to create ERP solutions that remain reliable, maintainable, and ready to support long-term business growth.