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

[doppleware]

Issue Description

The /query-logs endpoint is experiencing severe performance degradation, with response times increasing from 525.5μs to 4.26s. This is caused by missing index on the numeric_value column in the clinic_activity_logs table.

Current Status

• Response time: 4.26s (degraded from 525.5μs)
• Table cache hit rate: 22.8% (below optimal)
• Index cache hit rate: 88.8% (below optimal)

Solution

A new btree index has been created on the numeric_value column:

CREATE INDEX crystaldba_idx_clinic_activity_logs_numeric_value_1 ON clinic_activity_logs USING btree (numeric_value);

Implementation

• PR Add index on clinic_activity_logs.numeric_value for query performance-created-by-agentic #429 has been created with the necessary schema changes
• The index has been added directly to the database
• Schema.sql has been updated to include the index for future deployments

Monitoring

• Performance will be monitored through Digma to ensure the index resolves the issue
• Buffer cache settings should be reviewed to improve hit rates

Related

• PR Add index on clinic_activity_logs.numeric_value for query performance-created-by-agentic #429: Add index on clinic_activity_logs.numeric_value for query performance