Common query optimization patterns in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of Index Usage on Query Performance

Consider a table employees with a million rows and an index on the department_id column. Which query will most likely run faster due to index usage?

A SELECT * FROM employees WHERE department_id = 5;

B SELECT * FROM employees WHERE LOWER(name) = 'john';

C SELECT * FROM employees WHERE salary > 50000;

D SELECT * FROM employees WHERE department_id + 1 = 6;

💡 Hint

Indexes speed up queries that use the indexed column directly in conditions without transformations.

Query C uses the indexed column department_id directly in the WHERE clause, allowing PostgreSQL to use the index efficiently. Query A filters on salary, which is not indexed. Query B applies a function LOWER() on name, which prevents index usage unless a functional index exists. Query D applies arithmetic on department_id, which disables index usage.

📝 Syntax

intermediate

Correct Use of EXISTS for Efficient Filtering

Which of the following queries correctly uses EXISTS to efficiently check if an employee has any projects assigned in the projects table?

A SELECT * FROM employees e WHERE EXISTS (SELECT p.* FROM projects p WHERE p.employee_id = e.id);

B SELECT * FROM employees e WHERE EXISTS (SELECT * FROM projects p WHERE p.employee_id = e.id);

C SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM projects p WHERE p.employee_id = e.id);

D SELECT * FROM employees e WHERE EXISTS (SELECT COUNT(*) FROM projects p WHERE p.employee_id = e.id);

💡 Hint

EXISTS only checks for presence, so selecting 1 is enough and efficient.

Option C uses SELECT 1 which is the standard and efficient way to use EXISTS. Option C is valid but less efficient because SELECT * fetches all columns unnecessarily. Option C selects all columns explicitly, which is inefficient. Option C uses COUNT(*) inside EXISTS, which is incorrect because SELECT COUNT(*) always returns exactly one row, making the EXISTS always true regardless of matches.

🧠 Conceptual

advanced

Understanding the Impact of JOIN Order on Query Performance

In PostgreSQL, which statement about the order of tables in a JOIN clause is true regarding query optimization?

A The query planner can reorder JOINs regardless of the written order to optimize performance.

B The order of tables in the JOIN clause always determines the join order used by the query planner.

C JOIN order cannot be changed by the planner if explicit JOIN hints are not used.

D The first table in the JOIN clause is always used as the driving table in the execution plan.

💡 Hint

PostgreSQL's planner is smart and can reorder joins for best performance.

PostgreSQL's query planner analyzes all possible join orders and chooses the most efficient one, regardless of the order written in the SQL query. Therefore, the written order does not always dictate execution order. Options B, C, and D are incorrect because the planner can reorder joins freely unless specific join hints or constraints are applied.

🔧 Debug

advanced

Identifying the Cause of Slow Query with Subquery

A query uses a subquery in the WHERE clause to filter rows, but it runs very slowly on a large table. Which of the following is the most likely cause?

A The subquery uses an index, so it should be fast regardless of size.

B The subquery is correlated and runs once per row, causing many executions.

C The main query uses SELECT *, which slows down the subquery.

D The subquery is uncorrelated and runs only once, so it cannot cause slowness.

💡 Hint

Correlated subqueries run repeatedly for each row in the outer query.

Correlated subqueries depend on the outer query row and execute repeatedly, which can cause slow performance on large tables. Option B is incorrect because index usage alone does not guarantee speed if the subquery runs many times. Option B is unrelated to subquery performance. Option B is incorrect because uncorrelated subqueries run once and are usually efficient.

❓ optimization

expert

Optimizing Aggregation Queries with Partial Indexes

You have a large orders table with a status column. You often run this query:

SELECT COUNT(*) FROM orders WHERE status = 'completed';

Which optimization will most improve performance?

A Rewrite the query to use <code>GROUP BY status</code> instead of WHERE.

B Create a full index on the <code>status</code> column for all rows.

C Add a materialized view that stores the count of completed orders and refresh it periodically.

D Create a partial index on <code>status</code> for rows where <code>status = 'completed'</code>.

💡 Hint

Partial indexes index only a subset of rows, making lookups faster for specific conditions.

Partial indexes index only rows matching a condition, so a partial index on status = 'completed' speeds up queries filtering on that status. Option D creates a full index, which is larger and less efficient for this specific query. Option D adds complexity and maintenance overhead. Option D changes query logic but does not optimize performance.

Practice

