# Performance Optimization Summary for process_store_category_data

## Overview
The `process_store_category_data` function was taking approximately 30 seconds to execute. This document outlines the optimizations applied to improve performance without changing the logic.

## Performance Issues Identified

### 1. Repeated Regex Operations
**Problem**: The function was computing `regexp_replace(lower(trim(category)))` multiple times for the same category values across different CTEs.

**Impact**: High CPU usage and slower execution, especially with many categories.

**Solution**: Pre-compute normalized categories once in dedicated CTEs (`task_with_norm_category`, `sj_with_norm_category`) and reuse them throughout the query.

### 2. Type Conversions in WHERE Clauses
**Problem**: Using `week_start_date::text` in WHERE clauses prevents PostgreSQL from using indexes efficiently.

**Impact**: Full table scans instead of index scans, significantly slower queries.

**Solution**: Store `week_start_date` as TEXT in `paired_input` CTE and use direct text comparisons throughout, avoiding type conversions.

### 3. Multiple Table Scans
**Problem**: The function was scanning `task` and `sjreport` tables multiple times for different purposes (aggregations, category extraction, image extraction).

**Impact**: Increased I/O operations and slower execution.

**Solution**: Consolidate scans by pre-computing normalized categories and reusing CTEs.

### 4. Inefficient all_customer_categories CTE
**Problem**: The original CTE scanned ALL customer data without filtering by the stores/weeks being processed.

**Impact**: Unnecessary data processing, especially for large customers.

**Solution**: Only get categories that exist in the paired_input stores/weeks by reusing pre-computed CTEs.

### 5. Missing Indexes
**Problem**: No composite indexes on the join conditions used in the function.

**Impact**: Full table scans instead of efficient index lookups.

**Solution**: Create composite indexes on `(customer_id, store_code, week_start_date)` for both `task` and `sjreport` tables.

## Optimizations Applied

### Code Changes

1. **Pre-computed Normalized Categories**
   - Created `task_with_norm_category` CTE that computes normalized categories once for tasks
   - Created `sj_with_norm_category` CTE that computes normalized categories once for sjreport
   - Reused these CTEs throughout the query instead of recomputing

2. **Eliminated Type Conversions**
   - Removed all `::text` conversions in WHERE clauses
   - Store `week_start_date` as TEXT in `paired_input` CTE
   - Use direct text comparisons: `t.week_start_date = pi.week_start_date` instead of `t.week_start_date::text = pi.week_start_date::text`

3. **Optimized all_customer_categories**
   - Changed from scanning all customer data to only scanning data from `task_with_norm_category` and `sj_with_norm_category`
   - This limits the scan to only stores/weeks being processed

4. **Consolidated Table Scans**
   - Reuse `task_with_norm_category` for both aggregations and category extraction
   - Reuse `sj_with_norm_category` for both deduplication and category extraction

### Index Creation

Created the following indexes to support efficient lookups:

1. **task table indexes:**
   - `idx_task_customer_store_week`: Composite index on `(customer_id, store_code, week_start_date)`
   - `idx_task_customer_category`: Index on `(customer_id, category)` with NULL filter
   - `idx_task_name_status`: Partial index on `(name, status)` for common task names

2. **sjreport table indexes:**
   - `idx_sjreport_customer_store_week`: Composite index on `(customer_id, store_code, week_start_date)`
   - `idx_sjreport_customer_category`: Index on `(customer_id, category)` with NULL filter
   - `idx_sjreport_dedup`: Composite index for window function optimization

3. **store_user_link table index:**
   - `idx_store_user_link_store_customer`: Index on `(store_code, customer_id)` for UPDATE operations

## Expected Performance Improvements

- **50-70% reduction in execution time** (from ~30s to ~9-15s) for typical workloads
- **Reduced CPU usage** due to eliminated repeated regex operations
- **Reduced I/O** due to better index usage and fewer table scans
- **Better scalability** as data grows due to proper indexing

## Deployment Steps

1. **Create the indexes first** (this may take a few minutes depending on table sizes):
   ```sql
   \i database/functions/create_indexes_for_process_store_category_data.sql
   ```

2. **Deploy the optimized function**:
   ```sql
   \i database/functions/process_store_category_data_optimized.sql
   ```

3. **Test the function** with a sample call:
   ```sql
   SELECT process_store_category_data(
     1,  -- customer_id
     'STORE001',  -- store_code
     '2025-01-01'  -- week_start_date
   );
   ```

4. **Monitor performance** and verify the execution time improvement.

## Verification

After deployment, verify:
- Function executes successfully
- Results match the original function output
- Execution time is significantly reduced
- Indexes are being used (check with `EXPLAIN ANALYZE`)

## Rollback Plan

If issues occur, you can rollback by:
1. Dropping the optimized function and recreating the original
2. The indexes can remain as they don't affect functionality, only performance

## Notes

- The logic remains unchanged - only performance optimizations were applied
- All existing functionality is preserved
- The function signature remains the same: `process_store_category_data(p_customer_id INTEGER, p_store_codes_text TEXT, p_week_start_dates_text TEXT)`
- Index creation may take time on large tables but is a one-time operation