Indexed Field in Odoo: A Practical Guide for Developers and Consultants

Introduction

If you have spent any time writing or reviewing Odoo field definitions, you have probably come across something like index=True on a field. It looks small, almost like an afterthought. But that single attribute can make a meaningful difference in how fast your Odoo instance responds when users search, filter, or navigate large datasets.

This guide walks through what an indexed field actually is in the context of the Odoo data model, how it affects your database, and when it makes sense to use it in your own Odoo development work. Whether you are building a custom module or reviewing an existing implementation, understanding this concept will help you make better decisions.

What Is an Indexed Field in Odoo

In the Odoo ORM, a field is declared indexed when its definition includes index=True. This tells Odoo to ask PostgreSQL to create a B-tree index on the corresponding database column when the module is installed or updated.

Here is a simple example from an Odoo field definition in Python:

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

    reference = fields.Char(string='Reference', index=True)
    state = fields.Selection([...], index=True)
    partner_id = fields.Many2one('res.partner', index=True)

From a user interface perspective, there is nothing visible about indexed fields. A user filling in a form or browsing a list view has no way of knowing whether a field is indexed or not. The indexing is entirely a database-level concern.

What it affects is speed. When a field is indexed, PostgreSQL can look up matching records much faster, especially on tables with thousands or millions of rows. Without an index, the database has to scan every row in the table to find the ones that match your search criteria. With an index, it navigates a sorted structure and finds the results directly.

Which Odoo Field Types Support index=True

Most scalar field types in the Odoo ORM support the index attribute:

• Char and Text fields
• Integer and Float fields
• Date and Datetime fields
• Selection fields
• Many2one fields (very commonly indexed)
• Boolean fields

Relational fields like One2many and Many2many do not have a direct column to index in the same way, so the index attribute is not relevant for them. Computed fields that are not stored also cannot be indexed, since they do not have a database column.

How the Field Works

When Odoo initializes or upgrades a module, it reads all field definitions and synchronizes the database schema. For any field with index=True, Odoo executes a SQL statement to create an index on that column in PostgreSQL.

By default, PostgreSQL creates a B-tree index, which is the standard index type and works well for equality comparisons (=), range queries (>, <, BETWEEN), and sorting. This covers the vast majority of what Odoo does when filtering records in list views or computing domain-based searches.

How It Interacts with the Odoo ORM

The Odoo ORM translates Python domain filters into SQL queries. When you write a domain like [('state', '=', 'sale')], the ORM generates something like WHERE state = 'sale'. If the state field has an index, PostgreSQL uses it to resolve that condition efficiently without scanning the entire table.

Many2one fields are a very common use case. In Odoo's data model, a field like partner_id on a sale order stores the integer ID of the related partner. When you open the list of orders filtered by customer, Odoo runs a query with a WHERE partner_id = X condition. With an index on partner_id, that lookup is fast even with hundreds of thousands of orders in the database.

Indexes and Write Performance

Indexes are not free. Every time a record is created, updated, or deleted, PostgreSQL needs to update all the indexes on that table as well. On tables with many indexes, write operations take slightly longer. For most Odoo use cases this trade-off is worth it, but it is worth understanding that indexing everything is not always the right answer.

The index='trigram' Option

In Odoo 16 and later versions, the index attribute accepts not just True but also the string value 'trigram'. This creates a GIN trigram index using the pg_trgm PostgreSQL extension, which supports fast pattern matching with ILIKE queries. This is particularly useful for text search on fields like product names or partner names where users often type partial strings in the search bar.

name = fields.Char(string='Product Name', index='trigram')

This is a more advanced option used in Odoo's own standard modules for fields that are frequently searched by partial text.

Business Use Cases

Indexed fields show up in many real Odoo workflows. Here are five practical examples where indexing a field makes a noticeable difference.

1. CRM: Filtering Leads by Salesperson

In CRM, sales managers regularly filter the pipeline by salesperson. The user_id field on crm.lead is indexed by default in Odoo, which makes filtering by salesperson fast even with thousands of leads. If you add a custom Many2one field pointing to a user or team, indexing it follows the same logic.

2. Sales: Searching Orders by Status

The state field on sale.order is indexed. This is what makes loading the list of confirmed orders, or filtering for orders waiting for delivery, fast for companies processing large volumes. Selection fields that are frequently used as filter criteria are good candidates for indexing.

3. Inventory: Tracking Moves by Product

Stock moves in Odoo can grow to very large volumes, especially in distribution or manufacturing businesses. The product_id field on stock.move is indexed, making it efficient to query all movements for a specific product. Without this index, product traceability reports would become painfully slow on busy warehouses.

4. Accounting: Filtering Journal Entries by Partner

Accountants frequently open the ledger for a specific customer or supplier. The partner_id on account.move.line is indexed to make those lookups efficient. In companies with years of accounting history, this index is what keeps the aged receivables report from timing out.

5. Custom Modules: Reference Fields for Traceability

When building custom modules, it is common to add reference fields that link records across models, for example a custom project code or an external document number. If users will search or filter by that reference field regularly, adding index=True is a straightforward way to keep those searches fast as data grows.

Creating or Customizing an Indexed Field

In Python (Custom Module Development)

Adding index=True to a field definition in a Python module is straightforward. You simply include it as a keyword argument when declaring the field:

from odoo import models, fields

