Blog

-- Traditional PostgreSQL multi-field query challenges - limited compound index effectiveness

-- Basic product catalog with traditional indexing approach
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    sku VARCHAR(100) UNIQUE NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    category VARCHAR(100) NOT NULL,
    subcategory VARCHAR(100),
    brand VARCHAR(100) NOT NULL,

    -- Price and inventory fields
    price DECIMAL(10,2) NOT NULL,
    sale_price DECIMAL(10,2),
    cost DECIMAL(10,2),
    stock_quantity INTEGER NOT NULL DEFAULT 0,
    reserved_quantity INTEGER NOT NULL DEFAULT 0,

    -- Product attributes
    weight_kg DECIMAL(8,3),
    dimensions_length_cm DECIMAL(8,2),
    dimensions_width_cm DECIMAL(8,2),  
    dimensions_height_cm DECIMAL(8,2),
    color VARCHAR(50),
    size VARCHAR(50),

    -- Status and lifecycle
    status VARCHAR(50) DEFAULT 'active',
    availability VARCHAR(50) DEFAULT 'in_stock',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    published_at TIMESTAMP,
    discontinued_at TIMESTAMP,

    -- SEO and marketing
    seo_title VARCHAR(200),
    seo_description TEXT,
    marketing_tags TEXT[],
    featured BOOLEAN DEFAULT false,

    -- Supplier information
    supplier_id INTEGER,
    supplier_sku VARCHAR(100),
    lead_time_days INTEGER,
    minimum_order_quantity INTEGER DEFAULT 1,

    -- Performance tracking
    view_count INTEGER DEFAULT 0,
    purchase_count INTEGER DEFAULT 0,
    rating_average DECIMAL(3,2) DEFAULT 0,
    rating_count INTEGER DEFAULT 0
);

-- Traditional indexing approach with limited compound effectiveness
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_products_brand ON products(brand);  
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_products_status ON products(status);
CREATE INDEX idx_products_created_at ON products(created_at);
CREATE INDEX idx_products_stock ON products(stock_quantity);

-- Attempt at compound indexes with limited optimization
CREATE INDEX idx_products_category_brand ON products(category, brand);
CREATE INDEX idx_products_price_range ON products(price, status);
CREATE INDEX idx_products_inventory ON products(status, stock_quantity, availability);

-- Complex multi-field query that struggles with traditional indexing
WITH product_search_filters AS (
  SELECT 
    p.*,

    -- Calculate derived fields for filtering
    (p.stock_quantity - p.reserved_quantity) as available_quantity,
    CASE 
      WHEN p.sale_price IS NOT NULL AND p.sale_price < p.price THEN p.sale_price
      ELSE p.price
    END as effective_price,

    -- Performance scoring
    (p.rating_average * p.rating_count + p.view_count * 0.1 + p.purchase_count * 2) as performance_score,

    -- Age calculation
    EXTRACT(DAYS FROM CURRENT_TIMESTAMP - p.created_at) as days_since_created,

    -- Availability status
    CASE 
      WHEN p.stock_quantity <= 0 THEN 'out_of_stock'
      WHEN p.stock_quantity <= 5 THEN 'low_stock'
      ELSE 'in_stock'
    END as stock_status

  FROM products p
  WHERE 
    -- Multiple filtering conditions that don't align with index structure
    p.status = 'active'
    AND p.category IN ('Electronics', 'Computers', 'Mobile')
    AND p.brand IN ('Apple', 'Samsung', 'Sony', 'Microsoft')
    AND p.price BETWEEN 100.00 AND 2000.00
    AND p.stock_quantity > 0
    AND p.created_at >= CURRENT_DATE - INTERVAL '2 years'
    AND (p.featured = true OR p.rating_average >= 4.0)
    AND p.availability = 'in_stock'
),

price_range_analysis AS (
  -- Complex price range queries that can't utilize compound indexes effectively
  SELECT 
    psf.*,

    -- Price tier classification
    CASE 
      WHEN effective_price < 200 THEN 'budget'
      WHEN effective_price < 500 THEN 'mid_range'  
      WHEN effective_price < 1000 THEN 'premium'
      ELSE 'luxury'
    END as price_tier,

    -- Discount calculations
    CASE 
      WHEN psf.sale_price IS NOT NULL THEN
        ROUND(((psf.price - psf.sale_price) / psf.price * 100), 1)
      ELSE 0
    END as discount_percentage,

    -- Competitive analysis (requires additional queries)
    (
      SELECT AVG(price) 
      FROM products p2 
      WHERE p2.category = psf.category 
      AND p2.brand = psf.brand
      AND p2.status = 'active'
    ) as category_brand_avg_price,

    -- Inventory velocity
    CASE 
      WHEN psf.purchase_count > 0 AND psf.days_since_created > 0 THEN
        ROUND(psf.purchase_count::DECIMAL / (psf.days_since_created / 30), 2)
      ELSE 0
    END as monthly_sales_velocity

  FROM product_search_filters psf
),

sorting_and_ranking AS (
  -- Complex sorting requirements that bypass index usage
  SELECT 
    pra.*,

    -- Multi-criteria ranking
    (
      -- Price competitiveness (lower is better)
      CASE 
        WHEN pra.category_brand_avg_price > 0 THEN
          (pra.category_brand_avg_price - pra.effective_price) / pra.category_brand_avg_price * 20
        ELSE 0
      END +

      -- Rating score
      pra.rating_average * 10 +

      -- Stock availability score
      CASE 
        WHEN pra.available_quantity > 10 THEN 20
        WHEN pra.available_quantity > 5 THEN 15
        WHEN pra.available_quantity > 0 THEN 10
        ELSE 0
      END +

      -- Recency bonus
      CASE 
        WHEN pra.days_since_created <= 30 THEN 15
        WHEN pra.days_since_created <= 90 THEN 10
        WHEN pra.days_since_created <= 180 THEN 5
        ELSE 0
      END +

      -- Featured bonus
      CASE WHEN pra.featured THEN 25 ELSE 0 END

    ) as composite_ranking_score

  FROM price_range_analysis pra
)

