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.