class ProjectTask(models.Model):
    _inherit = 'project.task'

    x_external_ref = fields.Char(
        string='External Reference',
        index=True,
        help='Reference number from the external system'
    )

After adding this field to your module, run odoo-bin -u your_module_name or upgrade the module through the Apps menu. Odoo will detect the new field and create the corresponding index in the database.

You can also add an index to an existing field by inheriting it and overriding the definition, though this approach requires care to avoid unintended side effects on the original field behavior.

In Odoo Studio

Odoo Studio lets non-technical users create fields through the interface. However, Studio does not currently expose an option to toggle indexing on or off when creating fields. Fields created through Studio are stored in the database as manual fields and do not have index=True set by default.

If you need a Studio-created field to be indexed for performance reasons, the cleanest path is to convert the Studio customization into a proper Python module and add index=True in code. This falls under technical customization and is typically handled by an Odoo developer rather than a Studio user.

Adding an Index Directly in PostgreSQL

In some situations, such as when optimizing an existing production database without a module upgrade, a database administrator may add an index directly using SQL:

CREATE INDEX CONCURRENTLY idx_sale_order_partner_id
ON sale_order (partner_id);

Using CONCURRENTLY avoids locking the table during index creation, which is important in production environments. That said, this approach should be coordinated with the Odoo module definition to keep the Python code and database in sync. If the module is later upgraded, Odoo may or may not manage the index depending on what is in the field definition.

Best Practices

Index Fields That Appear in Search Domains

If a field is regularly used in domain filters, either in list view filters, automated actions, scheduled jobs, or computed field dependencies, it is a good candidate for indexing. The most common examples are Many2one relational fields, state fields, and reference or code fields.

Follow Odoo's Own Conventions

The best reference for when to use index=True is Odoo's own source code. Look at the standard field definitions in sale.order, account.move, or stock.move to see which fields Odoo's own developers chose to index. Those choices are based on real usage patterns and performance data from thousands of production databases.

Always Index Many2one Fields on High-Volume Models

For models that accumulate a large number of records over time (journal entries, stock moves, sales order lines), always index Many2one fields that are used for filtering. The cost of an extra index on a write-heavy table is almost always worth the read performance improvement.

Consider Trigram Indexing for Text Search Fields

On Odoo 16 and later, if users frequently search for records by typing partial strings in a Char field such as a product name, partner name, or document reference, consider using index='trigram' instead of the default B-tree index. Trigram indexes are specifically designed for ILIKE pattern matching.

Verify Indexes Are Actually Being Used

After adding an index, you can verify that PostgreSQL is using it by running an EXPLAIN ANALYZE on the query. If the query planner is still choosing a sequential scan, the table may be too small for the index to be beneficial, or the query conditions may not be compatible with the index type.

Document Your Indexing Decisions

When building custom modules, leave a brief comment explaining why a field is indexed. This helps future developers and consultants understand the intent and avoid removing the index by accident during a refactor.

Common Pitfalls

Indexing Every Field by Default

A common mistake when learning about indexing is to add index=True to every field just to be safe. This is counterproductive. Each index takes up storage space and adds overhead to every write operation. On tables that receive a high volume of inserts or updates, unnecessary indexes can noticeably slow down the system.

Indexing Fields on Small Tables

On tables with a few hundred rows, PostgreSQL's query planner often decides that a sequential scan is faster than using an index. Indexes start to provide real benefit as tables grow to thousands of records and beyond. Indexing small lookup tables or rarely populated custom models adds complexity without any practical benefit.

Forgetting to Upgrade the Module After Adding index=True

Simply adding index=True to a field definition in Python does not automatically create the index in the database. You need to upgrade the module using -u module_name or through the Odoo backend. Forgetting this step is a frequent source of confusion during development and can lead to performance issues in staging or production environments where the upgrade was skipped.

Expecting Indexes to Speed Up ILIKE Searches on Char Fields

A regular B-tree index with index=True does not help with ILIKE '%keyword%' queries where the wildcard is at the start of the pattern. PostgreSQL cannot use a B-tree index to evaluate a leading wildcard. If you need fast partial text search, use index='trigram' on Odoo 16+ or explore full-text search options.

Not Considering Computed Stored Fields

Stored computed fields (those with store=True) do have a database column and can be indexed. Developers sometimes overlook this and miss an opportunity to improve performance on fields that aggregate or derive values used heavily in filters. If a stored computed field appears in domain filters across reports or views, indexing it is worth considering.

Conclusion

The index=True attribute is a small detail in the Odoo field definition, but it has a real impact on database performance as your data grows. Used correctly, it keeps searches fast, list views responsive, and reports quick to generate. Used carelessly, it adds overhead without any benefit.

The key takeaway is simple: index fields that are regularly used in domain filters, especially Many2one fields on high-volume models. Follow the patterns set by Odoo's own standard modules. Avoid over-indexing small tables or fields that are never filtered on. And if you are on Odoo 16 or later, consider index='trigram' for text search fields where users type partial strings.

Getting the indexing strategy right from the start of a custom development project is much easier than diagnosing slow queries in production later.

Working on an Odoo Implementation?

At Dasolo, we help companies implement, customize, and optimize Odoo. Whether you are building custom modules, improving the performance of an existing instance, or planning a new Odoo project from scratch, we bring hands-on technical expertise to every engagement.

If you are dealing with slow queries, complex customizations, or need guidance on Odoo development best practices, we are happy to help. Reach out to the Dasolo team and let us know what you are working on.