1. Which of the following is the best reason to create an index on a column in PostgreSQL?

easy

A. To speed up searches on that column

B. To reduce the size of the database

C. To automatically backup the data

D. To encrypt the data in that column

Solution

1. Step 1: Understand what an index does

   An index helps the database find rows faster by creating a quick lookup structure.

2. Step 2: Match the purpose to the options

   Only speeding up searches matches the purpose of an index; other options are unrelated.

3. Final Answer:

   To speed up searches on that column -> Option A

4. Quick Check:

   Index = Speed up search [OK]

Hint: Indexes speed up searches, not storage or encryption [OK]

Common Mistakes:

• Thinking indexes reduce database size
• Confusing indexes with backups
• Assuming indexes encrypt data

2. Which of the following is the correct syntax to check the query plan in PostgreSQL?

easy

A. EXPLAIN SELECT * FROM users;

B. DESCRIBE SELECT * FROM users;

C. PLAN SELECT * FROM users;

D. SHOW PLAN SELECT * FROM users;

Solution

1. Step 1: Recall the command to view query plans

   PostgreSQL uses EXPLAIN to show how it will run a query.

2. Step 2: Compare options to the correct command

   Only EXPLAIN SELECT * FROM users; is valid syntax for query plans.

3. Final Answer:

   EXPLAIN SELECT * FROM users; -> Option A

4. Quick Check:

   EXPLAIN = Query plan check [OK]

Hint: Use EXPLAIN before your query to see the plan [OK]

Common Mistakes:

• Using SHOW PLAN which is invalid
• Trying PLAN or DESCRIBE which are not PostgreSQL commands
• Missing the EXPLAIN keyword

3. Consider the query:
SELECT id, name FROM employees WHERE department = 'Sales';
Which optimization pattern does this query follow?

medium

A. Using a JOIN to combine tables

B. Using ORDER BY to sort results

C. Using a subquery to filter data

D. Selecting only needed columns instead of *

Solution

1. Step 1: Analyze the SELECT clause

   The query selects only 'id' and 'name', not all columns with '*'.

2. Step 2: Identify the optimization pattern

   Selecting only needed columns reduces data transfer and improves speed.

3. Final Answer:

   Selecting only needed columns instead of * -> Option D

4. Quick Check:

   Selective columns = Better performance [OK]

Hint: Avoid SELECT *; pick only columns you need [OK]

Common Mistakes:

• Confusing JOIN usage with column selection
• Thinking ORDER BY is always an optimization
• Assuming subqueries are used here

4. You have this query:
SELECT * FROM orders WHERE order_date = '2023-01-01';
It runs slowly. Which fix will likely improve performance?

medium

A. Change SELECT * to SELECT COUNT(*)

B. Add an index on the order_date column

C. Remove the WHERE clause

D. Use GROUP BY order_date

Solution

1. Step 1: Identify the cause of slowness

   Query filters on order_date but may scan all rows without an index.

2. Step 2: Apply optimization by indexing

   Adding an index on order_date lets PostgreSQL find matching rows faster.

3. Final Answer:

   Add an index on the order_date column -> Option B

4. Quick Check:

   Index on filter column = Faster query [OK]

Hint: Index columns used in WHERE for faster filtering [OK]

Common Mistakes:

• Removing WHERE loses filtering purpose
• Changing SELECT * to COUNT(*) changes result, not speed
• Using GROUP BY without aggregation is incorrect

5. You want to optimize a query that joins two large tables on a column and filters by a date range. Which combination of patterns will best improve performance?

hard

A. Use subqueries instead of JOINs; do not filter by date

B. Select all columns with *; avoid indexes to save space

C. Create indexes on join columns and filter columns; use EXPLAIN to check plan

D. Add ORDER BY on join column; remove WHERE clause

Solution

1. Step 1: Identify key optimization needs

   Joining large tables and filtering by date needs indexes on join and filter columns.

2. Step 2: Use EXPLAIN to verify query plan

   Checking the plan helps confirm indexes are used and query is efficient.

3. Final Answer:

   Create indexes on join columns and filter columns; use EXPLAIN to check plan -> Option C

4. Quick Check:

   Indexes + EXPLAIN = Best optimization [OK]

Hint: Index join and filter columns; verify with EXPLAIN [OK]

Common Mistakes:

• Selecting all columns wastes resources
• Avoiding indexes slows queries
• Removing WHERE loses filtering benefits