Profiling slow Odoo modules: PostgreSQL EXPLAIN, Python profilers, and the Odoo profiler

A slow Odoo action is usually slow for one of three reasons: a query that could be fast is not, a compute method is called more times than it needs to be, or a batch operation is doing N+1 queries. Each cause requires a different tool to identify. Using the wrong tool is why performance investigations often conclude with "we optimized the wrong thing."

This post walks through the three-layer profiling stack that covers most real-world Odoo performance issues.

Layer 1: PostgreSQL EXPLAIN ANALYZE#

Start with the database. Most Odoo performance problems are SQL problems. Before touching Python, confirm that the slow operation is generating slow queries.

Enable query logging#

In odoo.conf (development only):

[options]
log_handler = odoo.sql_db:DEBUG

This logs every SQL query with its execution time. In production this is too verbose - use it in a development instance pointed at a copy of production data.

Capture slow queries#

In PostgreSQL, enable slow query logging:

-- In postgresql.conf or via ALTER SYSTEM
ALTER SYSTEM SET log_min_duration_statement = '100ms';
SELECT pg_reload_conf();

Any query taking over 100ms will appear in the PostgreSQL log. The threshold is adjustable - start at 100ms and lower it as you eliminate the worst offenders.

Read the EXPLAIN output#

Once you have a slow query, run it through EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT *
FROM account_move
WHERE company_id = 1
  AND state = 'posted'
  AND date >= '2026-01-01'
ORDER BY date DESC
LIMIT 100;

What to look for:

• Seq Scan on a large table - a full table scan that should be an index scan. Add an index.
• Nested Loop with high row estimates - planner underestimated row counts. Run ANALYZE account_move to update statistics.
• High buffer hits - data is in memory (good). High buffer reads - data is hitting disk (bad, indicates missing index or working set too large for shared_buffers).

Adding indexes to custom models#

If EXPLAIN shows a Seq Scan on a custom model's field that is frequently filtered:

class EquipmentLoan(models.Model):
    _name = "equipment.loan"

    state = fields.Selection([...], index=True)  # Adds a PostgreSQL index
    partner_id = fields.Many2one("res.partner", index=True)
    due_date = fields.Date(index=True)

Adding index=True to a field generates a PostgreSQL B-tree index on the column. For compound filters (e.g. always filtering by state AND partner_id), a composite index is more efficient:

_sql_constraints = [...]

def _auto_init(self):
    super()._auto_init()
    # Composite index for the common filter pattern
    tools.create_index(
        self._cr,
        "equipment_loan_state_partner_idx",
        self._table,
        ["state", "partner_id"],
    )

Layer 2: Python profiler#

SQL accounts for most slow operations but not all. Compute methods with expensive logic, recursive computations, or methods called in a tight loop show up in Python profiling, not in SQL.

cProfile in a shell script#

import cProfile
import pstats
import io

pr = cProfile.Profile()
pr.enable()

# The code you want to profile
records = env["equipment.loan"].search([("state", "=", "active")])
values = records._compute_overdue_stats()

pr.disable()

stream = io.StringIO()
ps = pstats.Stats(pr, stream=stream).sort_stats("cumulative")
ps.print_stats(30)
print(stream.getvalue())

Run this in an Odoo shell (odoo-bin shell). The output shows the 30 most time-consuming function calls sorted by cumulative time. The top entry is usually not the actual bottleneck - it is often execute_kw or the Odoo ORM dispatcher. Scroll down to find your code.

What cProfile finds#

The classic pattern is a compute method that calls self.search([...]) inside a loop:

# Slow: executes a query for every record
def _compute_active_loan_count(self):
    for record in self:
        record.active_loan_count = self.env["equipment.loan"].search_count([
            ("partner_id", "=", record.id),
            ("state", "=", "active"),
        ])

cProfile shows search_count consuming most of the time. The fix is a read_group:

# Fast: one query for all records
def _compute_active_loan_count(self):
    domain = [
        ("partner_id", "in", self.ids),
        ("state", "=", "active"),
    ]
    counts = self.env["equipment.loan"].read_group(
        domain=domain,
        fields=["partner_id"],
        groupby=["partner_id"],
    )
    count_by_partner = {row["partner_id"][0]: row["partner_id_count"] for row in counts}
    for record in self:
        record.active_loan_count = count_by_partner.get(record.id, 0)

Layer 3: The Odoo built-in profiler#

Odoo 16+ includes a built-in profiler accessible from the debug menu. It captures SQL queries, Python call stacks, and RPC call timings in an integrated view.

Enabling it#

In the Odoo UI: Settings → Technical → Profiler (requires debug mode). Or via URL: append ?debug=1 to any page URL, then open the debug menu (bug icon in the nav bar) and select "Start profiler."

The profiler records every operation during the profiling session and displays a flamegraph and SQL query log when you stop it.

When to use the built-in profiler vs cProfile#

Use the built-in profiler when:

• You need to profile a specific UI action (button click, page load, form save)
• You want the combined Python + SQL view in one place
• The slowness is in a user-facing action you cannot easily replicate in a shell

Use cProfile when:

• You want to profile a specific code path without the overhead of the full UI
• You are running in a headless environment (CI, production log analysis)
• You need fine-grained call-level timing

Reading the Odoo profiler flamegraph#

The flamegraph shows time on the X axis and call depth on the Y axis. The widest bars at the top are the slowest calls. Click any bar to see the Python source.

The most useful view is the SQL tab: it lists every query in order with its execution time and the Python call stack that generated it. This is the fastest way to find N+1 query patterns - you will see the same query executing 50 or 100 times with slightly different parameter values.

The order to apply fixes#

1. Check EXPLAIN - fix missing indexes first. This is the highest-leverage change and takes minutes.
2. Check the SQL tab of the built-in profiler - find N+1 patterns and replace loops with read_group or mapped.
3. Check cProfile for expensive compute methods - optimize or cache.

Do not skip step 1. Many Python-level fixes add complexity that is not necessary if a 5-minute index addition would resolve the problem.