CASE expression in PostgreSQL - Time & Space Complexity

Time Complexity: CASE expression in PostgreSQL

O(n)

Understanding Time Complexity

We want to understand how the time it takes to run a CASE expression changes as the data grows.

How does the number of rows affect the work done by the CASE expression?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT id,
       CASE 
         WHEN score >= 90 THEN 'A'
         WHEN score >= 80 THEN 'B'
         WHEN score >= 70 THEN 'C'
         ELSE 'F'
       END AS grade
FROM students;

This query assigns a grade to each student based on their score using a CASE expression.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The CASE expression is evaluated once for each row in the students table.
• How many times: Exactly as many times as there are rows (students) in the table.

How Execution Grows With Input

Each student row requires checking the CASE conditions once.

Input Size (n)	Approx. Operations
10	10 CASE evaluations
100	100 CASE evaluations
1000	1000 CASE evaluations

Pattern observation: The work grows directly with the number of rows; doubling rows doubles the work.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the CASE expression grows linearly with the number of rows.

Common Mistake

[X] Wrong: "CASE expressions run in constant time no matter how many rows there are."

[OK] Correct: The CASE expression runs once per row, so more rows mean more evaluations and more time.

Interview Connect

Understanding how CASE expressions scale helps you explain query performance clearly and shows you know how databases handle row-by-row logic.

Self-Check

"What if the CASE expression had nested CASE statements inside? How would that affect the time complexity?"