Query optimizations in Odoo versions 17–19 for faster PostgreSQL performance

We get a lot of queries about performance optimization for PostgreSQL from organizations using Odoo. The platform has been built entirely on PostgreSQL, and Odoo’s ORM and database layer have undergone several performance‑oriented changes across releases 17, 18, and 19 to reduce SQL overhead and take better advantage of PostgreSQL.

I have always maintained that SQL optimization is the most significant factor contributing towards database performance improvements, so I am very happy to see these improvements being rolled out. 

Last week, Odoo released version 19 and ended support for version 16. If that isn’t motivation enough, I have gathered some of the performance improvements made in the system after version 16. Hopefully, that gives you enough incentive to upgrade Odoo in order to reduce performance woes. 

The optimizations below are drawn from the official changelog and pull-request discussions, highlighting the most relevant changes that affect query generation and execution. 

Odoo 17.x

1.Combining search and read queries: In Odoo 17.4, the implementation of searching and reading was refactored so that search()/search_read() can perform the search and fetch fields in the same SQL query. Two new methods – search_fetch() and fetch() – were introduced to minimize the number of queries needed for search_read. By fetching the required fields at the time of the search, the ORM avoids executing separate SELECT statements for each record.

2. Limit parameter for search_count(): Odoo 17 added a limit argument to search_count(). When a UI element only needs an approximate count, the ORM stops counting after the limit and returns the approximate number, avoiding full table scans.

3. Specific index types: Developers can now specify the type of index when declaring a field. Supported types include:

  • btree (default).
  • btree_not_null (used when most records have NULL values), and
  • gin trigram (for ILIKE/LIKE searches on character fields)

Exposing these index types allows the ORM to create efficient indexes suited to the query pattern. For instance, trigram indexes speed up fuzzy searches, while a btree_not_null index avoids indexing mostly‐NULL columns.

4. Grouping and aggregation improvements: Later 17.x releases introduced the ability to group by date‑part numbers in read_group and domains and renamed the group_operator attribute to aggregator, enabling more expressive grouping and aggregation in a single query.

5. Replacement of inselect operator: The internal inselect operator was removed and developers are encouraged to use the standard in operator together with a Query or SQL object, simplifying query generation and reducing special cases.

6. Flush and caching API: The _flush_search method was deprecated; flushing is now done via execute_query(), which uses the SQL object’s metadata to ensure only necessary fields are flushed. A new API for flushing and cache invalidation was also introduced, reducing redundant commits.

Odoo 18.x

  • Declarative constraints and indexes: Odoo 18.1 introduced models.Constraint, models.Index and models.UniqueIndex objects. These allow developers to declare indexes and constraints directly as model attributes, e.g.,
_some_check = models.Constraint("CHECK(a > 1)")
_user_idx  = models.Index("(active, user_id)")
_partner_idx = models.Index("(partner_id, res_model)")
_name_uniq   = models.UniqueIndex("(name) WHERE active IS TRUE")

The ORM now creates and synchronizes these indexes automatically, eliminating the need for virtual constraints and ensuring indexes are consistently defined and migrated across modules. By making index management declarative, Odoo encourages proper indexing for frequently searched fields.

  • Domain manipulation API: A new odoo.domain and Domain API was added, providing functional operations for building and combining domains. This paves the way for domain‑level optimizations by enabling the ORM to analyze and simplify domains before translation into SQL.
  • Domain optimization (equality vs. IN): Odoo 18.3 optimized domain handling by standardizing the equality operator. All conditions (field, ‘=’, value) are transformed into equivalent IN conditions (field, ‘in’, [value]). This unification means the ORM only has to implement the in and not in operators, simplifying code paths and enabling optimizations such as merging multiple IN conditions. The merge logic can detect contradictions – e.g., merging (‘x’,’in’,[1,2]) and (‘x’,’in’,[3]) yields a false domain – and generates a smaller query.
  • Optimized Field.search: After domain simplifications, the ORM invokes the Field.search method with only in/not in operators. Developers can implement search methods for positive operators and rely on the ORM to derive negative conditions. This reduces the number of search methods and improves consistency in query generation.
  • Multi‑valued field optimization: Similar merging logic was extended to Many2many/One2many fields, optimizing IN conditions for multi‑valued fields.
  • Index type enhancements: The index parameter on fields continues to support btree, btree_not_null and trigram indexes. Odoo 18 also introduced handling for unaccent in trigram indexes (when the unaccent extension is installed) to ensure that trigram indexes remain effective when accent‑insensitive search is enabled.
  • Name search improvement: Searching by display name now uses _search_display_name, aligning name‑search semantics with other fields and enabling reuse of the optimized search infrastructure.

Odoo 19.x

  • Using GROUPING SETS for pivot views: A major optimization in Odoo 19 is the use of PostgreSQL’s GROUPING SETS feature for pivot views. Previously, opening a pivot triggered multiple formatted_read_group calls, one for each combination of row/column groupings. For a pivot grouped by two row fields and one column field, this meant six separate queries, often leading to sequential scans. The GROUPING SETS optimization combines all of these requests into a single SQL query. The PR introducing this change adds _read_grouping_sets and formatted_read_grouping_sets methods to the ORM, reduces heavy RPC calls and dramatically lowers user and worker times. Example benchmarks show user time dropping from ~6 s to ~2 s (and worker time from 22 s to 2 s) when pivoting commissions data.
  • Domain optimization before search: The ORM now applies domain optimizations before running Fields.search. Equality conditions are rewritten as IN conditions and multiple IN/NOT IN conditions are merged, reducing domain trees and generating simpler SQL queries. This also makes the search methods simpler and reduces overhead when filtering records.
  • Deprecation of read_group: The traditional read_group method has been deprecated in favour of a new _read_group backend method and a public formatted_read_group API. The new implementation is better suited to batching queries (and forms the basis of the GROUPING SETS optimization).
  • Dynamic dates in domains: Domains now support dynamic date expressions, allowing the ORM to include relative date conditions (e.g., “today‑3 days”) directly in SQL rather than resolving them in Python.
  • Ongoing index and constraint improvements: The declarative Constraint/Index API introduced in 18.x remains available, encouraging developers to define composite and conditional indexes to support new features in 19.x.

Key takeaways

  • Reduced query count: The introduction of search_fetch()/fetch() and the use of GROUPING SETS for pivot views reduces the number of SQL statements, lowering latency and server load.
  • Smarter domain handling: Unifying equality and IN conditions and merging multiple IN clauses produces simpler WHERE clauses and allows early detection of contradictory domains, which PostgreSQL can optimize more effectively.
  • Better indexing: Declarative index definitions and support for specific index types (including trigram and not‑null btree indexes) give developers direct control over index selection, improving query plans for searches and aggregations.
  • Continuous refinement: The ORM continues to evolve, deprecating older methods (read_group, inselect, _flush_search) and renaming attributes to align with PostgreSQL capabilities, making query generation more predictable and efficient.

These changes collectively enable Odoo’s ORM to generate fewer, more efficient SQL queries, allowing PostgreSQL to leverage appropriate indexes and resulting in faster response times and lower server overhead. 

References

Previous Post
Understanding PostgreSQL WAL and optimizing it with a dedicated disk

Leave A Comment

Archives

Categories