Query optimization with EXPLAIN in Supabase - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding EXPLAIN output for a simple SELECT

You run the following query on a Supabase PostgreSQL database:

SELECT * FROM users WHERE age > 30;

Using EXPLAIN on this query, which part of the output tells you if an index is used?

A The 'Sort' keyword indicates an index is used.

B The 'Aggregate' keyword indicates an index is used.

C The 'Seq Scan' keyword indicates an index is used.

D The 'Index Scan' keyword indicates an index is used.

💡 Hint

Look for keywords that describe how the database reads rows.

'Index Scan' means the database uses an index to find rows quickly. 'Seq Scan' means it reads the whole table without using an index.

❓ service_behavior

intermediate

Effect of adding an index on EXPLAIN output

You have a table orders with millions of rows. Initially, you run:

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

The output shows a 'Seq Scan'. After adding an index on customer_id, you run the same EXPLAIN again. What change do you expect in the output?

A The output will show 'Aggregate' because of the index.

B The output will show 'Index Scan' indicating the index is used.

C The output will show 'Hash Join' instead of 'Seq Scan'.

D The output will still show 'Seq Scan' because indexes don't affect EXPLAIN.

💡 Hint

Think about how indexes help find rows faster.

Adding an index on customer_id allows the database to use 'Index Scan' instead of scanning the whole table.

❓ Architecture

advanced

Choosing the best index type for query optimization

You want to optimize a query filtering by a text column email in a Supabase PostgreSQL database. The query is:

SELECT * FROM users WHERE email LIKE 'john%';

Which index type is best to speed up this query and why?

A A B-tree index, because it supports prefix matching with LIKE.

B A Hash index, because it is faster for all LIKE queries.

C A GIN index, because it supports full-text search and LIKE efficiently.

D No index helps with LIKE queries.

💡 Hint

Consider how the pattern 'john%' works with different index types.

B-tree indexes support prefix matching like 'john%'. Hash indexes do not support range or pattern queries. GIN indexes are for full-text search, not simple prefix LIKE.

❓ security

advanced

Security implications of EXPLAIN in production

Allowing users to run EXPLAIN on queries in a production Supabase database can expose sensitive information. What is a key security risk of unrestricted EXPLAIN access?

A Users can see the exact data stored in tables.

B EXPLAIN can modify data if misused.

C Users can learn the database schema and query patterns, aiding attacks.

D EXPLAIN disables database encryption.

💡 Hint

Think about what EXPLAIN reveals about the database structure.

EXPLAIN shows query plans, including table names, indexes, and join methods, which can help attackers understand the database design.

✅ Best Practice

expert

Interpreting EXPLAIN ANALYZE for query tuning

You run this command in Supabase:

EXPLAIN ANALYZE SELECT * FROM products WHERE price > 100;

The output shows:

Seq Scan on products  (cost=0.00..1000.00 rows=500 width=50) (actual time=0.01..500.00 rows=1000 loops=1)

What does the large difference between estimated rows (500) and actual rows (1000) suggest?

A The statistics are outdated or inaccurate, causing poor estimates.

B The query plan is accurate and needs no change.

C The index is being used correctly.

D The query is using a join instead of a scan.

💡 Hint

Consider what happens if the database guesses wrong about data distribution.

If estimated rows differ greatly from actual rows, the database statistics may be stale, leading to inefficient query plans.

Practice

1. What is the main purpose of using EXPLAIN in Supabase queries?

easy

A. To execute the query faster automatically

B. To see how the database plans to run your query

C. To delete data from the database

D. To create new tables in the database

Solution

1. Step 1: Understand the role of EXPLAIN

   EXPLAIN shows the query plan, which is how the database intends to run the query.

2. Step 2: Differentiate EXPLAIN from other commands

   It does not execute or change data; it only provides information about query execution.

3. Final Answer:

   To see how the database plans to run your query -> Option B

4. Quick Check:

   EXPLAIN = query plan info [OK]

Hint: EXPLAIN shows query plans, not data changes [OK]

