Performance Optimization: Add index for clinic_activity_logs numeric_value column-created-by-agentic

[doppleware]

Issue Description

The /query-logs endpoint is experiencing severe performance degradation due to inefficient queries on the clinic_activity_logs table, particularly when filtering or sorting by the numeric_value column.

Impact

• High query execution times
• Increased server load
• Degraded user experience
• Issue has been ongoing from May 21, 2025 to July 23, 2025

Solution

Added an index on the numeric_value column to improve query performance:

CREATE INDEX idx_clinic_activity_logs_numeric_value ON clinic_activity_logs(numeric_value);

Implementation

The fix has been implemented in PR #444 which adds the index to the PostgreSQL schema.

Additional Recommendations

1. Monitor query performance after index creation
2. Consider implementing query result caching if real-time data isn't required
3. Add monitoring for query execution time
4. Review and optimize the query implementation
5. Consider implementing pagination for large result sets
6. Evaluate table partitioning if the table continues to grow significantly

Related

• PR Add index on numeric_value column for clinic_activity_logs table-created-by-agentic #444