> ## Documentation Index
> Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Index Optimization Report

> This document tracks the index optimization work completed for Encore OS Platform as part of Phase 2A performance improvements.

> **Version:** 1.0.0\
> **Date:** 2026-01-17\
> **Status:** ✅ Complete

## Executive Summary

This document tracks the index optimization work completed for Encore OS Platform as part of Phase 2A performance improvements.

### Key Metrics

| Metric                     | Before | After   |
| -------------------------- | ------ | ------- |
| Missing FK Indexes         | 60     | 0 ✅     |
| Tables with High Seq Scans | 35+    | Indexed |
| Duplicate Indexes          | 3      | 0 ✅     |

***

## Phase 2A: Foreign Key Indexes

### Methodology

1. **Analysis:** Queried `pg_stat_user_tables` for tables with high sequential scan counts
2. **Verification:** Cross-referenced with actual schema to identify missing FK indexes
3. **Prioritization:** Ordered by sequential scan count (highest first)
4. **Implementation:** Created indexes in 3 batches by priority

### Migration 1: High Priority (18 indexes)

Tables with >500 sequential scans:

| Table                             | Column               | Seq Scans |
| --------------------------------- | -------------------- | --------- |
| `pf_audit_logs`                   | `user_id`            | 1,503     |
| `pf_audit_logs`                   | `record_id`          | 1,503     |
| `pf_notifications`                | `user_id`            | 1,356     |
| `pf_notifications`                | `template_id`        | 1,356     |
| `pf_notifications`                | `batch_id`           | 1,356     |
| `fw_form_submissions`             | `form_id`            | 1,161     |
| `hr_credential_renewal_workflows` | `employee_id`        | 1,047     |
| `pf_tasks`                        | `source_id`          | 693       |
| `lo_issues`                       | `meeting_id`         | 684       |
| `lo_meetings`                     | `agenda_template_id` | 677       |
| `hr_job_postings`                 | `position_id`        | 522       |
| `hr_job_postings`                 | `department_id`      | 522       |
| `hr_job_postings`                 | `hiring_manager_id`  | 522       |
| `hr_offers`                       | `application_id`     | 517       |
| `hr_offers`                       | `position_id`        | 517       |
| `hr_employee_credentials`         | `employee_id`        | 507       |
| `hr_employee_credentials`         | `credential_type_id` | 507       |
| `hr_interviews`                   | `application_id`     | 499       |

### Migration 2: Medium Priority (15 indexes)

Tables with 200-500 sequential scans:

| Table                       | Column                |
| --------------------------- | --------------------- |
| `pf_ai_conversations`       | `user_id`             |
| `pf_org_dashboard_defaults` | `template_id`         |
| `hr_applications`           | `job_posting_id`      |
| `hr_employees`              | `manager_id`          |
| `hr_employees`              | `position_id`         |
| `hr_employees`              | `department_id`       |
| `hr_employees`              | `primary_site_id`     |
| `pf_wizard_templates`       | `current_version_id`  |
| `pf_wizard_templates`       | `source_template_id`  |
| `rh_episodes`               | `bed_id`              |
| `rh_episodes`               | `residence_id`        |
| `rh_episodes`               | `resident_profile_id` |
| `hr_leave_requests`         | `employee_id`         |
| `hr_incidents`              | `employee_id`         |
| `hr_pay_rates`              | `employee_id`         |

### Migration 3: Standard Priority (27 indexes)

Finance and governance FK indexes:

| Table                  | Column                |
| ---------------------- | --------------------- |
| `fa_invoices`          | `customer_id`         |
| `fa_invoices`          | `site_id`             |
| `fa_invoices`          | `journal_entry_id`    |
| `fa_vendor_bills`      | `vendor_id`           |
| `fa_vendor_bills`      | `site_id`             |
| `fa_vendor_bills`      | `journal_entry_id`    |
| `fa_accounts`          | `parent_account_id`   |
| `fa_accounts`          | `default_fund_id`     |
| `fa_bank_accounts`     | `gl_account_id`       |
| `fa_budgets`           | `fiscal_year_id`      |
| `fa_budgets`           | `approved_by`         |
| `fa_budgets`           | `submitted_by`        |
| `fa_customer_payments` | `customer_id`         |
| `fa_customer_payments` | `journal_entry_id`    |
| `fa_credit_memos`      | `customer_id`         |
| `fa_credit_memos`      | `original_invoice_id` |
| `fa_credit_memos`      | `journal_entry_id`    |
| `gr_contracts`         | `vendor_id`           |
| `gr_contracts`         | `assigned_to`         |
| `gr_policies`          | `category_id`         |
| `gr_policies`          | `approved_by`         |
| `fm_work_orders`       | `asset_id`            |
| `fm_work_orders`       | `assigned_to`         |
| `fm_work_orders`       | `site_id`             |
| `fm_inventory_items`   | `category_id`         |
| `fm_inventory_items`   | `preferred_vendor_id` |
| `pf_documents`         | `site_id`             |

***

## Duplicate Index Cleanup

Removed 3 duplicate indexes that were redundant:

| Index Removed                    | Covered By         |
| -------------------------------- | ------------------ |
| Previously identified duplicates | Cleaned in Phase 1 |

***

## Index Verification

### Check New Indexes

```sql theme={null}
SELECT 
  indexname,
  tablename,
  indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND indexname LIKE 'idx_%'
ORDER BY tablename, indexname;
```

### Monitor Index Usage

```sql theme={null}
-- Check index usage after 1 week
SELECT * FROM pf_get_unused_indexes();
```

### Check for Missing Indexes

```sql theme={null}
-- Find FKs without indexes
SELECT
  tc.table_name,
  kcu.column_name,
  ccu.table_name AS foreign_table_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND NOT EXISTS (
    SELECT 1 FROM pg_indexes
    WHERE tablename = tc.table_name
      AND indexdef LIKE '%' || kcu.column_name || '%'
  )
ORDER BY tc.table_name;
```

***

## Performance Impact

### Expected Improvements

| Query Type        | Before               | After         |
| ----------------- | -------------------- | ------------- |
| FK lookups        | Sequential scan      | Index scan    |
| JOIN operations   | Slow on large tables | Optimized     |
| RLS policy checks | Subquery per row     | Direct lookup |

### Monitoring Queries

```sql theme={null}
-- Check slow queries
SELECT * FROM pf_get_slow_queries(100, 20);

-- Check table bloat
SELECT * FROM pf_get_table_bloat();
```

***

## Maintenance Recommendations

1. **Weekly:** Run `VACUUM ANALYZE` on high-traffic tables (automatic via Supabase)
2. **Monthly:** Check `pf_get_unused_indexes()` for indexes to remove
3. **Quarterly:** Review `pf_get_slow_queries()` for new indexing opportunities

***

## References

* [Performance Optimization Log](/performance/PERFORMANCE_OPTIMIZATION_LOG)
* [RLS Policy Audit](https://github.com/Encore-OS/encoreos/blob/development/docs/database/RLS_POLICY_AUDIT.md)
* [Database Audit Report](https://github.com/Encore-OS/encoreos/blob/development/docs/archive/architecture/DATABASE_AUDIT_REPORT.md)