Query optimization with EXPLAIN in Supabase

Introduction

We use EXPLAIN to see how a database runs a query. This helps us make queries faster and use less resources.

When a query is running slowly and you want to find out why.

Before running a complex query to check if it will be efficient.

When you want to compare two ways of writing a query to see which is better.

To understand how the database uses indexes and joins in your query.

When optimizing your app to reduce database costs and improve speed.

Syntax

Supabase

EXPLAIN [ANALYZE] SELECT column1, column2 FROM table WHERE condition;

Use EXPLAIN to get the query plan without running the query.

Add ANALYZE to run the query and get actual timing and row counts.

Examples

Shows the plan for selecting users older than 30 without running the query.

Supabase

EXPLAIN SELECT * FROM users WHERE age > 30;

Runs the query and shows real execution details for orders with pending status.

Supabase

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

Shows how the database will get products cheaper than 100 and sort them.

Supabase

EXPLAIN SELECT name FROM products WHERE price < 100 ORDER BY price;

Sample Program

This runs the query to find users who signed up after January 1, 2023, and shows detailed timing and steps the database took.

Supabase

EXPLAIN ANALYZE SELECT id, email FROM users WHERE signup_date > '2023-01-01';

Important Notes

EXPLAIN output shows steps like Seq Scan (full table scan) or Index Scan (using an index).

Look for high cost or time values to find slow parts of your query.

Using indexes can reduce cost and speed up queries.

Summary

EXPLAIN helps you see how the database runs your query.

Use EXPLAIN ANALYZE to get real execution details.

Optimizing queries saves time and resources.