Creating Effective Database Indexes in Odoo: Performance Boost

WHERE partner_id = 123 AND order_date >= '2025-01-01'

# In Odoo model
class SaleOrder(models.Model):
    _inherit = 'sale.order'
    
    # Frequently filtered
    partner_id = fields.Many2one('res.partner', index=True)
    
    # Frequently sorted
    order_date = fields.Date(index=True)
    
    # Frequently filtered
    state = fields.Selection(index=True)
    
    # NOT filtered or sorted → no index needed
    description = fields.Text()
    notes = fields.Html()

CREATE INDEX idx_sale_order_partner_id ON sale_order(partner_id);
CREATE INDEX idx_sale_order_date ON sale_order(order_date);
CREATE INDEX idx_sale_order_state ON sale_order(state);

-- Query: WHERE partner_id = 123 AND order_date >= '2025-01-01'
-- Index: (partner_id, order_date)
CREATE INDEX idx_sale_order_partner_date
ON sale_order(partner_id, order_date);

-- Column order CRITICAL:
-- Good:   (partner_id, order_date) - filters both columns
-- Bad:    (order_date, partner_id) - only filters order_date efficiently

-- Query: SELECT name, email FROM res_partner WHERE country_id = 1
-- Index includes all needed columns → "index-only scan"
CREATE INDEX idx_partner_country_covering
ON res_partner(country_id) INCLUDE (name, email);

-- Database reads index only, not main table
-- 2-3x faster

-- Only index active records (saves 50% of index size)
CREATE INDEX idx_sale_order_active
ON sale_order(partner_id)
WHERE state IN ('draft', 'sent', 'sale');

-- Queries: WHERE state IN ('draft', 'sent', 'sale') AND partner_id = 123
-- Uses partial index (faster, smaller)

-- Queries: WHERE state = 'cancel'
-- Doesn't use index (only 1% of data anyway)

-- Enable logging of slow queries
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- Log queries > 1 second
SELECT pg_reload_conf();

-- Check logs for slow queries
-- Find top 10 slowest

-- Query: Users filter orders by customer and date range
EXPLAIN ANALYZE
SELECT * FROM sale_order
WHERE partner_id = 123
  AND order_date >= '2025-01-01'
  AND order_date <= '2025-12-31';

-- Output before index:
-- Seq Scan on sale_order (cost=0.00..500000.00 rows=10000000 width=200)
--   Filter: (partner_id = 123 AND order_date >= '2025-01-01' AND order_date <= '2025-12-31')
--   Actual Rows: 50
-- Planning Time: 0.2ms
-- Execution Time: 45000.0ms  ← 45 SECONDS!

-- Create composite index
CREATE INDEX idx_sale_order_partner_date
ON sale_order(partner_id, order_date);

-- Re-run EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT * FROM sale_order
WHERE partner_id = 123
  AND order_date >= '2025-01-01'
  AND order_date <= '2025-12-31';

-- Output after index:
-- Index Scan using idx_sale_order_partner_date on sale_order (cost=0.42..1234.56 rows=50 width=200)
--   Index Cond: (partner_id = 123 AND order_date >= '2025-01-01' AND order_date <= '2025-12-31')
--   Actual Rows: 50
-- Planning Time: 0.1ms
-- Execution Time: 2.1ms  ← 2 MILLISECONDS! 21,000x FASTER!

-- Sale Orders
CREATE INDEX idx_sale_order_partner ON sale_order(partner_id);
CREATE INDEX idx_sale_order_state ON sale_order(state);
CREATE INDEX idx_sale_order_date ON sale_order(order_date DESC);
CREATE INDEX idx_sale_order_partner_state_date
  ON sale_order(partner_id, state, order_date DESC);

-- Sale Order Lines
CREATE INDEX idx_sale_order_line_order ON sale_order_line(order_id);
CREATE INDEX idx_sale_order_line_product ON sale_order_line(product_id);

-- Products
CREATE INDEX idx_product_categ ON product_product(categ_id);
CREATE INDEX idx_product_code ON product_product(default_code);

-- Customers
CREATE INDEX idx_partner_country ON res_partner(country_id);
CREATE INDEX idx_partner_email ON res_partner(email);

-- Invoices
CREATE INDEX idx_account_move_partner ON account_move(partner_id);
CREATE INDEX idx_account_move_state ON account_move(state);

-- Stock
CREATE INDEX idx_stock_move_product ON stock_move(product_id);
CREATE INDEX idx_stock_move_state ON stock_move(state);

class SaleOrder(models.Model):
    _inherit = 'sale.order'
    
    # This creates index on partner_id column (critical!)
    partner_id = fields.Many2one(
        'res.partner',
        string='Customer',
        index=True,  # ← Creates database index
        required=True
    )
    
    # Relationship fields ALWAYS benefit from indexing
    user_id = fields.Many2one(
        'res.users',
        string='Salesman',
        index=True
    )
    
    # Company field (filtered in multi-company setups)
    company_id = fields.Many2one(
        'res.company',
        index=True
    )

-- Indexes with 0 scans = not being used
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch,
       pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE 'pg_toast%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Remove unused indexes (saves space, speeds writes)
-- DROP INDEX index_name;

-- Indexes with high scan counts = being used effectively
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read,
       ROUND(100 * idx_tup_read / NULLIF(idx_scan, 0), 2) as avg_rows_returned
FROM pg_stat_user_indexes
WHERE idx_scan > 0
ORDER BY idx_scan DESC
LIMIT 20;

-- Queries doing full table scans (slow)
SELECT query, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_statements
WHERE seq_scan > 100
  AND query NOT LIKE '%pg_stat%'
ORDER BY seq_tup_read DESC
LIMIT 10;

-- These queries need indexes!