| name | odoo-perf |
| description | Making Odoo fast — recordset hygiene (no query-in-loop, search-once-with-in, mapped/filtered/sorted), the ORM cache & prefetching, stored vs non-stored computed fields (write amplification, exhaustive @api.depends), database indexes (index=True and typed indexes), _read_group aggregation, batch create/write, and justified raw SQL with correct cache invalidation. Use whenever an Odoo page/list/report/cron is slow, an N+1 query pattern appears, you're about to add store=True or an index, or you're writing a loop that search()es or browses one record at a time. Measure real SQL counts from the running instance — don't guess. Targets Odoo 17/18/19. |
Odoo performance
In Odoo, wall-clock is dominated by SQL query count, not Python. The ORM already batches writes, prefetches relations, and caches reads across a whole recordset — almost all slowness is fighting it: a search or browse inside a loop turns one query into N. Measure the real count first, then fix the hot path. Delegate measurement to the odoo-introspect skill — trace_flow reports total_sql and per-call sql_count:
odoo-ai --db <DB> trace <model> <record_id> <method>
Version floor: Odoo 17/18, through Odoo 19 (current LTS). The _read_group tuple API and the flush_* / invalidate_* cache methods are v16/17+; pre-16 uses read_group (dicts) and flush() / invalidate_cache(). Note read_group is deprecated in 18.2 (use _read_group, or formatted_read_group for formatted public output), and the field aggregate attribute group_operator was renamed aggregator in 17.2 — see skills/odoo-introspect/references/version-matrix.md.
Use the built-in, don't hand-roll it
| Need | Use | Not |
|---|
| Count / sum / group per key | _read_group(domain, groupby, aggregates) | search + count in a loop |
| Load N records' relations | one recordset + mapped / prefetch | browse(id) per row |
| Filter / sort already-loaded recs | recs.filtered(...), recs.sorted(...) | re-search with a tweaked domain |
| Update many rows the same way | recs.write({...}) (one UPDATE) | for r in recs: r.write(...) |
| Insert many rows | create([vals, vals, ...]) | for v in vals: create(v) |
| Searchable / groupable derived value | stored compute + index | recompute on every read |
| Display-only derived value | non-stored compute | store=True (write amplification) |
Recordset hygiene (where performance dies)
- Never query in a loop.
for r in recs: env['x'].search([('y','=',r.id)]) is N queries. Search once — env['x'].search([('y','in',recs.ids)]) — then group in Python with filtered / mapped.
- Set ops stay in cache:
recs.filtered(lambda r: r.state=='done'), recs.mapped('partner_id'), a | b, a - b, a & b.
- Aggregate with
_read_group, not per-record counting. v17 returns a list of tuples — e.g. dict(model._read_group(domain, ['partner_id'], ['__count'])) — not the old list-of-dicts; don't index results by key name.
recs.write({...}) once batches a single UPDATE; field-by-field in a loop is N×fields writes.create([...]) a list once (@api.model_create_multi) instead of looping create.
The ORM cache & prefetch — why looping is slow
Reading a field on one record of a recordset triggers a prefetch of that field for the whole recordset in one query — so iterating a recordset is cheap, while browse(id) one-at-a-time defeats it (a query each). Keep records in a single recordset and let mapped / dotted access prefetch. Manage the cache explicitly only around out-of-band writes:
self.env.flush_all() (or self.flush_recordset([...])) — push pending ORM writes to the DB, e.g. before raw SQL reads.self.invalidate_recordset([...]) / self.env.invalidate_all() — drop cached values after changing rows behind the ORM's back.
Stored vs non-stored computes
store=True only if you must search, group, or report on the field. Stored computes add write amplification: every write touching a dependency re-runs the compute and writes the column.- When stored,
@api.depends(...) must name every path that can change the value, including dotted ('line_ids.price_subtotal'). Miss one ⇒ the stored value silently goes stale (no error). Audit against model_brief.fields[].depends.
- Non-stored computes cost nothing on write and recompute on read; add a
_search_<field> if it must be filterable without storing.
related= is a thin stored/non-stored compute — same rules.
Indexes
index=True → a btree index. Add it to fields you search or group by often (foreign keys used in domains, status fields). Indexes cost write time and disk — don't index everything.index= also accepts typed values: 'btree_not_null' (partial index skipping NULLs — ideal for a mostly-empty optional m2o/bool), and 'trigram' (for like / ilike text search; requires the PostgreSQL pg_trgm extension). index=True is 'btree'.- A non-stored computed field has no column, so it can't be indexed — store it first if it must be.
Raw SQL — only when justified
Reach for self.env.cr.execute only when the ORM genuinely can't express it (heavy aggregation / window functions) and you've measured the win. Then:
- Parameterize —
cr.execute("... WHERE id = %s", (rec.id,)), never an f-string (SQL injection).
self.env.flush_all() before reading, so pending ORM writes are in the DB.self.invalidate_recordset([...]) (or self.env.invalidate_all()) after writing, so the ORM doesn't serve stale values.
Raw SQL bypasses ACL, record rules, computes, and constraints — you now own all of them.
Gotchas that fail silently
- Incomplete
@api.depends on a stored compute — the value silently rots; surfaces months later as "wrong total". The brief lists the real depends to audit.
store=True "just in case" — every dependency write now recomputes; on a hot model this is a real regression with no error. Store only to search/group/report.- N+1 hidden behind a compute — a non-stored compute that
searches makes every row in a list view its own query. Catch it with --log-handler=odoo.sql_db:DEBUG or trace_flow.
mapped() on a non-stored field still computes it — mapped avoids queries for stored/related fields but runs a non-stored compute per record; know which you have.- Raw SQL without
invalidate_recordset — ORM serves stale cached values, then CacheMiss (see odoo-debug).
_read_group return shape (v17) — tuples, not dicts; old [d['field'] for d in res] code silently breaks after upgrade. Also: public read_group is deprecated in 18.2 (prefer _read_group / formatted_read_group), and a stored compute/searchable field's aggregate attribute is aggregator= since 17.2 (group_operator= is the old name).
References & related skills
This skill's reference
references/performance-patterns.md — N+1 before/after, prefetch mechanics, _read_group v17 recipes, stored-compute write-amplification math, the index decision matrix, the raw-SQL checklist, and reading trace_flow SQL counts.references/n-plus-one-and-prefetch.md — the recordset/prefetch model, N+1 patterns (per-record search in a loop, .mapped() vs python loop, _read_group aggregates), stored/index decisions, flush / invalidate pitfalls, and reading trace's top_self_sql + call_counts to FIND the hotspot rather than guess.
Other skills in the loop
odoo-introspect — Tier 0 engine; trace_flow for real total_sql / sql_count, model_brief for depends / store / index facts.odoo-debug — SQL logging (--log-handler=odoo.sql_db:DEBUG), CacheMiss decoding.odoo-dev — choosing the right hook so you don't hand-roll what the ORM batches.
