CREATE VIEW syntax in PostgreSQL - Time & Space Complexity

Time Complexity: CREATE VIEW syntax

O(n)

Understanding Time Complexity

When we create a view in a database, it acts like a saved query. Understanding how the time to get results grows helps us know how efficient our view is.

We want to see how the time to get data from a view changes as the data grows.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

CREATE VIEW recent_orders AS
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date > CURRENT_DATE - INTERVAL '30 days';

This code creates a view that shows orders from the last 30 days.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Scanning the orders table rows to check the date condition.
• How many times: Once for each row in the orders table when the view is queried.

How Execution Grows With Input

As the number of orders grows, the database checks more rows to find those in the last 30 days.

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 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 get results grows linearly with the number of rows in the table.

Common Mistake

[X] Wrong: "Creating a view makes the query run faster because it stores results."

[OK] Correct: A view just saves the query, it does not store data. The database runs the query each time you ask the view, so the time depends on the underlying data size.

Interview Connect

Understanding how views work and their time cost shows you know how databases handle saved queries, a useful skill for real projects and interviews.

Self-Check

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