Join algorithms (nested loop, hash, merge) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Nested Loop Join

Which of the following best describes how a nested loop join works in PostgreSQL?

A It scans each row of the first table and for each row, scans the entire second table to find matching rows.

B It uses an index on both tables to directly find matching rows without scanning.

C It creates a hash table from the smaller table and probes it with rows from the larger table.

D It sorts both tables and then merges them by matching sorted keys.

💡 Hint

Think about the simplest join method that compares every row from one table to every row of the other.

A nested loop join works by taking each row from the first table and scanning the entire second table to find matches. This is simple but can be slow for large tables.

❓ query_result

intermediate

Output of Hash Join Query

Given two tables employees and departments, which query output matches the use of a hash join in PostgreSQL?

Assume employees has 1000 rows and departments has 10 rows.

PostgreSQL

EXPLAIN (ANALYZE, BUFFERS) SELECT e.name, d.name FROM employees e JOIN departments d ON e.department_id = d.id;

A The query plan shows a Bitmap Heap Scan without any join operation.

B The query plan shows a Nested Loop Join scanning all rows of both tables.

C The query plan shows a Merge Join after sorting both tables on join keys.

D The query plan shows a Hash Join with a hash built on the smaller table 'departments'.

💡 Hint

Hash joins are efficient when one table is much smaller and can be hashed in memory.

PostgreSQL uses a hash join by building a hash table on the smaller table (departments) and then probes it with rows from the larger table (employees).

📝 Syntax

advanced

Correct SQL for Merge Join Usage

Which SQL query is most likely to trigger a merge join in PostgreSQL?

A SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id ORDER BY o.customer_id, c.id;

B SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.amount > 100;

C SELECT * FROM orders o LEFT JOIN customers c ON o.customer_id = c.id;

D SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

💡 Hint

Merge joins require both tables to be sorted on the join keys.

Merge join is efficient when both tables are sorted on the join keys. The query with ORDER BY on join keys encourages PostgreSQL to use merge join.

❓ optimization

advanced

Choosing the Best Join Algorithm

You have two tables: big_sales with 1 million rows and small_regions with 100 rows. Which join algorithm will PostgreSQL most likely choose for SELECT * FROM big_sales JOIN small_regions ON big_sales.region_id = small_regions.id; and why?

A Merge join, because it sorts both tables before joining.

B Nested loop join, because it scans each row of the big table and matches with the small table.

C Hash join, because it builds a hash table on the smaller table for fast lookups.

D Cross join, because it multiplies all rows from both tables.

💡 Hint

Think about which join is efficient when one table is very small.

Hash join is efficient here because PostgreSQL builds a hash table on the small table (small_regions) and probes it with the large table (big_sales), avoiding scanning the big table multiple times.

🔧 Debug

expert

Diagnosing Unexpected Join Algorithm

You wrote this query:

SELECT * FROM products p JOIN categories c ON p.category_id = c.id;

PostgreSQL uses a nested loop join instead of a hash or merge join, causing slow performance. Which of the following is the most likely reason?

A The query uses SELECT *, which disables hash and merge joins.

B There is no index on the join keys, so PostgreSQL cannot efficiently build a hash or merge join.

C The tables are too small, so PostgreSQL always uses nested loop joins regardless of indexes.

D PostgreSQL does not support hash or merge joins for JOIN operations.

💡 Hint

Indexes help PostgreSQL choose efficient join methods.

Without indexes on join keys, PostgreSQL may fall back to nested loop join because it cannot efficiently build hash tables or sort for merge joins.