SELECT 
  sar.product_id,
  sar.sku,
  sar.product_name,
  sar.category,
  sar.subcategory,
  sar.brand,
  sar.effective_price,
  sar.price_tier,
  sar.discount_percentage,
  sar.available_quantity,
  sar.stock_status,
  sar.rating_average,
  sar.rating_count,
  sar.performance_score,
  sar.monthly_sales_velocity,
  sar.composite_ranking_score,

  -- Additional computed fields
  CASE 
    WHEN sar.discount_percentage > 20 THEN 'great_deal'
    WHEN sar.discount_percentage > 10 THEN 'good_deal'
    ELSE 'regular_price'
  END as deal_status,

  -- Recommendation priority
  CASE 
    WHEN sar.composite_ranking_score > 80 THEN 'highly_recommended'
    WHEN sar.composite_ranking_score > 60 THEN 'recommended'  
    WHEN sar.composite_ranking_score > 40 THEN 'consider'
    ELSE 'standard'
  END as recommendation_level

FROM sorting_and_ranking sar

-- Complex ordering that can't utilize compound indexes effectively
ORDER BY 
  CASE 
    WHEN sar.featured = true THEN 0 
    ELSE 1 
  END,  -- Featured first
  sar.composite_ranking_score DESC,  -- Then by composite score
  sar.rating_average DESC,  -- Then by rating
  sar.available_quantity DESC,  -- Then by stock availability
  sar.created_at DESC  -- Finally by recency

LIMIT 50 OFFSET 0;

-- Performance analysis of the traditional approach
EXPLAIN (ANALYZE, BUFFERS) 
WITH product_search_filters AS (
  SELECT p.*
  FROM products p
  WHERE 
    p.status = 'active'
    AND p.category = 'Electronics'
    AND p.brand IN ('Apple', 'Samsung')
    AND p.price BETWEEN 200.00 AND 1000.00
    AND p.stock_quantity > 0
    AND p.rating_average >= 4.0
)
SELECT * FROM product_search_filters 
ORDER BY price, rating_average DESC, created_at DESC
LIMIT 10;

-- Problems with traditional multi-field indexing:
-- 1. Index selection conflicts - query planner struggles to choose optimal index
-- 2. Poor compound index utilization due to filter order mismatches
-- 3. Expensive sorting operations that can't use index ordering
-- 4. Multiple index scans and expensive merge operations
-- 5. Large intermediate result sets that require post-filtering
-- 6. Inability to optimize across different query patterns efficiently
-- 7. Complex explain plans with sequential scans and sorts
-- 8. High I/O overhead from multiple index lookups
-- 9. Limited flexibility for dynamic filtering combinations
-- 10. Poor performance scaling with data volume growth

-- Attempt to create better compound indexes (still limited)
CREATE INDEX idx_products_comprehensive_search ON products(
  status, category, brand, price, stock_quantity, rating_average, created_at
);

-- But this creates issues:
-- 1. Index becomes too wide and expensive to maintain
-- 2. Only effective for queries that match the exact field order
-- 3. Partial index usage for queries with different filter combinations  
-- 4. High storage overhead and slow write performance
-- 5. Still can't optimize for different sort orders efficiently

-- Complex aggregation query with traditional limitations
WITH category_brand_performance AS (
  SELECT 
    p.category,
    p.brand,

    -- Aggregated metrics that require full collection scans
    COUNT(*) as total_products,
    AVG(p.price) as avg_price,
    AVG(p.rating_average) as avg_rating,
    SUM(p.stock_quantity) as total_stock,
    SUM(p.purchase_count) as total_sales,

    -- Percentile calculations (expensive without proper indexing)
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY p.price) as price_p25,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY p.price) as price_median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY p.price) as price_p75,

    -- Time-based analysis
    COUNT(*) FILTER (WHERE p.created_at >= CURRENT_DATE - INTERVAL '30 days') as new_products_30d,
    COUNT(*) FILTER (WHERE p.featured = true) as featured_count,
    COUNT(*) FILTER (WHERE p.stock_quantity = 0) as out_of_stock_count

  FROM products p
  WHERE p.status = 'active'
  GROUP BY p.category, p.brand
  HAVING COUNT(*) >= 5  -- Only brands with significant presence
),

performance_ranking AS (
  SELECT 
    cbp.*,

    -- Performance scoring
    (
      (cbp.avg_rating * 20) +
      (CASE WHEN cbp.avg_price > 0 THEN (cbp.total_sales * 1000.0 / cbp.total_products) ELSE 0 END) +
      ((cbp.featured_count * 100.0 / cbp.total_products)) +
      (100 - (cbp.out_of_stock_count * 100.0 / cbp.total_products))
    ) as performance_index,

    -- Market position analysis
    RANK() OVER (
      PARTITION BY cbp.category 
      ORDER BY cbp.total_sales DESC, cbp.avg_rating DESC
    ) as category_sales_rank,

    RANK() OVER (
      PARTITION BY cbp.category 
      ORDER BY cbp.avg_price DESC
    ) as category_price_rank

  FROM category_brand_performance cbp
)

