Experiment Search Performance Degradation with Complex Filters

Search queries with filter_string containing >3 conditions taking >10 seconds, API timeouts on complex searches, database CPU spikes during search operations, query plans showing nested loops and sequential scans for tag/param/metric filters

1. INVESTIGATE: Analyze slow search queries using database query logs. Run EXPLAIN ANALYZE on generated SQL to identify bottlenecks. Check if searches are using indexed columns or requiring full table scans. 2. DIAGNOSE: Identify which filter conditions are causing slowness (tags, params, metrics, or combinations). Determine if issue is missing indexes, inefficient join strategy, or excessive result set size. 3. REMEDIATE: Create specialized indexes for common search patterns: 'CREATE INDEX CONCURRENTLY idx_tags_key_value ON tags(key, value)' for tag-based filters. 'CREATE INDEX CONCURRENTLY idx_params_key_value ON params(key, value)' for parameter filters. Use materialized views for common complex searches (e.g., production models with specific tag combinations). Implement search query caching for repeated searches. Break complex filters into multiple simpler queries and combine results in application layer. Limit search result size using max_results parameter. Consider using dedicated search index (Elasticsearch) for complex queries if search is critical to operations. 4. PREVENT: Document search performance best practices: prefer simple filters over complex expressions, use indexed columns (experiment_id, status, lifecycle_stage) when possible. Implement query performance monitoring with logging of slow searches. Educate users on efficient search patterns. Consider implementing search query complexity limits.