PostgreSQL Query Optimization for ERP Scale

ERP databases are not slow by accident. They are slow because the same patterns that make ORMs productive — automatic joins, lazy loading, computed field resolution — scale poorly against tables with millions of rows and multi-company filtering. The fix is not to abandon the ORM. The fix is to understand exactly where it breaks down.

Read the Query Plan Before Changing Anything

EXPLAIN ANALYZE is the starting point for every slow query investigation. The plan shows actual execution times, row estimates, join strategies, and which nodes are doing sequential scans on large tables. A sequential scan on a hundred-row table is harmless. The same scan on a stock move table with ten million rows is a budget problem.

When reading a plan, look for these signals:

• Sequential scans on tables that grow with transaction volume: account moves, stock pickings, sale order lines, purchase orders.
• Large discrepancies between estimated and actual row counts, which indicate stale statistics and lead to wrong join strategies.
• Nested loop joins on high-cardinality results, which compound cost multiplicatively.
• Sort operations without a supporting index, particularly on date and state columns.

Odoo’s ORM generates queries that are frequently correct but rarely optimal. The gap grows with table size.

Why Multi-Company and State Filters Are Expensive Without the Right Index

In Odoo, nearly every query carries implicit filters: the company, the active flag, and often a lifecycle state. These filters look trivial in Python but translate to non-trivial scan conditions in SQL when the planner cannot satisfy all three with a single index.

A query filtering account moves by company, state, and date will do a full table scan unless a composite index covers those columns in an order the planner can exploit. Partial indexes go further: an index scoped to only unposted moves, or only open purchase orders, is smaller and faster for the most common access patterns.

Indexing decisions should be driven by query plans, not intuition. Columns that appear in every WHERE clause on business-critical paths — company, state, date, partner — deserve composite or partial coverage. Columns that appear only in rare reports do not.

Where ORM-Generated Queries Become Costly

Odoo’s ORM is a layer of abstraction over SQL, and abstraction has a price. Computed fields with subqueries are evaluated lazily — a single record browse can trigger multiple round trips if fields are not prefetched. Many-to-many relational fields produce join chains that are hard for the planner to optimize. Search domain translation can generate redundant SQL conditions that prevent index use.

The most expensive ORM-generated queries in production Odoo instances tend to share a few characteristics:

• They aggregate over large transactional tables without pushing the aggregation into a subquery or a materialized view.
• They filter on computed stored fields that are not indexed, forcing a full table scan for every domain match.
• They resolve many-to-many relationships with IN clauses over large sets, which the planner often handles worse than a join.
• They retrieve wide records with dozens of columns when only three or four are needed.

When to Drop to Raw SQL

There is no rule against raw SQL in Odoo. The ORM exposes cursor access precisely for cases where the abstraction is counterproductive. Reporting endpoints, aggregation jobs, and dashboards that summarize millions of rows are the clearest candidates.

Raw SQL is appropriate when the query requires window functions, CTEs, or aggregation strategies the ORM cannot express cleanly — and when joining across tables in a way that must bypass Odoo’s record rules safely, which requires explicit awareness of security boundaries.

The discipline is to keep raw SQL contained and documented. A raw query embedded in a business method without explanation becomes a maintenance liability. The same query in a dedicated reporting layer, with a note explaining why the ORM was bypassed, remains readable.

Validate Under Production-Like Data Volume

A query that runs in 20 milliseconds on a staging database with 50,000 records can run in 4 seconds against 8 million rows in production. This is not a surprise — it is a predictable consequence of how the planner’s cost model shifts with table statistics. Optimization work validated only on small datasets is not finished.

Testing under realistic volume means running plans against a production-sized anonymized copy, verifying that row count estimates match actuals, and confirming index use. It also means accounting for write amplification: indexes that improve reads add overhead to inserts and updates, and ERP systems write heavily.

PostgreSQL optimization in ERP contexts is not about one fast query. It is about a database that stays responsive as the business scales, with query plans that were chosen deliberately rather than inherited from defaults.