Skip to content
OrionHub Developer tooling and cloud development
Section
performance
Kind
Editorial
Reading time
4 min
Updated
8 September 2026

The Slow Query Nobody Notices: Finding It Before Your Users Do

Slow performance is often a result of a query pattern that appears harmless on its own, but grows increasingly slow as the volume of data and traffic increases. It is easy to overlook these problems at first because they may not immediately hit the logging thresholds. However, as users make requests more frequently and the back-end data becomes more extensive, these patterns can lead to performance bottlenecks. This article explores the techniques to recognize these hidden culprits before your users notice the growing latency.

Set the Tripwire

The first step in identifying slow queries is to establish a baseline. This baseline is defined by the database's slow-query logging threshold. Different databases use different default values and units for this threshold:

  • MySQL's slow-query log uses a parameter called long_query_time, which defaults to 10 seconds and can be set to any value with microsecond precision. Source
  • PostgreSQL uses log_min_duration_statement, with a value of -1 to disable logging and 0 to log everything, including statements that complete in practically no time. Source
  • MongoDB logs queries that exceed the duration set in slowms, which defaults to 100 milliseconds. Source

These thresholds are essential because they provide a way to capture potentially problematic queries without flooding the logs with noise. However, setting the value too high or too low can lead to dismissing slow queries or masking performance issues in the volume of data, respectively. The key is to fine-tune these settings based on the specific needs and characteristics of your application.

Find the Offenders in Aggregate

Once the threshold is in place, the next step is to analyze the logged queries. Simply counting the occurrences of each query is not enough. It is crucial to consider the context in which these queries are executed. Aggregating the data by endpoint or call path rather than by raw statement provides a more accurate representation of the performance bottlenecks.

For example, MySQL's slow-query log includes additional data that can be used to group queries by context. Furthermore, MySQL provides the ability to rate-limit logging of queries that do not use indexes, allowing you to focus on the most impactful queries. Source

By aggregating the data by endpoint or call path, you can identify the specific areas of your application that are most affected by slow queries. This approach makes it easier to pinpoint the root cause of the performance issues and focus your optimization efforts on the most critical areas.

Read the Plan by Shape

When analyzing the slow queries, it is essential to look beyond the SQL syntax and focus on the query execution plan instead. A well-structured execution plan can reveal the underlying issues that contribute to slow performance. While analyzing query plans can be complex, learning to recognize the different plan shapes and their implications can greatly assist in identifying performance bottlenecks.

For instance, a query plan that displays a large number of scans, such as full table or index scans, may indicate a lack of appropriate indexing. Conversely, a plan that involves nested loops or hash joins may point to join-related performance issues. Understanding query plan shapes allows you to quickly identify the most significant contributors to slow performance and focus your optimization efforts on the most critical areas.

The Two Common Growth Traps

As the volume of data and user traffic grows, specific query patterns become increasingly problematic. Two common culprits are: ORM round trips, and Pagination requests.

ORMs can generate a large number of sequential queries, each fetching related data one at a time. This pattern, known as N+1 query where one row fetch leads to n separate related-row fetches, can quickly add up to significant performance degradation as the data set expands.

Pagination can also lead to performance bottlenecks, particularly when implemented inefficiently. For large tables, a simple offset-based pagination approach can become increasingly slow as the page number increases.

To avoid these pitfalls, developers should be aware of the potential performance impact of these patterns and design their queries and data structures accordingly. Implementing efficient pagination strategies, limiting ORM round trips, and leveraging database features such as range key searches can help mitigate these issues and maintain performance as the data set grows.

Fix the Highest-Leverage Pattern First

When tackling performance issues, it is crucial to prioritize the fixes that will have the most significant impact. However, there is no universal "one-size-fits-all" answer to what to fix first. The recommended approach often varies depending on the underlying issue and the specific characteristics of the application.

Well-executed indexes can significantly accelerate the query execution time by reducing the scan time and enabling more efficient retrieval of data. However, indiscriminate indexing can lead to maintenance overhead and slow data insertion without delivering significant performance benefits. Learning to identify the queries that would benefit most from an indexing strategy is a critical skill.

Pagination and ORM-related optimizations, like implementing efficient data fetching strategies, can also yield substantial performance improvements. These optimizations help reduce the volume of data being transferred and the number of round trips to the database, leading to faster response times and improved user experience.

In conclusion, recognizing and addressing performance bottlenecks requires a combination of careful monitoring, data analysis, and targeted optimizations. By setting appropriate slow-query thresholds, aggregating data by context, analyzing query execution plans, and prioritizing the most impactful fixes, developers can proactively identify and resolve performance issues before they impact the user experience. While there is no silver bullet to optimizing database performance, these principles can serve as a solid foundation for maintaining a responsive and efficient application as it grows.