WITH clause syntax in PostgreSQL - Time & Space Complexity

Time Complexity: WITH clause syntax

O(n)

Understanding Time Complexity

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

How does using WITH affect the work the database does?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '7 days'
)
SELECT customer_id, COUNT(*) FROM recent_orders GROUP BY customer_id;

This query finds orders from the last 7 days and counts how many each customer made.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Scanning the orders table to find recent orders.
• How many times: Once to filter recent orders, then once to group and count by customer.

How Execution Grows With Input

As the number of orders grows, the database must check more rows to find recent ones and then group them.

Input Size (n)	Approx. Operations
10	About 10 rows scanned and grouped
100	About 100 rows scanned and grouped
1000	About 1000 rows scanned and grouped

Pattern observation: The work grows roughly in direct proportion to the number of rows in the orders table.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows linearly as the number of orders increases.

Common Mistake

[X] Wrong: "The WITH clause runs the subquery only once and does not affect performance as data grows."

[OK] Correct: Although the WITH clause runs the subquery only once, it still must scan the entire orders table to filter recent orders, so the time grows with data size.

Interview Connect

Understanding how WITH clauses affect query time helps you write efficient queries and explain your reasoning clearly in interviews.

Self-Check

"What if we added an index on order_date? How would the time complexity change?"