SELECT 
  pr.category,
  pr.brand,
  pr.total_products,
  ROUND(pr.avg_price, 2) as avg_price,
  ROUND(pr.avg_rating, 2) as avg_rating,
  pr.total_stock,
  pr.total_sales,
  ROUND(pr.price_median, 2) as median_price,
  pr.new_products_30d,
  pr.featured_count,
  ROUND(pr.performance_index, 1) as performance_score,
  pr.category_sales_rank,
  pr.category_price_rank,

  -- Market analysis
  CASE 
    WHEN pr.category_price_rank <= 3 THEN 'premium_positioning'
    WHEN pr.category_price_rank <= pr.total_products * 0.5 THEN 'mid_market'
    ELSE 'value_positioning'
  END as market_position,

  CASE 
    WHEN pr.performance_index > 200 THEN 'top_performer'
    WHEN pr.performance_index > 150 THEN 'strong_performer'
    WHEN pr.performance_index > 100 THEN 'average_performer'
    ELSE 'underperformer'
  END as performance_tier

FROM performance_ranking pr
ORDER BY pr.category, pr.performance_index DESC;

-- Traditional limitations in complex analytics:
-- 1. Multiple full table scans required for different aggregations
-- 2. Expensive sorting and ranking operations
-- 3. No ability to use covering indexes for complex calculations
-- 4. High CPU and memory usage for large result set processing
-- 5. Poor query plan reusability across similar analytical queries
-- 6. Limited optimization for mixed OLTP and OLAP workloads
-- 7. Complex join operations that can't utilize compound indexes effectively
-- 8. Expensive window function calculations without proper index support
-- 9. Limited ability to optimize across time-series and categorical dimensions
-- 10. Inefficient handling of sparse data and optional field queries

// MongoDB Advanced Compound Indexing - high-performance multi-field query optimization
const { MongoClient, ObjectId } = require('mongodb');

const client = new MongoClient('mongodb://localhost:27017');
const db = client.db('advanced_compound_indexing');

// Comprehensive MongoDB Compound Index Manager
class AdvancedCompoundIndexManager {
  constructor(db, config = {}) {
    this.db = db;
    this.collections = {
      products: db.collection('products'),
      categories: db.collection('categories'),
      brands: db.collection('brands'),
      indexAnalytics: db.collection('index_analytics'),
      queryPerformance: db.collection('query_performance'),
      indexRecommendations: db.collection('index_recommendations')
    };

    // Advanced compound indexing configuration
    this.config = {
      enableIntelligentIndexing: config.enableIntelligentIndexing !== false,
      enableQueryAnalysis: config.enableQueryAnalysis !== false,
      enablePerformanceTracking: config.enablePerformanceTracking !== false,
      enableIndexOptimization: config.enableIndexOptimization !== false,

      // Index management settings
      maxCompoundIndexFields: config.maxCompoundIndexFields || 8,
      backgroundIndexCreation: config.backgroundIndexCreation !== false,
      sparseIndexOptimization: config.sparseIndexOptimization !== false,
      partialIndexSupport: config.partialIndexSupport !== false,

      // Performance thresholds
      slowQueryThreshold: config.slowQueryThreshold || 1000, // milliseconds
      indexEfficiencyThreshold: config.indexEfficiencyThreshold || 0.8,
      cardinalityAnalysisThreshold: config.cardinalityAnalysisThreshold || 1000,

      // Optimization strategies
      enableESRRule: config.enableESRRule !== false, // Equality, Sort, Range
      enableQueryPlanCaching: config.enableQueryPlanCaching !== false,
      enableIndexIntersection: config.enableIndexIntersection !== false,
      enableCoveringIndexes: config.enableCoveringIndexes !== false,

      // Monitoring and maintenance
      indexMaintenanceInterval: config.indexMaintenanceInterval || 86400000, // 24 hours
      performanceAnalysisInterval: config.performanceAnalysisInterval || 3600000, // 1 hour
      enableAutomaticOptimization: config.enableAutomaticOptimization || false
    };

    // Performance tracking
    this.performanceMetrics = {
      queryPatterns: new Map(),
      indexUsage: new Map(),
      slowQueries: [],
      optimizationHistory: []
    };

    // Index strategy patterns
    this.indexStrategies = {
      searchOptimized: ['status', 'category', 'brand', 'price', 'rating_average'],
      analyticsOptimized: ['created_at', 'category', 'brand', 'total_sales'],
      inventoryOptimized: ['status', 'availability', 'stock_quantity', 'updated_at'],
      sortingOptimized: ['featured', 'price', 'rating_average', 'created_at']
    };

    this.initializeCompoundIndexing();
  }

  async initializeCompoundIndexing() {
    console.log('Initializing advanced compound indexing system...');

    try {
      // Create comprehensive compound indexes
      await this.createAdvancedCompoundIndexes();

      // Setup query pattern analysis
      if (this.config.enableQueryAnalysis) {
        await this.setupQueryPatternAnalysis();
      }

      // Initialize performance monitoring
      if (this.config.enablePerformanceTracking) {
        await this.setupPerformanceMonitoring();
      }

      // Enable automatic optimization if configured
      if (this.config.enableAutomaticOptimization) {
        await this.setupAutomaticOptimization();
      }

      console.log('Advanced compound indexing system initialized successfully');

    } catch (error) {
      console.error('Error initializing compound indexing:', error);
      throw error;
    }
  }

