Dynamic SQL with EXECUTE in PostgreSQL - Time & Space Complexity

Time Complexity: Dynamic SQL with EXECUTE

O(n)

Understanding Time Complexity

When using dynamic SQL with EXECUTE in PostgreSQL, it's important to understand how the time to run the query changes as the input grows.

We want to know how the number of operations grows when the dynamic query runs on larger data.

Scenario Under Consideration

Analyze the time complexity of the following dynamic SQL snippet.

DECLARE
  sql_text TEXT;
  result RECORD;
BEGIN
  sql_text := 'SELECT * FROM users WHERE age > ' || min_age;
  FOR result IN EXECUTE sql_text LOOP
    -- process each row
  END LOOP;
END;
    

This code builds a query string based on a variable and then runs it to fetch rows from the users table where age is greater than a given minimum.

Identify Repeating Operations

Look for repeated actions that affect time.

• Primary operation: Looping over each row returned by the dynamic query.
• How many times: Once for each row matching the condition in the users table.

How Execution Grows With Input

The number of rows returned depends on how many users have age greater than the given minimum.

Input Size (n)	Approx. Operations
10	About 10 rows processed
100	About 100 rows processed
1000	About 1000 rows processed

Pattern observation: As the number of matching rows grows, the loop runs more times, increasing work roughly in direct proportion.

Final Time Complexity

Time Complexity: O(n)

This means the time grows linearly with the number of rows returned by the dynamic query.

Common Mistake

[X] Wrong: "Dynamic SQL always runs in constant time because it just builds a string and executes it once."

[OK] Correct: The time depends on how many rows the query returns and processes, not just on building the string.

Interview Connect

Understanding how dynamic SQL execution time grows helps you explain performance considerations clearly and shows you can reason about real database workloads.

Self-Check

"What if the dynamic query included a JOIN with another large table? How would that affect the time complexity?"