Why views matter in PostgreSQL - Performance Analysis

Time Complexity: Why views matter in PostgreSQL

O(n)

Understanding Time Complexity

When using views in PostgreSQL, it's important to understand how the time to get results changes as data grows.

We want to know how using a view affects the speed of queries as the database gets bigger.

Scenario Under Consideration

Analyze the time complexity of querying a simple view.

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

SELECT * FROM recent_orders WHERE customer_id = 123;
    

This view selects orders from the last 30 days. The query fetches recent orders for one customer.

Identify Repeating Operations

Look at what repeats when running the query.

• Primary operation: Scanning the orders table to find rows matching the date and customer filters.
• How many times: Once per query, but the scan checks many rows depending on table size.

How Execution Grows With Input

As the orders table grows, the number of rows to check grows too.

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

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

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows linearly with the number of rows in the orders table.

Common Mistake

[X] Wrong: "Using a view makes queries run instantly regardless of data size."

[OK] Correct: A view is just a saved query. It still runs on the underlying data, so bigger tables mean more work.

Interview Connect

Understanding how views affect query time helps you explain database design choices clearly and confidently.

Self-Check

"What if the orders table had an index on order_date? How would that change the time complexity?"