  async createAdvancedCompoundIndexes() {
    console.log('Creating advanced compound indexes with intelligent optimization...');

    try {
      const products = this.collections.products;

      // Core Search and Filtering Compound Index (ESR Pattern: Equality, Sort, Range)
      await products.createIndex({
        status: 1,              // Equality filter (most selective first)
        category: 1,            // Equality filter  
        brand: 1,               // Equality filter
        featured: -1,           // Sort field (featured items first)
        price: 1,               // Range filter
        rating_average: -1,     // Sort field
        created_at: -1          // Final sort field
      }, {
        name: 'idx_products_search_optimized',
        background: this.config.backgroundIndexCreation,
        sparse: this.config.sparseIndexOptimization
      });

      // E-commerce Product Catalog Compound Index
      await products.createIndex({
        status: 1,
        availability: 1,
        category: 1,
        subcategory: 1,
        price: 1,
        stock_quantity: -1,
        rating_average: -1
      }, {
        name: 'idx_products_catalog_comprehensive',
        background: this.config.backgroundIndexCreation,
        partialFilterExpression: {
          status: 'active',
          stock_quantity: { $gt: 0 }
        }
      });

      // Advanced Analytics and Reporting Index
      await products.createIndex({
        created_at: -1,         // Time-based queries (most recent first)
        category: 1,            // Grouping dimension
        brand: 1,               // Grouping dimension  
        purchase_count: -1,     // Performance metric
        rating_average: -1,     // Quality metric
        price: 1                // Financial metric
      }, {
        name: 'idx_products_analytics_optimized',
        background: this.config.backgroundIndexCreation
      });

      // Inventory Management Compound Index
      await products.createIndex({
        status: 1,
        availability: 1,
        stock_quantity: 1,
        reserved_quantity: 1,
        supplier_id: 1,
        updated_at: -1
      }, {
        name: 'idx_products_inventory_management',
        background: this.config.backgroundIndexCreation,
        sparse: true
      });

      // Text Search and SEO Optimization Index
      await products.createIndex({
        status: 1,
        category: 1,
        '$text': {
          product_name: 'text',
          seo_title: 'text', 
          seo_description: 'text',
          marketing_tags: 'text'
        },
        rating_average: -1,
        view_count: -1
      }, {
        name: 'idx_products_text_search_optimized',
        background: this.config.backgroundIndexCreation,
        weights: {
          product_name: 10,
          seo_title: 8,
          seo_description: 5,
          marketing_tags: 3
        }
      });

      // Price and Discount Analysis Index
      await products.createIndex({
        status: 1,
        category: 1,
        brand: 1,
        'pricing.effective_price': 1,
        'pricing.discount_percentage': -1,
        'pricing.price_tier': 1,
        featured: -1
      }, {
        name: 'idx_products_pricing_analysis',
        background: this.config.backgroundIndexCreation
      });

      // Performance and Popularity Tracking Index
      await products.createIndex({
        status: 1,
        performance_score: -1,
        view_count: -1,
        purchase_count: -1,
        rating_count: -1,
        created_at: -1
      }, {
        name: 'idx_products_performance_tracking',
        background: this.config.backgroundIndexCreation
      });

      // Supplier and Procurement Index
      await products.createIndex({
        supplier_id: 1,
        status: 1,
        lead_time_days: 1,
        minimum_order_quantity: 1,
        cost: 1,
        updated_at: -1
      }, {
        name: 'idx_products_supplier_procurement',
        background: this.config.backgroundIndexCreation,
        sparse: true
      });

      // Geographic and Dimensional Analysis Index (for products with physical attributes)
      await products.createIndex({
        status: 1,
        category: 1,
        'dimensions.weight_kg': 1,
        'dimensions.volume_cubic_cm': 1,
        'shipping.shipping_class': 1,
        availability: 1
      }, {
        name: 'idx_products_dimensional_analysis',
        background: this.config.backgroundIndexCreation,
        sparse: true,
        partialFilterExpression: {
          'dimensions.weight_kg': { $exists: true }
        }
      });

      // Customer Behavior and Recommendation Index
      await products.createIndex({
        status: 1,
        'analytics.recommendation_score': -1,
        'analytics.conversion_rate': -1,
        'analytics.customer_segment_affinity': 1,
        price: 1,
        rating_average: -1
      }, {
        name: 'idx_products_recommendation_engine',
        background: this.config.backgroundIndexCreation,
        sparse: true
      });

      // Seasonal and Temporal Analysis Index
      await products.createIndex({
        status: 1,
        'lifecycle.seasonality_pattern': 1,
        'lifecycle.peak_season_months': 1,
        created_at: -1,
        discontinued_at: 1,
        'analytics.seasonal_performance_score': -1
      }, {
        name: 'idx_products_seasonal_analysis',
        background: this.config.backgroundIndexCreation,
        sparse: true
      });

      console.log('Advanced compound indexes created successfully');

    } catch (error) {
      console.error('Error creating compound indexes:', error);
      throw error;
    }
  }

  async optimizeQueryWithCompoundIndexes(queryPattern, options = {}) {
    console.log('Optimizing query with intelligent compound index selection...');
    const startTime = Date.now();

    try {
      // Analyze query pattern and select optimal index strategy
      const indexStrategy = await this.analyzeQueryPattern(queryPattern);

      // Build optimized aggregation pipeline
      const optimizedPipeline = await this.buildOptimizedPipeline(queryPattern, indexStrategy, options);

      // Execute query with performance monitoring
      const results = await this.executeOptimizedQuery(optimizedPipeline, indexStrategy);

      // Track performance metrics
      await this.trackQueryPerformance({
        queryPattern: queryPattern,
        indexStrategy: indexStrategy,
        executionTime: Date.now() - startTime,
        results: results.length,
        pipeline: optimizedPipeline
      });

      return {
        results: results,
        performance: {
          executionTime: Date.now() - startTime,
          indexesUsed: indexStrategy.recommendedIndexes,
          optimizationStrategy: indexStrategy.strategy,
          queryPlan: indexStrategy.queryPlan
        },
        optimization: {
          pipelineOptimized: true,
          indexHintsApplied: indexStrategy.hintsApplied,
          coveringIndexUsed: indexStrategy.coveringIndexUsed,
          sortOptimized: indexStrategy.sortOptimized
        }
      };

    } catch (error) {
      console.error('Error optimizing query:', error);

      // Track failed query for analysis
      await this.trackQueryPerformance({
        queryPattern: queryPattern,
        executionTime: Date.now() - startTime,
        error: error.message,
        failed: true
      });

      throw error;
    }
  }

