Performance Optimization: Add index for query-logs endpoint-created-by-agentic

[doppleware]

Background

The /query-logs endpoint has been experiencing severe performance degradation with high z-scores and increased duration.

Issue Description

• Endpoint: /api/clinic-activity/query-logs
• Problem: Performance degradation in query execution
• Root Cause: Missing index on numeric_value column in clinic_activity_logs table
• Impact: High query execution times and low buffer cache hit rates

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

• Created PR Add index on numeric_value column for query performance-created-by-agentic #427 with database migration file
• Index has been created and verified in the database
• Query execution plans show proper index usage

Monitoring

• Buffer cache hit rates are being monitored
• Query performance is being tracked

Related

• PR Add index on numeric_value column for query performance-created-by-agentic #427