Fix Slow Odoo Queries: search_read, List Views, and Database Performance

The Problem

Odoo was snappy when you started, but after a year of data, list views take 10 seconds to load, reports timeout, and the whole system feels sluggish. The culprit is almost always slow database queries — and the fix is usually straightforward once you identify the problem.

Common Symptoms

• List views take 5-30 seconds to load
• Search results slow to appear
• Reports timeout with 504 Gateway Timeout
• High CPU on the database server
• Odoo log shows queries taking 1000ms+
• Browser shows spinning wheel for minutes

Step 1: Identify the Slow Queries

Enable Odoo Query Logging

# In odoo.conf:
log_level = debug_sql

# Or command line:
./odoo-bin --log-handler=odoo.sql_db:DEBUG

# This logs ALL SQL queries with execution time.
# Warning: very verbose, use only for debugging.

Use PostgreSQL Slow Query Log

# In postgresql.conf:
log_min_duration_statement = 500  # log queries taking > 500ms
log_statement = 'none'  # don't log all queries, just slow ones

# Reload: sudo systemctl reload postgresql

Check Active Queries

# Find currently running slow queries:
SELECT pid, now() - query_start AS duration, 
       state, query 
FROM pg_stat_activity 
WHERE state = 'active' 
  AND now() - query_start > interval '1 second'
ORDER BY query_start;

Common Slow Query Patterns and Fixes

1. Missing Indexes on Filtered Fields

The most impactful fix. If you filter or sort by a field that does not have a database index, PostgreSQL does a full table scan.

# Identify missing indexes:
EXPLAIN ANALYZE SELECT id FROM sale_order 
WHERE state = 'sale' AND date_order > '2025-01-01';

# Look for "Seq Scan" in the output — that means no index.
# "Index Scan" or "Bitmap Index Scan" = good.

Fix:

# Add index in Odoo model:
state = fields.Selection([...], index=True)  # adds B-tree index
date_order = fields.Datetime(index=True)

# Or add directly in PostgreSQL:
CREATE INDEX CONCURRENTLY idx_sale_order_state 
  ON sale_order (state);
CREATE INDEX CONCURRENTLY idx_sale_order_date 
  ON sale_order (date_order);

2. name_search on Large Tables

Many2one fields use name_search which does ILIKE '%term%' — the slowest possible text search pattern.

Fix: Add a trigram index for ILIKE performance:

-- Enable pg_trgm extension:
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Add trigram index:
CREATE INDEX CONCURRENTLY idx_partner_name_trgm 
  ON res_partner USING gin (name gin_trgm_ops);

3. Computed Fields Without Store

Non-stored computed fields are computed for every record on every request. If a list view shows 80 records with 5 computed fields, that is 400 computations per page load.

Fix: Add store=True to computed fields used in list views or filters:

# Slow: computed on every access
margin = fields.Float(compute='_compute_margin')

# Fast: computed once, stored in database, indexed for filtering
margin = fields.Float(compute='_compute_margin', store=True)

4. Large One2many Fields Loaded in Views

Loading a form view that includes a One2many with thousands of child records is slow.

Fix:

• Add pagination to the One2many: <field name="line_ids" limit="20"/>
• Use a separate list view action instead of embedding
• Add default filters to reduce loaded records

5. Unoptimized Domain Filters

Complex domains with nested OR conditions and cross-model filters generate slow SQL.

# Slow: cross-model filter with OR
domain = ['|', 
  ('partner_id.country_id.name', '=', 'US'),
  ('partner_id.state_id.name', '=', 'California')
]

# Faster: pre-fetch partner IDs
us_partners = self.env['res.partner'].search([
    '|', ('country_id.name', '=', 'US'), 
    ('state_id.name', '=', 'California')
]).ids
domain = [('partner_id', 'in', us_partners)]

6. count() on Large Tables

Odoo's list view calls search_count() to show the total record count. On tables with millions of rows, this is slow.

Fix:

# In custom models, override search_count for performance:
@api.model
def search_count(self, domain, limit=None):
    # Use estimate for very large counts
    if not domain:
        self.env.cr.execute(
            "SELECT reltuples FROM pg_class WHERE relname = %s",
            [self._table]
        )
        return int(self.env.cr.fetchone()[0])
    return super().search_count(domain, limit=limit)

PostgreSQL Tuning for Odoo

# Key postgresql.conf settings for Odoo:
shared_buffers = 2GB           # 25% of RAM
effective_cache_size = 6GB     # 75% of RAM
work_mem = 64MB                # per query sort/hash memory
maintenance_work_mem = 512MB   # for VACUUM, CREATE INDEX
random_page_cost = 1.1         # for SSD storage (default 4.0)
effective_io_concurrency = 200 # for SSD
max_worker_processes = 8
max_parallel_workers_per_gather = 4

Regular Maintenance

# Vacuum and analyze (should run automatically, but verify):
VACUUM ANALYZE;

# Check table bloat:
SELECT schemaname, tablename, 
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
WHERE schemaname = 'public' 
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC 
LIMIT 20;

# Reindex bloated indexes:
REINDEX TABLE CONCURRENTLY sale_order;