  async analyzeQueryPattern(queryPattern) {
    console.log('Analyzing query pattern for optimal index selection...');

    const analysis = {
      filterFields: [],
      sortFields: [],
      rangeFields: [],
      equalityFields: [],
      textSearchFields: [],
      recommendedIndexes: [],
      strategy: 'compound_optimized',
      hintsApplied: false,
      coveringIndexUsed: false,
      sortOptimized: false
    };

    // Extract filter conditions
    if (queryPattern.match) {
      Object.keys(queryPattern.match).forEach(field => {
        const condition = queryPattern.match[field];

        if (typeof condition === 'object' && condition.$gte !== undefined || condition.$lte !== undefined || condition.$lt !== undefined || condition.$gt !== undefined) {
          analysis.rangeFields.push(field);
        } else if (typeof condition === 'object' && condition.$in !== undefined) {
          analysis.equalityFields.push(field);
        } else if (typeof condition === 'object' && condition.$text !== undefined) {
          analysis.textSearchFields.push(field);
        } else {
          analysis.equalityFields.push(field);
        }

        analysis.filterFields.push(field);
      });
    }

    // Extract sort conditions
    if (queryPattern.sort) {
      Object.keys(queryPattern.sort).forEach(field => {
        analysis.sortFields.push(field);
      });
    }

    // Determine optimal index strategy based on ESR (Equality, Sort, Range) pattern
    analysis.recommendedIndexes = this.selectOptimalIndexes(analysis);

    // Determine if covering index can be used
    analysis.coveringIndexUsed = await this.canUseCoveringIndex(queryPattern, analysis);

    // Check if sort can be optimized
    analysis.sortOptimized = this.canOptimizeSort(analysis);

    return analysis;
  }

  selectOptimalIndexes(analysis) {
    const recommendedIndexes = [];

    // Check if query matches existing optimized compound indexes
    const fieldSet = new Set([...analysis.equalityFields, ...analysis.sortFields, ...analysis.rangeFields]);

    // Search-optimized pattern
    if (fieldSet.has('status') && fieldSet.has('category') && fieldSet.has('price')) {
      recommendedIndexes.push('idx_products_search_optimized');
    }

    // Analytics pattern
    if (fieldSet.has('created_at') && fieldSet.has('category') && fieldSet.has('brand')) {
      recommendedIndexes.push('idx_products_analytics_optimized');
    }

    // Inventory pattern
    if (fieldSet.has('availability') && fieldSet.has('stock_quantity')) {
      recommendedIndexes.push('idx_products_inventory_management');
    }

    // Pricing pattern
    if (fieldSet.has('pricing.effective_price') || fieldSet.has('price')) {
      recommendedIndexes.push('idx_products_pricing_analysis');
    }

    // Text search pattern
    if (analysis.textSearchFields.length > 0) {
      recommendedIndexes.push('idx_products_text_search_optimized');
    }

    // Default to comprehensive catalog index if no specific pattern matches
    if (recommendedIndexes.length === 0) {
      recommendedIndexes.push('idx_products_catalog_comprehensive');
    }

    return recommendedIndexes;
  }

  async buildOptimizedPipeline(queryPattern, indexStrategy, options = {}) {
    console.log('Building optimized aggregation pipeline...');

    const pipeline = [];

    // Match stage with index-optimized field ordering
    if (queryPattern.match) {
      const optimizedMatch = this.optimizeMatchStage(queryPattern.match, indexStrategy);
      pipeline.push({ $match: optimizedMatch });
    }

    // Add computed fields stage if needed
    if (options.addComputedFields) {
      pipeline.push({
        $addFields: {
          effective_price: {
            $cond: {
              if: { $and: [{ $ne: ['$sale_price', null] }, { $lt: ['$sale_price', '$price'] }] },
              then: '$sale_price',
              else: '$price'
            }
          },
          available_quantity: { $subtract: ['$stock_quantity', { $ifNull: ['$reserved_quantity', 0] }] },
          performance_score: {
            $add: [
              { $multiply: ['$rating_average', { $ifNull: ['$rating_count', 0] }] },
              { $multiply: ['$view_count', 0.1] },
              { $multiply: ['$purchase_count', 2] }
            ]
          },
          days_since_created: {
            $divide: [{ $subtract: [new Date(), '$created_at'] }, 1000 * 60 * 60 * 24]
          }
        }
      });
    }

    // Advanced filtering stage with computed fields
    if (options.advancedFilters) {
      pipeline.push({
        $match: {
          available_quantity: { $gt: 0 },
          effective_price: { $gte: options.minPrice || 0, $lte: options.maxPrice || Number.MAX_SAFE_INTEGER }
        }
      });
    }

    // Sorting stage optimized for index usage
    if (queryPattern.sort || options.sort) {
      const sortSpec = queryPattern.sort || options.sort;
      const optimizedSort = this.optimizeSortStage(sortSpec, indexStrategy);
      pipeline.push({ $sort: optimizedSort });
    }

    // Faceting stage for complex analytics
    if (options.enableFaceting) {
      pipeline.push({
        $facet: {
          results: [
            { $limit: options.limit || 50 },
            { $skip: options.skip || 0 }
          ],
          totalCount: [{ $count: 'count' }],
          categoryStats: [
            { $group: { _id: '$category', count: { $sum: 1 }, avgPrice: { $avg: '$effective_price' } } }
          ],
          brandStats: [
            { $group: { _id: '$brand', count: { $sum: 1 }, avgRating: { $avg: '$rating_average' } } }
          ],
          priceStats: [
            {
              $group: {
                _id: null,
                minPrice: { $min: '$effective_price' },
                maxPrice: { $max: '$effective_price' },
                avgPrice: { $avg: '$effective_price' }
              }
            }
          ]
        }
      });
    } else {
      // Standard pagination
      if (options.skip) pipeline.push({ $skip: options.skip });
      if (options.limit) pipeline.push({ $limit: options.limit });
    }

    // Projection stage for covering index optimization
    if (options.projection) {
      pipeline.push({ $project: options.projection });
    }

    return pipeline;
  }

