AGE function for differences in PostgreSQL - Time & Space Complexity

Time Complexity: AGE function for differences

O(n)

Understanding Time Complexity

We want to understand how the time to calculate age differences grows as we use the AGE function more times.

How does the work change when we ask for many age calculations?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT id, AGE(current_date, birth_date) AS age_diff
FROM users;
    

This query calculates the age difference between today and each user's birth date.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: AGE function calculation for each row in the users table.
• How many times: Once per user row, so as many times as there are users.

How Execution Grows With Input

Each additional user means one more AGE calculation. The work grows steadily as the number of users grows.

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

Pattern observation: The number of operations grows directly with the number of rows.

Final Time Complexity

Time Complexity: O(n)

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

Common Mistake

[X] Wrong: "The AGE function runs once and applies to all rows at the same time."

[OK] Correct: Each row needs its own AGE calculation, so the function runs repeatedly, not just once.

Interview Connect

Understanding how functions like AGE scale with data size helps you write efficient queries and explain their behavior clearly.

Self-Check

"What if we added a WHERE clause to filter only recent users? How would the time complexity change?"