FOREACH for array iteration in PostgreSQL - Time & Space Complexity

Time Complexity: FOREACH for array iteration

O(n)

Understanding Time Complexity

When using FOREACH to loop over an array in PostgreSQL, it's important to understand how the time taken grows as the array gets bigger.

We want to know how the number of steps changes when the array length increases.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

DECLARE
  my_array integer[] := ARRAY[1,2,3,4,5];
  element integer;
BEGIN
  FOREACH element IN ARRAY my_array LOOP
    RAISE NOTICE 'Element: %', element;
  END LOOP;
END;

This code loops through each element in an integer array and prints it.

Identify Repeating Operations

• Primary operation: Looping through each element of the array once.
• How many times: Exactly once for each element in the array.

How Execution Grows With Input

As the array gets longer, the number of steps grows directly with the number of elements.

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

Pattern observation: The steps increase in a straight line as the array size grows.

Final Time Complexity

Time Complexity: O(n)

This means the time to complete the loop grows directly with the number of elements in the array.

Common Mistake

[X] Wrong: "FOREACH loops run in constant time no matter the array size."

[OK] Correct: Each element must be visited once, so the time grows with the array length, not fixed.

Interview Connect

Understanding how looping over arrays scales helps you explain performance clearly in interviews and shows you grasp how data size affects query speed.

Self-Check

"What if we nested a FOREACH loop inside another FOREACH loop over the same array? How would the time complexity change?"