Query optimization with EXPLAIN in Supabase - Time & Space Complexity

Time Complexity: Query optimization with EXPLAIN

O(n)

Understanding Time Complexity

When we run database queries, some take longer than others. Using EXPLAIN helps us see how the database plans to run a query.

We want to understand how the time to run a query grows as the data size grows.

Scenario Under Consideration

Analyze the time complexity of a SELECT query with EXPLAIN.

const { data, error } = await supabase.rpc('explain_query', {
  query: 'EXPLAIN SELECT * FROM users WHERE age > 30'
});

This runs EXPLAIN on a query to see how the database will execute it.

Identify Repeating Operations

Look for repeated actions that affect time.

• Primary operation: The database scans or indexes rows to find matches.
• How many times: Depends on number of rows in the table scanned or indexed.

How Execution Grows With Input

As the number of rows grows, the database work grows too.

Input Size (n)	Approx. Api Calls/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 scanned.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows roughly in a straight line with the number of rows checked.

Common Mistake

[X] Wrong: "EXPLAIN makes the query run faster by itself."

[OK] Correct: EXPLAIN only shows the plan; it does not speed up the query execution.

Interview Connect

Understanding how query time grows helps you design better databases and write faster queries, a useful skill in many cloud roles.

Self-Check

"What if we add an index on the age column? How would the time complexity change?"