Full-Text Search Optimization in Odoo: PostgreSQL Secrets

SELECT * FROM product_product
WHERE name LIKE '%blue%'
  OR name LIKE '%cotton%'
  OR name LIKE '%t-shirt%'
  OR description LIKE '%blue%'
  ...

-- Scans entire table sequentially
-- Time: 45 seconds
-- CPU: 100%
-- Result: Frustrated user closes tab

SELECT * FROM product_product
WHERE product_search_vector @@ to_tsquery('english', 'blue & cotton & t-shirt')
ORDER BY ts_rank(product_search_vector, to_tsquery(...)) DESC

-- Uses full-text index (GIN)
-- Time: 0.1 seconds
-- CPU: 2%
-- Result: User finds product instantly

-- Add GIN-indexed search vector column
ALTER TABLE product_product
ADD COLUMN search_vector tsvector;

-- Index for fast searching
CREATE INDEX idx_product_search
ON product_product USING GIN(search_vector);

-- Function to generate search vector
CREATE OR REPLACE FUNCTION product_search_vector_update() 
RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector := 
    setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.description, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(NEW.barcode, '')), 'C');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to auto-update on changes
CREATE TRIGGER product_search_vector_trigger
BEFORE INSERT OR UPDATE ON product_product
FOR EACH ROW
EXECUTE FUNCTION product_search_vector_update();

-- Update all existing products
UPDATE product_product 
SET search_vector = 
  setweight(to_tsvector('english', COALESCE(name, '')), 'A') ||
  setweight(to_tsvector('english', COALESCE(description, '')), 'B') ||
  setweight(to_tsvector('english', COALESCE(barcode, '')), 'C')
WHERE search_vector IS NULL;

-- Reindex
REINDEX INDEX idx_product_search;

from odoo import models, fields, api

class ProductProduct(models.Model):
    _inherit = 'product.product'
    
    # Search vector (automatically maintained by database trigger)
    search_vector = fields.Text(
        string='Search Vector',
        compute='_compute_search_vector',
        store=True,
        index=True,  # GIN index
    )
    
    @api.depends('name', 'description', 'barcode')
    def _compute_search_vector(self):
        """Generate search vector from product fields."""
        for product in self:
            # Combine fields with weights
            # A = name (most important)
            # B = description (medium)
            # C = barcode (low)
            product.search_vector = self._generate_tsvector(product)
    
    @staticmethod
    def _generate_tsvector(product):
        """Generate tsvector from product data."""
        # Note: In practice, use database trigger for efficiency
        # This is handled by PostgreSQL function
        return None  # Computed by database trigger

class ProductProduct(models.Model):
    _inherit = 'product.product'
    
    @api.model
    def search_products(self, query, limit=20):
        """Full-text search products by query."""
        if not query or len(query) < 2:
            return self.search([], limit=limit)
        
        # Parse query - join terms with & (AND)
        search_terms = ' & '.join(query.split())
        
        # Use raw SQL for performance
        sql = """
        SELECT id, name, list_price, image_1920,
               ts_rank(search_vector, query) as rank
        FROM product_product, to_tsquery('english', %s) as query
        WHERE search_vector @@ query
          AND active = true
        ORDER BY rank DESC, name
        LIMIT %s
        """
        self.env.cr.execute(sql, (search_terms, limit))
        results = self.env.cr.dictfetchall()
        
        # Convert to recordset
        product_ids = [r['id'] for r in results]
        return self.browse(product_ids)

-- English search vector
ALTER TABLE product_product
ADD COLUMN search_vector_en tsvector;

-- French search vector
ALTER TABLE product_product
ADD COLUMN search_vector_fr tsvector;

-- Spanish search vector
ALTER TABLE product_product
ADD COLUMN search_vector_es tsvector;

-- Index all
CREATE INDEX idx_product_search_en ON product_product USING GIN(search_vector_en);
CREATE INDEX idx_product_search_fr ON product_product USING GIN(search_vector_fr);
CREATE INDEX idx_product_search_es ON product_product USING GIN(search_vector_es);