Common Mistakes:

• Thinking EXPLAIN runs the query faster
• Confusing EXPLAIN with data modification commands
• Assuming EXPLAIN creates or deletes tables

2. Which of the following is the correct syntax to get detailed execution info of a query in Supabase?

easy

A. EXPLAIN ANALYZE SELECT * FROM users;

B. EXPLAIN SELECT * FROM users;

C. ANALYZE EXPLAIN SELECT * FROM users;

D. SELECT EXPLAIN ANALYZE * FROM users;

Solution

1. Step 1: Recall the syntax for detailed query execution

   To get real execution details, use EXPLAIN ANALYZE before the query.

2. Step 2: Check each option's syntax

   EXPLAIN ANALYZE SELECT * FROM users; correctly places EXPLAIN ANALYZE before the SELECT statement; others are incorrect or invalid.

3. Final Answer:

   EXPLAIN ANALYZE SELECT * FROM users; -> Option A

4. Quick Check:

   EXPLAIN ANALYZE = detailed execution [OK]

Hint: Use EXPLAIN ANALYZE before SELECT for details [OK]

Common Mistakes:

• Swapping EXPLAIN and ANALYZE order
• Placing EXPLAIN inside SELECT
• Using ANALYZE without EXPLAIN

3. Given the EXPLAIN output shows a sequential scan on a large table, what is the likely effect on query performance?

medium

A. The query will be very fast because it uses an index

B. The query will fail due to missing indexes

C. The query will only scan a small part of the table

D. The query might be slow because it reads the whole table

Solution

1. Step 1: Understand sequential scan meaning

   A sequential scan reads every row in the table, which can be slow for large tables.

2. Step 2: Compare with index scan

   Index scans read only relevant rows, making queries faster; sequential scans do not use indexes.

3. Final Answer:

   The query might be slow because it reads the whole table -> Option D

4. Quick Check:

   Seq scan = full table read, slower [OK]

Hint: Seq scan reads all rows, slowing large queries [OK]

Common Mistakes:

• Assuming sequential scan uses indexes
• Thinking sequential scan reads only part of table
• Confusing failure with slow performance

4. You run EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123; but notice the query is slow. What is a likely cause and fix?

medium

A. No index on customer_id; add an index on that column

B. The query syntax is wrong; rewrite the SELECT statement

C. The database is offline; restart the server

D. The table orders does not exist; create it first

Solution

1. Step 1: Analyze slow query cause

   Slow queries filtering by a column often mean missing indexes on that column.

2. Step 2: Suggest fix based on EXPLAIN ANALYZE

   Adding an index on customer_id helps the database find rows faster, improving speed.

3. Final Answer:

   No index on customer_id; add an index on that column -> Option A

4. Quick Check:

   Missing index = slow filter; add index [OK]

Hint: Slow filtered query? Check indexes on filter columns [OK]

Common Mistakes:

• Assuming syntax error causes slowness
• Restarting server without checking query plan
• Thinking missing table causes slow query

5. You want to optimize a complex query joining two large tables. The EXPLAIN ANALYZE shows nested loops causing slow performance. What is the best approach to improve it?

hard

A. Increase the database server's CPU without changing the query

B. Remove all indexes to force sequential scans

C. Add indexes on join columns and rewrite query to use explicit JOIN syntax

D. Split the query into multiple smaller queries without joins

Solution

1. Step 1: Understand nested loops impact

   Nested loops join can be slow on large tables without proper indexes on join columns.

2. Step 2: Apply optimization techniques

   Adding indexes on join columns helps the database find matching rows faster. Using explicit JOIN syntax clarifies the query for optimization.

3. Final Answer:

   Add indexes on join columns and rewrite query to use explicit JOIN syntax -> Option C

4. Quick Check:

   Indexes + clear JOIN = faster joins [OK]

Hint: Index join columns and use explicit JOIN for speed [OK]

Common Mistakes:

• Removing indexes causing slower scans
• Splitting queries losing join benefits
• Relying only on hardware upgrades