Performance Issue: Slow queries on clinic_activity_logs table-created-by-agentic

[doppleware]

A critical performance issue has been identified in the clinic_activity_logs table queries.

Problem Description

• Query execution time: 2.69s (expected: 653.25μs)
• Full sequential scan on numeric_value column
• Low table cache hit rate (20.6%)
• Affects /api/clinic-activity/query-logs endpoint
• 652 samples showing consistent degradation
• Z-Score: 12.37 (severe deviation from normal)

Root Cause

Missing index on the numeric_value column causing full table scans.

Solution

1. Created index on numeric_value column
2. Added migration file for the index
3. Verified performance improvement
   • Before: 2.69s
   • After: ~0.3ms

Related

• PR Add index on clinic_activity_logs.numeric_value for performance-created-by-agentic #116 - Index creation
• PR added average visits per pet #103 - Related to increased database load

Monitoring

• Query performance has been verified
• Cache hit rates should be monitored
• Additional optimization may be needed if load increases further