  optimizeMatchStage(matchConditions, indexStrategy) {
    console.log('Optimizing match stage for compound index efficiency...');

    const optimizedMatch = {};

    // Reorder match conditions to align with compound index field order
    // ESR Pattern: Equality conditions first, then sort fields, then range conditions

    // Add equality conditions first (most selective)
    const equalityFields = ['status', 'category', 'brand', 'availability'];
    equalityFields.forEach(field => {
      if (matchConditions[field] !== undefined) {
        optimizedMatch[field] = matchConditions[field];
      }
    });

    // Add other non-range conditions
    Object.keys(matchConditions).forEach(field => {
      if (!equalityFields.includes(field) && !this.isRangeCondition(matchConditions[field])) {
        optimizedMatch[field] = matchConditions[field];
      }
    });

    // Add range conditions last
    Object.keys(matchConditions).forEach(field => {
      if (this.isRangeCondition(matchConditions[field])) {
        optimizedMatch[field] = matchConditions[field];
      }
    });

    return optimizedMatch;
  }

  optimizeSortStage(sortSpec, indexStrategy) {
    console.log('Optimizing sort stage for index-supported ordering...');

    // Reorder sort fields to match compound index ordering when possible
    const optimizedSort = {};

    // Priority order based on common compound index patterns
    const sortPriority = ['featured', 'status', 'category', 'brand', 'price', 'rating_average', 'created_at'];

    // Add sort fields in optimized order
    sortPriority.forEach(field => {
      if (sortSpec[field] !== undefined) {
        optimizedSort[field] = sortSpec[field];
      }
    });

    // Add any remaining sort fields
    Object.keys(sortSpec).forEach(field => {
      if (!sortPriority.includes(field)) {
        optimizedSort[field] = sortSpec[field];
      }
    });

    return optimizedSort;
  }

  isRangeCondition(condition) {
    if (typeof condition !== 'object' || condition === null) return false;
    return condition.$gte !== undefined || condition.$lte !== undefined || 
           condition.$gt !== undefined || condition.$lt !== undefined || 
           condition.$in !== undefined;
  }

  async canUseCoveringIndex(queryPattern, analysis) {
    // Determine if the query can be satisfied entirely by index fields
    // This is a simplified check - in production, analyze the actual query projection
    const projectionFields = queryPattern.projection ? Object.keys(queryPattern.projection) : [];
    const requiredFields = [...analysis.filterFields, ...analysis.sortFields, ...projectionFields];

    // Check against known covering indexes
    const coveringIndexes = [
      'idx_products_search_optimized',
      'idx_products_catalog_comprehensive'
    ];

    // Simplified check - in practice, would verify actual index field coverage
    return requiredFields.length <= 6; // Assume reasonable covering index size
  }

  canOptimizeSort(analysis) {
    // Check if sort fields align with compound index ordering
    return analysis.sortFields.length > 0 && analysis.sortFields.length <= 3;
  }

  async executeOptimizedQuery(pipeline, indexStrategy) {
    console.log('Executing optimized query with performance monitoring...');

    try {
      // Apply index hints if specified
      const aggregateOptions = {
        allowDiskUse: false, // Force memory-based operations for better performance
        maxTimeMS: 30000
      };

      if (indexStrategy.recommendedIndexes.length > 0) {
        aggregateOptions.hint = indexStrategy.recommendedIndexes[0];
      }

      const cursor = this.collections.products.aggregate(pipeline, aggregateOptions);
      const results = await cursor.toArray();

      return results;

    } catch (error) {
      console.error('Error executing optimized query:', error);
      throw error;
    }
  }

  async performCompoundIndexAnalysis() {
    console.log('Performing comprehensive compound index analysis...');

    try {
      // Analyze current index usage and effectiveness
      const indexStats = await this.analyzeIndexUsage();

      // Identify slow queries and optimization opportunities
      const slowQueryAnalysis = await this.analyzeSlowQueries();

      // Generate index recommendations
      const recommendations = await this.generateIndexRecommendations(indexStats, slowQueryAnalysis);

      // Perform index efficiency analysis
      const efficiencyAnalysis = await this.analyzeIndexEfficiency();

      return {
        indexUsage: indexStats,
        slowQueries: slowQueryAnalysis,
        recommendations: recommendations,
        efficiency: efficiencyAnalysis,

        // Summary metrics
        summary: {
          totalIndexes: indexStats.totalIndexes,
          efficientIndexes: indexStats.efficientIndexes,
          underutilizedIndexes: indexStats.underutilizedIndexes,
          recommendedOptimizations: recommendations.length,
          averageQueryTime: slowQueryAnalysis.averageExecutionTime
        }
      };

    } catch (error) {
      console.error('Error performing compound index analysis:', error);
      throw error;
    }
  }

