EXPLAIN ANALYZE for query profiling in PostgreSQL - Time & Space Complexity

Time Complexity: EXPLAIN ANALYZE for query profiling

O(n)

Understanding Time Complexity

When we run a database query, we want to know how long it takes and why. EXPLAIN ANALYZE helps us see the real cost of a query.

We ask: How does the query's work grow as the data grows?

Scenario Under Consideration

Analyze the time complexity of the following query profiling command.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 123;
    

This command runs the query and shows detailed timing and steps the database takes to get the results.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Scanning rows in the orders table to find matches.
• How many times: Once for each row until the matching customer_id is found or all rows are checked.

How Execution Grows With Input

As the number of orders grows, the time to scan grows too.

Input Size (n)	Approx. Operations
10	About 10 row checks
100	About 100 row checks
1000	About 1000 row checks

Pattern observation: The work grows roughly in direct proportion to the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows linearly with the number of rows in the table.

Common Mistake

[X] Wrong: "EXPLAIN ANALYZE just shows the query plan, not actual time."

[OK] Correct: EXPLAIN ANALYZE actually runs the query and shows real timing, so it reflects true cost.

Interview Connect

Knowing how to read EXPLAIN ANALYZE helps you understand query speed and efficiency, a useful skill for real database work and problem solving.

Self-Check

"What if we add an index on customer_id? How would the time complexity shown by EXPLAIN ANALYZE change?"