Degraded Performance

Summary

Degraded Performance

# Post-Mortem: Trial Balance Performance Degradation ## Executive Summary A missing database index on the `account` table resulted in severe performance degradation for trial balance queries, causing excessive CPU utilisation and poor user experience. Implementation of the `account_worksheet_id_lookup_idx` index reduced query execution time by **98.5%**, from 1,286ms to 19ms with seed data. ## Issue Description Trial balance load times were significantly impacted due to PostgreSQL performing sequential scans on the `account` table, which contains ~**2 million records** in production. The query optimiser was forced to scan all rows instead of using an efficient index lookup, resulting in: * High CPU utilisation on RDS instances * User-facing latency * Increased database resource consumption affecting overall system performance ## Root Cause Analysis The `account` table had an existing index `account_id_worksheet_id_idx` with columns ordered as `(id, worksheet_id)`. However, the trial balance query filters by `worksheet_id`, making this index ineffective for the lookup pattern. PostgreSQL cannot efficiently use a compound index when searching by the second column only. ## Resolution Created a new partial index on `account(worksheet_id)` with a WHERE clause for non-null values, enabling efficient lookups for the trial balance query pattern. ## Impact & Metrics ### Performance Improvements | Metric | Before | After | Improvement | | --- | --- | --- | --- | | **Execution Time** | 1,286.6ms | 19.2ms | **98.5%** ↓ | | **Total Time** | 1,330ms | 42ms | **96.8%** ↓ | | **Rows Scanned** | 2,278,000 | 25 | **99.999%** ↓ | | **Query Cost** | 2,263,972 | 5,061 | **99.8%** ↓ | ### Key Statistics * **Performance Factor**: 67x faster execution * **CPU Impact**: Significant reduction in RDS CPU utilisation * **Verification**: Confirmed via RDS Performance Insights and CloudWatch logs ## Lessons Learned 1. **Index column order is critical** - PostgreSQL cannot efficiently use a compound index when filtering by the second column 2. **Sequential scans on large tables have a severe impact** - 2M row scans vs 25 targeted index lookups during testing 3. **Query plan analysis with production-scale data is essential** for identifying performance bottlenecks early

• Started

  Jul 30, 2025 · 11:29 AM

• Resolved

  Jul 30, 2025 · 5:49 PM

• Duration

  6h 20m

• Severity

  Minor

• ◐

  Investigating

  Jul 30 · 11:29 AM Cimplico

  We are currently investigating degraded performance in the application.

• ◉

  Monitoring

  Jul 30 · 3:55 PM Cimplico

  A fix has been implemented and we are monitoring the results.

• ✓

  Resolved

  Jul 30 · 5:49 PM Cimplico

  We have recorded significant improvements to the load speeds within the application. We will continue to monitor the issue, but will be marking this incident as resolved.

• ✓

  Postmortem

  Jul 30 · 8:04 PM Cimplico

  # Post-Mortem: Trial Balance Performance Degradation ## Executive Summary A missing database index on the `account` table resulted in severe performance degradation for trial balance queries, causing excessive CPU utilisation and poor user experience. Implementation of the `account_worksheet_id_lookup_idx` index reduced query execution time by **98.5%**, from 1,286ms to 19ms with seed data. ## Issue Description Trial balance load times were significantly impacted due to PostgreSQL performing sequential scans on the `account` table, which contains ~**2 million records** in production. The query optimiser was forced to scan all rows instead of using an efficient index lookup, resulting in: * High CPU utilisation on RDS instances * User-facing latency * Increased database resource consumption affecting overall system performance ## Root Cause Analysis The `account` table had an existing index `account_id_worksheet_id_idx` with columns ordered as `(id, worksheet_id)`. However, the trial balance query filters by `worksheet_id`, making this index ineffective for the lookup pattern. PostgreSQL cannot efficiently use a compound index when searching by the second column only. ## Resolution Created a new partial index on `account(worksheet_id)` with a WHERE clause for non-null values, enabling efficient lookups for the trial balance query pattern. ## Impact & Metrics ### Performance Improvements | Metric | Before | After | Improvement | | --- | --- | --- | --- | | **Execution Time** | 1,286.6ms | 19.2ms | **98.5%** ↓ | | **Total Time** | 1,330ms | 42ms | **96.8%** ↓ | | **Rows Scanned** | 2,278,000 | 25 | **99.999%** ↓ | | **Query Cost** | 2,263,972 | 5,061 | **99.8%** ↓ | ### Key Statistics * **Performance Factor**: 67x faster execution * **CPU Impact**: Significant reduction in RDS CPU utilisation * **Verification**: Confirmed via RDS Performance Insights and CloudWatch logs ## Lessons Learned 1. **Index column order is critical** - PostgreSQL cannot efficiently use a compound index when filtering by the second column 2. **Sequential scans on large tables have a severe impact** - 2M row scans vs 25 targeted index lookups during testing 3. **Query plan analysis with production-scale data is essential** for identifying performance bottlenecks early