def search_by_language(self, query, lang='en'):
    """Search in specific language."""
    lang_map = {
        'en': 'english',
        'fr': 'french',
        'es': 'spanish',
    }
    language = lang_map.get(lang, 'english')
    search_terms = ' & '.join(query.split())
    
    sql = f"""
    SELECT id, name, ts_rank(search_vector_{lang}, query) as rank
    FROM product_product, to_tsquery(%s, %s) as query
    WHERE search_vector_{lang} @@ query
    ORDER BY rank DESC
    LIMIT 20
    """
    self.env.cr.execute(sql, (language, search_terms))
    return self.env.cr.dictfetchall()

-- Install pg_trgm extension for fuzzy matching
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Create fuzzy index
CREATE INDEX idx_product_name_trgm
ON product_product USING GIN(name gin_trgm_ops);

def search_fuzzy(self, query):
    """Search with typo tolerance."""
    # Full-text search first
    fts_results = self.search_products(query)
    if fts_results:
        return fts_results
    
    # If no results, use fuzzy matching
    # Useful for typos: "bluw" matches "blue"
    sql = """
    SELECT id, name, similarity(name, %s) as similarity
    FROM product_product
    WHERE name %% %s  -- %% is "similarity operator"
      AND active = true
    ORDER BY similarity DESC
    LIMIT 20
    """
    self.env.cr.execute(sql, (query, query))
    return self.env.cr.dictfetchall()

class ProductSearch(models.Model):
    _name = 'product.search'
    _description = 'Product Full-Text Search'
    
    @api.model
    def search_products(self, query, filters=None, limit=20):
        """Comprehensive product search."""
        if not query or len(query) < 2:
            return []
        
        # Build search query
        search_terms = self._parse_query(query)
        
        # Build filter domain
        domain = [('active', '=', True)]
        if filters:
            if filters.get('category'):
                domain.append(('categ_id', '=', filters['category']))
            if filters.get('min_price'):
                domain.append(('list_price', '>=', filters['min_price']))
            if filters.get('max_price'):
                domain.append(('list_price', '<=', filters['max_price']))
        
        # Execute full-text search
        sql = """
        SELECT 
            p.id,
            p.name,
            p.categ_id,
            p.list_price,
            p.image_1920,
            ts_rank(p.search_vector,
                query,
                32  -- weight for A/B/C/D weights
            ) as relevance
        FROM product_product p,
             to_tsquery('english', %s) as query
        WHERE p.search_vector @@ query
          AND p.active = true
        """
        
        params = [search_terms]
        
        # Add filters to SQL
        if filters:
            if filters.get('category'):
                sql += " AND p.categ_id = %s"
                params.append(filters['category'])
            if filters.get('min_price'):
                sql += " AND p.list_price >= %s"
                params.append(filters['min_price'])
            if filters.get('max_price'):
                sql += " AND p.list_price <= %s"
                params.append(filters['max_price'])
        
        sql += """
        ORDER BY relevance DESC, p.name
        LIMIT %s
        """
        params.append(limit)
        
        self.env.cr.execute(sql, params)
        results = self.env.cr.dictfetchall()
        return results
    
    @staticmethod
    def _parse_query(query):
        """Parse search query into PostgreSQL tsquery format."""
        # Remove special characters
        clean = query.replace('"', '').replace("'", '')
        # Split into terms
        terms = clean.split()
        # Join with & (AND)
        search_query = ' & '.join(f"'{term}':*" for term in terms)
        return search_query

# In your web controller
@route('/shop/search', type='json', auth='public')
def search_products(self, query, **kwargs):
    """AJAX endpoint for product search."""
    results = self.env['product.search'].search_products(
        query,
        filters={
            'category': kwargs.get('category_id'),
            'min_price': kwargs.get('min_price'),
            'max_price': kwargs.get('max_price'),
        },
        limit=20
    )
    return {
        'results': [
            {
                'id': r['id'],
                'name': r['name'],
                'price': r['list_price'],
                'category': r['categ_id'][1] if r['categ_id'] else None,
                'relevance': r['relevance'],
            }
            for r in results
        ]
    }