  async analyzeIndexUsage() {
    console.log('Analyzing compound index usage patterns...');

    try {
      // Get index statistics from MongoDB
      const indexStats = await this.collections.products.aggregate([
        { $indexStats: {} }
      ]).toArray();

      const analysis = {
        totalIndexes: indexStats.length,
        efficientIndexes: 0,
        underutilizedIndexes: 0,
        indexDetails: [],
        usagePatterns: new Map()
      };

      for (const indexStat of indexStats) {
        const indexAnalysis = {
          name: indexStat.name,
          accessCount: indexStat.accesses.ops,
          lastAccessed: indexStat.accesses.since,
          keyPattern: indexStat.key,

          // Calculate efficiency metrics
          efficiency: this.calculateIndexEfficiency(indexStat),

          // Usage classification
          usageClass: this.classifyIndexUsage(indexStat)
        };

        analysis.indexDetails.push(indexAnalysis);

        if (indexAnalysis.efficiency > this.config.indexEfficiencyThreshold) {
          analysis.efficientIndexes++;
        }

        if (indexAnalysis.usageClass === 'underutilized') {
          analysis.underutilizedIndexes++;
        }
      }

      return analysis;

    } catch (error) {
      console.error('Error analyzing index usage:', error);
      return { error: error.message };
    }
  }

  calculateIndexEfficiency(indexStat) {
    // Calculate index efficiency based on access patterns and selectivity
    const accessCount = indexStat.accesses.ops || 0;
    const timeSinceCreation = Date.now() - (indexStat.accesses.since ? indexStat.accesses.since.getTime() : Date.now());
    const daysSinceCreation = Math.max(1, timeSinceCreation / (1000 * 60 * 60 * 24));

    const accessesPerDay = accessCount / daysSinceCreation;

    // Efficiency score based on access frequency and recency
    return Math.min(1.0, accessesPerDay / 100); // Normalize to 0-1 scale
  }

  classifyIndexUsage(indexStat) {
    const accessCount = indexStat.accesses.ops || 0;
    const efficiency = this.calculateIndexEfficiency(indexStat);

    if (efficiency > 0.8) return 'highly_utilized';
    if (efficiency > 0.5) return 'moderately_utilized';
    if (efficiency > 0.1) return 'lightly_utilized';
    return 'underutilized';
  }

  async analyzeSlowQueries() {
    console.log('Analyzing slow queries for optimization opportunities...');

    try {
      // This would typically analyze MongoDB profiler data
      // For demo purposes, we'll simulate slow query analysis

      const slowQueryAnalysis = {
        averageExecutionTime: 150, // milliseconds
        slowestQueries: [
          {
            pattern: 'complex_search',
            averageTime: 500,
            count: 25,
            indexMisses: ['category + brand + price range']
          },
          {
            pattern: 'analytics_aggregation', 
            averageTime: 800,
            count: 12,
            indexMisses: ['created_at + category grouping']
          }
        ],
        totalSlowQueries: 37,
        optimizationOpportunities: [
          'Add compound index for category + brand + price filtering',
          'Optimize sort operations with index-aligned ordering',
          'Consider covering indexes for frequent projections'
        ]
      };

      return slowQueryAnalysis;

    } catch (error) {
      console.error('Error analyzing slow queries:', error);
      return { error: error.message };
    }
  }

  async generateIndexRecommendations(indexStats, slowQueryAnalysis) {
    console.log('Generating intelligent index recommendations...');

    const recommendations = [];

    // Analyze missing compound indexes based on slow query patterns
    if (slowQueryAnalysis.slowestQueries) {
      for (const slowQuery of slowQueryAnalysis.slowestQueries) {
        if (slowQuery.indexMisses) {
          for (const missingIndex of slowQuery.indexMisses) {
            recommendations.push({
              type: 'create_compound_index',
              priority: 'high',
              description: `Create compound index for: ${missingIndex}`,
              estimatedImprovement: '60-80% query time reduction',
              implementation: this.generateIndexCreationCommand(missingIndex)
            });
          }
        }
      }
    }

    // Recommend removal of underutilized indexes
    if (indexStats.indexDetails) {
      for (const indexDetail of indexStats.indexDetails) {
        if (indexDetail.usageClass === 'underutilized' && !indexDetail.name.includes('_id_')) {
          recommendations.push({
            type: 'remove_index',
            priority: 'medium',
            description: `Consider removing underutilized index: ${indexDetail.name}`,
            estimatedImprovement: 'Reduced storage overhead and faster writes',
            implementation: `db.collection.dropIndex("${indexDetail.name}")`
          });
        }
      }
    }

    // Recommend index consolidation opportunities
    recommendations.push({
      type: 'consolidate_indexes',
      priority: 'medium',
      description: 'Consolidate multiple single-field indexes into compound indexes',
      estimatedImprovement: 'Better query optimization and reduced storage',
      implementation: 'Analyze query patterns and create strategic compound indexes'
    });

    return recommendations;
  }

  generateIndexCreationCommand(indexDescription) {
    // Generate MongoDB index creation command based on description
    // This is a simplified implementation
    return `db.products.createIndex({/* fields based on: ${indexDescription} */}, {background: true})`;
  }

