VARIADIC parameters in PostgreSQL - Time & Space Complexity

Time Complexity: VARIADIC parameters

O(n)

Understanding Time Complexity

When using VARIADIC parameters in PostgreSQL functions, it's important to understand how the function's execution time changes as you pass more arguments.

We want to know how the number of input values affects the work the database does.

Scenario Under Consideration

Analyze the time complexity of the following PostgreSQL function using VARIADIC parameters.

CREATE FUNCTION sum_all(VARIADIC nums int[]) RETURNS int AS $$
  DECLARE
    total int := 0;
    n int;
  BEGIN
    FOREACH n IN ARRAY nums LOOP
      total := total + n;
    END LOOP;
    RETURN total;
  END;
$$ LANGUAGE plpgsql;

This function sums all integers passed as a variable-length list using VARIADIC.

Identify Repeating Operations

Look for repeated actions inside the function.

• Primary operation: Looping through each number in the input array.
• How many times: Once for each number passed to the function.

How Execution Grows With Input

As you add more numbers to sum, the function does more work.

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

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

Final Time Complexity

Time Complexity: O(n)

This means the time to run the function grows linearly with the number of arguments passed.

Common Mistake

[X] Wrong: "Using VARIADIC means the function runs in constant time no matter how many arguments."

[OK] Correct: The function still processes each argument one by one, so more inputs mean more work.

Interview Connect

Understanding how variable-length inputs affect performance shows you can reason about real-world database functions and their efficiency.

Self-Check

"What if the function used a recursive approach instead of a loop? How would the time complexity change?"