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.