FILTER clause for conditional aggregation in PostgreSQL - Time & Space Complexity

Time Complexity: FILTER clause for conditional aggregation

O(n)

Understanding Time Complexity

We want to understand how the time to run a query with a FILTER clause changes as the data grows.

Specifically, how does filtering inside aggregation affect performance?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT department_id,
       COUNT(*) FILTER (WHERE status = 'active') AS active_count,
       COUNT(*) FILTER (WHERE status = 'inactive') AS inactive_count
FROM employees
GROUP BY department_id;
    

This query counts active and inactive employees per department using FILTER inside aggregation.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Scanning each row in the employees table once.
• How many times: Once per row, checking conditions and grouping.

How Execution Grows With Input

As the number of employees grows, the query checks each row once to decide which count to increase.

Input Size (n)	Approx. Operations
10	About 10 row checks
100	About 100 row checks
1000	About 1000 row checks

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

Final Time Complexity

Time Complexity: O(n)

This means the time grows linearly with the number of rows in the table.

Common Mistake

[X] Wrong: "Using FILTER makes the query run multiple times for each condition."

[OK] Correct: The database scans each row once and applies all FILTER conditions during that scan, so it does not repeat the whole query multiple times.

Interview Connect

Understanding how conditional aggregation scales helps you write efficient queries and explain your reasoning clearly in interviews.

Self-Check

"What if we replaced FILTER with CASE statements inside COUNT? How would the time complexity change?"