From a 49-Second KPI Request to an 840-Millisecond Response

Introduction

A customer-reported issue initially appeared to be a backend or database performance problem. A KPI request with thousands of selected elements was taking almost 50 seconds to complete, which was unacceptable for an interactive analytics experience.

Through structured investigation, production data extraction, local reproduction, automated E2E testing, profiling, and algorithmic optimization, we reduced the execution time from approximately 49 seconds to 840 milliseconds—an improvement of roughly 58 times.

AI played an important role throughout the investigation by helping convert the customer’s captured request into a repeatable E2E test, preserve the complete list of 4,000 selected sites, prepare the production data for safe local testing, analyze timing logs, and identify the inefficient O(n²) response-building logic. It also helped propose the one-pass grouping algorithm and pointer-based iteration, after which the changes were validated through regression tests and the complete E2E suite. AI accelerated the investigation and highlighted likely bottlenecks, while the final decisions were confirmed through profiling, real data, code review, and automated testing.

This article describes the investigation and optimization process.

1. Starting with a Customer-Reported Bug

The investigation began when a customer reported that a KPI Explore request was taking too long to return results.

The request contained:

• A large list of selected sites
• A date range and daily granularity
• Approximately 4,000 selected sites

At first, the problem could have been caused by several layers:

• The HTTP endpoint
• Request validation
• PostgreSQL metadata lookup
• ClickHouse query execution
• Go result processing
• JSON serialization
• Network transfer

The priority was to reproduce the exact customer request reliably.

2. Finding the Related Endpoint and Payload

The customer’s frontend request was inspected to identify the exact API endpoint and payload structure.

The request used the KPI endpoint:

POST /api/kpi/

The payload included the full selected-site list inside filters.

This was important because testing with only a few sites would not reproduce the production behavior. The performance issue was directly related to the number of selected elements, so the test had to use the complete list.

3. Creating an E2E Test with AI Assistance

An automated E2E test was created to reproduce the request consistently.

The test validated that:

• All 4,000 sites were included
• The request used the correct KPI, technology, vendor, and date range
• The endpoint returned HTTP 200 OK
• The response was marked as finished
• At least one KPI result was returned
• The returned series contained data

The test became the foundation for measuring performance before and after each change.

Using AI assistance accelerated several parts of the work:

• Converting the captured request into a Go test
• Preserving the complete element list
• Identifying missing request fields
• Building assertions around the response
• Re-running and interpreting test results

However, the important engineering principle was that AI-generated changes were verified against real data and automated tests.

4. Reproducing the Issue with Production Data Locally

To avoid making changes directly against production, the required ClickHouse data was exported in read-only mode.

The production table contained:

Table: data_site_daily
Rows: 1,330,192
Storage size: approximately 990.7 MiB

The table was exported to local files:

• CSV data file
• Executable table schema file

The data was then imported into a local ClickHouse instance running with Docker Compose.

The local copy was verified:

Rows: 1,330,192
Date range: 2026-05-24 to 2026-09-15

This created a safe and repeatable environment for profiling the application with realistic data.

5. Separating ClickHouse Time from Go Processing Time

The first complete E2E run took approximately:

49.017 seconds

The ClickHouse repository log showed that the database returned:

20,580 rows

The key timing evidence was:

• ClickHouse query completed quickly
• The HTTP response was returned approximately 48 seconds later
• The final response size was only about 620 KB

This showed that ClickHouse and network transfer were not the primary bottlenecks.

The delay was inside the Go service after the database query completed.

It is also important to consider that heavy requests and responses can affect network bandwidth and performance. However, in this case, the network payload was not large enough to explain the 49-second delay. The dominant cost was local Go processing.

Recommended by LinkedIn

6. Discovering the O(n²) Algorithm

The response builder generated a time series for every selected site.

The original logic worked approximately like this:

for each selected site:
    scan every returned row

With:

4,398 selected sites
20,580 returned rows

The total number of comparisons was approximately:

4,398 × 20,580 = 90,510,840 comparisons

This was effectively an O(n²)-style operation for the request shape.

The function also used:

for _, r := range results

Because Layers is a large structure containing many strings and a metrics map, each iteration copied the structure. The row address was then passed to a callback, which increased allocation and garbage-collection pressure.

CPU profiling showed significant activity in:

• runtime.duffcopy
• runtime.mallocgc
• runtime.gcDrain
• runtime.scanobject

This confirmed that the service was spending substantial time copying objects and cleaning up allocations in addition to performing repeated comparisons.

7. Rewriting the Algorithm from O(n²) to O(n)

The response transformation was rewritten to group rows in a single pass.

Instead of scanning all rows once per site, the new approach:

1. Builds a set of requested filter values.
2. Iterates through the returned rows once.
3. Extracts the filter value from each row.
4. Groups the metric data by that value.
5. Builds the final response in the original request order.

The effective complexity changed from:

O(selected values × returned rows)

to approximately:

O(returned rows + selected values)

The row iteration was also changed to use the original row pointer:

for i := range results {
    row := &results[i]
}

This avoids copying the large LayerKPI structure for every iteration.

The implementation preserved existing behavior, including:

• Request ordering
• Duplicate filter values
• Missing metric handling
• Cell-to-sector fallback behavior
• Response format and series names

8. Validating the Optimization

Regression tests were added for:

• Grouping multiple rows by site
• Preserving request order
• Preserving duplicate selections
• Ignoring missing metrics
• GSM/Nokia cell fallback
• Confirming pointer-based row access

The complete E2E test was then executed again against the local ClickHouse dataset.

Performance results:

MeasurementBeforeAfterAPI execution time49.017 seconds0.840 secondsImprovement—Approximately 58× fasterReturned rows20,58020,580Response size619,699 bytes619,699 bytesSelected sites4,3984,398

The complete test suite also passed:

60 tests passed
0 tests failed
Total duration: 64.718 seconds

The local ClickHouse table was verified after testing and still contained:

1,330,192 rows

Conclusion

The issue was initially reported as a slow customer request, but the investigation demonstrated that the database was not the main bottleneck.

The actual problem was an inefficient Go response-building algorithm that repeatedly scanned the same rows for every selected site. The function effectively performed more than 90 million comparisons and generated unnecessary structure copies and garbage-collection work.

By grouping rows in one pass and iterating through the original structures using pointers, the processing model was reduced from O(n²) behavior to approximately O(n).

The final result was a reduction from 49 seconds to 840 milliseconds—more than a 50-times improvement—while maintaining the same response data and passing the complete E2E test suite.

The broader lesson is that performance investigations should measure each layer independently:

1. Reproduce the real customer request.
2. Use production-shaped data safely in a local environment.
3. Separate database time from application processing time.
4. Profile before optimizing.
5. Improve the algorithm, not only the infrastructure.
6. Validate functionality and performance with automated tests.