Why utility functions matter in PostgreSQL - Performance Analysis

Time Complexity: Why utility functions matter

O(n)

Understanding Time Complexity

Utility functions help us reuse code and keep things organized in databases.

We want to see how using these functions affects the time it takes to run queries.

Scenario Under Consideration

Analyze the time complexity of this utility function and its use in a query.

CREATE OR REPLACE FUNCTION get_discount(price numeric, rate numeric) RETURNS numeric AS $$
BEGIN
  RETURN price * rate;
END;
$$ LANGUAGE plpgsql;

SELECT product_id, get_discount(price, 0.1) AS discount FROM products;

This function calculates a discount for each product price, then the query applies it to all products.

Identify Repeating Operations

Look for repeated actions in the query and function.

• Primary operation: Calling the utility function once per product row.
• How many times: Equal to the number of rows in the products table.

How Execution Grows With Input

Each product causes one function call, so more products mean more calls.

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

Pattern observation: The work grows directly with the number of products.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows in a straight line as the number of products increases.

Common Mistake

[X] Wrong: "Using a utility function makes the query slower by a lot because it adds extra steps."

[OK] Correct: The function runs once per row, just like any calculation would, so it doesn't add hidden loops or big delays.

Interview Connect

Understanding how utility functions affect query time helps you write clear and efficient database code, a useful skill in many real projects.

Self-Check

"What if the utility function called another function inside it? How would that change the time complexity?"