pg_stat_statements for slow queries in PostgreSQL - Time & Space Complexity

Time Complexity: pg_stat_statements for slow queries

O(n log n)

Understanding Time Complexity

We want to understand how the time to find slow queries grows as the number of queries increases.

How does checking query statistics scale when many queries are logged?

Scenario Under Consideration

Analyze the time complexity of this query using pg_stat_statements.

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
    

This query fetches the top 10 slowest queries by average execution time.

Identify Repeating Operations

Look for repeated steps in the query process.

• Primary operation: Sorting all recorded queries by their average time.
• How many times: Once per query execution, but sorting involves comparing all query entries.

How Execution Grows With Input

As the number of queries logged grows, sorting takes more work.

Input Size (n)	Approx. Operations
10	About 30 comparisons
100	About 700 comparisons
1000	About 10,000 comparisons

Pattern observation: Sorting work grows faster than the number of queries, roughly by n log n.

Final Time Complexity

Time Complexity: O(n log n)

This means the time to find the slowest queries grows a bit faster than the number of queries as more are logged.

Common Mistake

[X] Wrong: "Sorting the queries is just a simple list scan and takes linear time."

[OK] Correct: Sorting requires comparing many pairs, so it takes more than just looking once at each query.

Interview Connect

Understanding how query statistics scale helps you manage database performance and shows you can think about efficiency in real systems.

Self-Check

"What if we added a filter to only sort queries from the last hour? How would the time complexity change?"