Odoo search_count and Search Performance: Efficient Querying Guide

search_count vs search: When to Use Which

A common performance mistake is using search() when you only need a count:

# SLOW: loads all matching records into memory
count = len(self.env['sale.order'].search([('state', '=', 'sale')]))

# FAST: SELECT COUNT(*) — no records loaded
count = self.env['sale.order'].search_count([('state', '=', 'sale')])

search_count generates a SELECT COUNT(*) query. It never instantiates record objects, never triggers computed fields, and uses minimal memory. Always use it when you only need the number of matching records.

search() Options That Matter

records = self.env['sale.order'].search(
    domain=[('state', '=', 'sale')],
    offset=0,       # skip first N records (pagination)
    limit=20,       # max records to return
    order='date_order desc',  # SQL ORDER BY
)

Key performance rules:

• Always use limit when displaying data — never load unbounded result sets
• Use order on indexed fields when possible
• Combine offset + limit for pagination, but be aware of deep pagination costs

Domain Optimization

The domain expression directly translates to SQL WHERE clauses. Poor domains generate slow queries:

# BAD: uses child_of which may traverse a deep hierarchy
[('category_id', 'child_of', parent_id)]

# BAD: negative operators prevent index usage
[('name', 'not ilike', 'test')]

# GOOD: direct equality uses index
[('state', '=', 'sale')]

# GOOD: range on indexed date field
[('date_order', '>=', '2026-01-01'), ('date_order', '<', '2026-04-01')]

# GOOD: IN for multiple values
[('state', 'in', ['sale', 'done'])]

Domain Operator Performance Ranking

Indexing Strategy

Odoo automatically creates indexes for:

• Fields with index=True
• Many2one fields (foreign key index)
• The id primary key

Add indexes to fields used frequently in search domains:

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

    # Add index for performance
    custom_reference = fields.Char(index=True)
    priority_level = fields.Selection([...], index=True)

For composite queries, consider database-level indexes:

def init(self):
    self.env.cr.execute("""
        CREATE INDEX IF NOT EXISTS
            sale_order_state_date_idx
        ON sale_order (state, date_order DESC)
    """)

Exists Check Pattern

When you only need to know if at least one record matches:

# SLOW: counts all matching records
if self.env['sale.order'].search_count([('state', '=', 'draft')]) > 0:
    ...

# FAST: stops at first match
if self.env['sale.order'].search([('state', '=', 'draft')], limit=1):
    ...

Using search(limit=1) returns immediately after finding the first match. search_count must scan all matching records to return the total count.

Pagination Best Practices

def _get_paginated_orders(self, page=1, per_page=20):
    domain = [('state', '=', 'sale')]
    total = self.env['sale.order'].search_count(domain)
    orders = self.env['sale.order'].search(
        domain,
        offset=(page - 1) * per_page,
        limit=per_page,
        order='date_order desc'
    )
    return {
        'items': orders.read(['name', 'amount_total', 'date_order']),
        'total': total,
        'page': page,
        'pages': (total + per_page - 1) // per_page
    }

For deep pagination (page 500+), OFFSET becomes slow because PostgreSQL must skip all preceding rows. Consider keyset pagination for very large datasets:

# Keyset pagination: use last seen ID instead of offset
def _get_next_page(self, last_id=0, limit=20):
    return self.env['sale.order'].search(
        [('state', '=', 'sale'), ('id', '>', last_id)],
        limit=limit,
        order='id asc'
    )

name_search Optimization

The name_search method powers Many2one dropdown searches. It can be slow on large tables:

class Partner(models.Model):
    _inherit = 'res.partner'

    # Override _name_search for custom search logic
    def _name_search(self, name='', domain=None, operator='ilike', limit=100, order=None):
        domain = domain or []
        if name:
            # Search by reference first (indexed, fast)
            domain_ref = [('ref', '=', name)] + domain
            ids = self._search(domain_ref, limit=limit, order=order)
            if ids:
                return ids
        return super()._name_search(name, domain, operator, limit, order)

Avoiding N+1 Queries

N+1 is the most common Odoo performance problem. It occurs when you loop over records and access related fields one by one:

# N+1 PROBLEM: each partner_id access triggers a query
for order in orders:
    print(order.partner_id.name)  # 1 query per iteration

# FIX: pre-read all related data
order_data = orders.read(['partner_id'])
# Or use mapped to trigger prefetch
orders.mapped('partner_id.name')

Profiling Slow Searches

Enable query logging to identify slow searches:

# In odoo.conf
log_level = debug_sql

# Or programmatically
import logging
logging.getLogger('odoo.sql_db').setLevel(logging.DEBUG)

Look for queries with high row counts, missing indexes, or sequential scans. Use EXPLAIN ANALYZE on the generated SQL to understand the query plan.