Query optimization with EXPLAIN in Supabase - Step-by-Step Execution

Process Flow - Query optimization with EXPLAIN

Write SQL Query

↓

Run EXPLAIN on Query

↓

Get Query Plan

↓

Analyze Steps

↓

Identify Bottlenecks

↓

Rewrite Query or Add Index

↓

Test Optimized Query

↓

Repeat if Needed

This flow shows how to use EXPLAIN to see how a query runs, find slow parts, and improve it step-by-step.

Execution Sample

Supabase

EXPLAIN SELECT * FROM users WHERE age > 30;

This command shows the query plan for selecting users older than 30.

Process Table

Step	Action	Query Plan Detail	Result/Output
1	Parse SQL	Check syntax and structure	Valid query
2	Generate Plan	Scan 'users' table with filter age > 30	Seq Scan or Index Scan
3	Estimate Cost	Calculate rows and cost	Cost: 10..1000 rows: 50
4	Output Plan	Show plan tree	Seq Scan on users Filter: age > 30
5	Analyze Plan	Look for full table scan or missing index	Full scan detected, consider index
6	Add Index	Create index on age column	Index created
7	Re-run EXPLAIN	Check new plan	Index Scan on users using age_idx
8	Compare Costs	New cost lower than before	Cost: 5..200 rows: 50
9	End	Optimization complete	Query runs faster with index

💡 Optimization ends after comparing costs and confirming improved query plan.

Status Tracker

Variable	Start	After Step 5	After Step 7	Final
Query Plan Type	Not generated	Seq Scan (full table scan)	Index Scan (using age_idx)	Index Scan (using age_idx)
Estimated Cost	N/A	10..1000	5..200	5..200
Rows Estimated	N/A	50	50	50
Index Present	No	No	Yes	Yes

Key Moments - 3 Insights

Why does EXPLAIN show a sequential scan before adding an index?

Because without an index, the database must check every row to find matches, as shown in step 4 of the execution_table.

How do we know the query got faster after optimization?

The estimated cost decreased from 10..1000 to 5..200 after adding the index, as seen in steps 3 and 8.

What does the 'Filter: age > 30' mean in the plan?

It means the database only keeps rows where age is greater than 30 during the scan, shown in step 4.

Visual Quiz - 3 Questions

Look at the execution_table, what type of scan is used before adding the index?

A Index Scan

B Bitmap Scan

C Seq Scan

D No Scan

💡 Hint

Check Step 4 in the execution_table where the plan shows 'Seq Scan on users'.

At which step does the query plan change to use the index?

A Step 5

B Step 7

C Step 3

D Step 9

💡 Hint

Look at Step 7 in the execution_table where it says 'Index Scan on users using age_idx'.

If the index was not created, how would the estimated cost change?

A It would stay high like 10..1000

B It would become zero

C It would decrease to 5..200

D It would be unpredictable

💡 Hint

Refer to Step 3 and Step 8 in the execution_table comparing costs before and after index creation.

Concept Snapshot

Use EXPLAIN before running a query to see its plan.
Look for full table scans as a sign of slow queries.
Add indexes on columns used in filters to speed up scans.
Re-run EXPLAIN to confirm the plan uses the index.
Lower estimated cost means better performance.
Repeat until query is efficient.

Full Transcript

Query optimization with EXPLAIN involves running the EXPLAIN command on a SQL query to see how the database plans to execute it. The plan shows steps like scanning tables and filtering rows. Initially, without indexes, the database may do a full table scan, which is slow. By analyzing the plan, you can identify bottlenecks such as missing indexes. Adding an index on the filtered column helps the database use a faster index scan. Re-running EXPLAIN shows the improved plan with lower estimated cost, meaning the query will run faster. This process can be repeated to further optimize queries.