LOOP, WHILE, FOR iterations in PostgreSQL - Time & Space Complexity

Time Complexity: LOOP, WHILE, FOR iterations

O(n)

Understanding Time Complexity

When we use loops like LOOP, WHILE, or FOR in PostgreSQL, the time it takes to run depends on how many times the loop runs.

We want to understand how the total work grows as the number of loop cycles increases.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

DO $$
DECLARE
  counter INTEGER := 1;
BEGIN
  WHILE counter <= 1000 LOOP
    -- some simple operation
    counter := counter + 1;
  END LOOP;
END $$;
    

This code runs a WHILE loop that repeats a simple operation 1000 times.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The loop body runs once each time the counter increases.
• How many times: The loop runs exactly 1000 times in this example.

How Execution Grows With Input

As the number of loop cycles grows, the total work grows in the same way.

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

Pattern observation: If you double the number of loop cycles, the total work doubles too.

Final Time Complexity

Time Complexity: O(n)

This means the time to finish grows directly in proportion to how many times the loop runs.

Common Mistake

[X] Wrong: "The loop always takes the same time no matter how many times it runs."

[OK] Correct: Each loop cycle adds more work, so more cycles mean more total time.

Interview Connect

Understanding how loops affect time helps you explain how your code scales and shows you can think about efficiency clearly.

Self-Check

"What if we added a nested loop inside the existing loop? How would the time complexity change?"