Performance Optimization for /query-logs Endpoint-created-by-agentic

[doppleware]

Issue Description

The /query-logs endpoint is experiencing severe performance degradation with high response times (around 30s).

Root Cause

1. Missing index on the numeric_value column in the clinic_activity_logs table
2. No query plan hints for consistent performance
3. No result caching implementation

Solution

1. Created index:

CREATE INDEX crystaldba_idx_clinic_activity_logs_numeric_value_1 ON clinic_activity_logs USING btree (numeric_value)

2. Added query hints and implemented caching in the code (see PR Optimize query-logs endpoint performance-created-by-agentic #448)

Performance Impact

• Query execution time improved from ~30s to ~11ms
• Consistent query plan selection with index hint
• Additional performance improvement through result caching

Related

• PR Optimize query-logs endpoint performance-created-by-agentic #448: Optimize query-logs endpoint performance