  async analyzeIndexEfficiency() {
    console.log('Analyzing compound index efficiency and optimization potential...');

    const efficiencyAnalysis = {
      overallEfficiency: 0.75, // Simulated metric
      topPerformingIndexes: [
        'idx_products_search_optimized',
        'idx_products_catalog_comprehensive'
      ],
      improvementOpportunities: [
        {
          index: 'idx_products_analytics_optimized',
          currentEfficiency: 0.6,
          potentialImprovement: 'Reorder fields to better match query patterns',
          estimatedGain: '25% performance improvement'
        }
      ],
      resourceUtilization: {
        totalIndexSize: '450 MB',
        memoryUsage: '180 MB',
        maintenanceOverhead: 'Low'
      }
    };

    return efficiencyAnalysis;
  }

  async trackQueryPerformance(performanceData) {
    try {
      const performanceRecord = {
        ...performanceData,
        timestamp: new Date(),
        collection: 'products'
      };

      await this.collections.queryPerformance.insertOne(performanceRecord);

      // Update in-memory performance tracking
      const pattern = performanceData.queryPattern.match ? 
        Object.keys(performanceData.queryPattern.match).join('+') : 'unknown';

      if (!this.performanceMetrics.queryPatterns.has(pattern)) {
        this.performanceMetrics.queryPatterns.set(pattern, {
          count: 0,
          totalTime: 0,
          averageTime: 0
        });
      }

      const patternMetrics = this.performanceMetrics.queryPatterns.get(pattern);
      patternMetrics.count++;
      patternMetrics.totalTime += performanceData.executionTime;
      patternMetrics.averageTime = patternMetrics.totalTime / patternMetrics.count;

    } catch (error) {
      console.warn('Error tracking query performance:', error);
      // Don't throw - performance tracking shouldn't break queries
    }
  }

  async setupPerformanceMonitoring() {
    console.log('Setting up compound index performance monitoring...');

    setInterval(async () => {
      try {
        await this.performPeriodicAnalysis();
      } catch (error) {
        console.warn('Error in periodic performance analysis:', error);
      }
    }, this.config.performanceAnalysisInterval);
  }

  async performPeriodicAnalysis() {
    console.log('Performing periodic compound index performance analysis...');

    try {
      // Analyze recent query performance
      const recentPerformance = await this.analyzeRecentPerformance();

      // Generate optimization recommendations
      const recommendations = await this.generateOptimizationRecommendations(recentPerformance);

      // Log analysis results
      await this.collections.indexAnalytics.insertOne({
        timestamp: new Date(),
        analysisType: 'periodic_performance',
        performance: recentPerformance,
        recommendations: recommendations
      });

      // Apply automatic optimizations if enabled
      if (this.config.enableAutomaticOptimization && recommendations.length > 0) {
        await this.applyAutomaticOptimizations(recommendations);
      }

    } catch (error) {
      console.error('Error in periodic analysis:', error);
    }
  }

  async analyzeRecentPerformance() {
    const oneHourAgo = new Date(Date.now() - 60 * 60 * 1000);

    const recentQueries = await this.collections.queryPerformance.find({
      timestamp: { $gte: oneHourAgo }
    }).toArray();

    const analysis = {
      totalQueries: recentQueries.length,
      averageExecutionTime: 0,
      slowQueries: recentQueries.filter(q => q.executionTime > this.config.slowQueryThreshold).length,
      queryPatterns: new Map()
    };

    if (recentQueries.length > 0) {
      analysis.averageExecutionTime = recentQueries.reduce((sum, q) => sum + q.executionTime, 0) / recentQueries.length;
    }

    return analysis;
  }

  async generateOptimizationRecommendations(performanceAnalysis) {
    const recommendations = [];

    if (performanceAnalysis.averageExecutionTime > this.config.slowQueryThreshold) {
      recommendations.push({
        type: 'performance_degradation',
        priority: 'high',
        description: 'Average query time has increased significantly',
        action: 'analyze_index_usage'
      });
    }

    if (performanceAnalysis.slowQueries > performanceAnalysis.totalQueries * 0.1) {
      recommendations.push({
        type: 'slow_query_threshold',
        priority: 'medium', 
        description: 'High percentage of slow queries detected',
        action: 'review_compound_indexes'
      });
    }

    return recommendations;
  }

  async applyAutomaticOptimizations(recommendations) {
    console.log('Applying automatic compound index optimizations...');

    for (const recommendation of recommendations) {
      try {
        if (recommendation.action === 'analyze_index_usage') {
          await this.performCompoundIndexAnalysis();
        } else if (recommendation.action === 'review_compound_indexes') {
          await this.reviewIndexConfiguration();
        }
      } catch (error) {
        console.warn(`Error applying optimization ${recommendation.action}:`, error);
      }
    }
  }

  async reviewIndexConfiguration() {
    console.log('Reviewing compound index configuration for optimization opportunities...');

    // This would analyze current indexes and suggest improvements
    const review = {
      timestamp: new Date(),
      reviewType: 'automated_compound_index_review',
      findings: [
        'All critical compound indexes are present',
        'Index usage patterns are within expected ranges',
        'No immediate optimization required'
      ]
    };

    await this.collections.indexAnalytics.insertOne(review);
  }
}

// Benefits of MongoDB Advanced Compound Indexing:
// - Intelligent multi-field query optimization with ESR (Equality, Sort, Range) pattern adherence
// - Advanced compound index strategies tailored for different query patterns and use cases
// - Comprehensive performance monitoring and automatic optimization recommendations  
// - Sophisticated index selection and query plan optimization
// - Built-in covering index support for maximum query performance
// - Automatic index efficiency analysis and maintenance recommendations
// - Production-ready compound indexing with minimal configuration overhead
// - Advanced query pattern analysis for optimal index design
// - Resource-aware index management with storage and memory optimization
// - SQL-compatible compound indexing through QueryLeaf integration

module.exports = {
  AdvancedCompoundIndexManager
};