Sequential scan vs index scan in PostgreSQL - Practice Questions

Challenge - 5 Problems

❓ query_result

intermediate

Identify the scan type used by this query

Given a table employees with an index on the department_id column, what type of scan will PostgreSQL most likely use for this query?

SELECT * FROM employees WHERE department_id = 5;

A Sequential scan

B Hash scan

C Bitmap heap scan

D Index scan

💡 Hint

Think about how PostgreSQL uses indexes to find rows matching a condition.

When a query filters on a column with an index, PostgreSQL usually uses an index scan to quickly find matching rows instead of scanning the whole table.

🧠 Conceptual

intermediate

When does PostgreSQL prefer sequential scan over index scan?

Which situation below best explains when PostgreSQL will choose a sequential scan instead of an index scan?

A When the query filters on a unique column

B When the table has an index on the filtered column

C When the table is very small or the query returns most rows

D When the query uses an ORDER BY clause

💡 Hint

Think about the cost of using an index versus reading the whole table.

If the table is small or the query returns a large portion of rows, scanning the whole table sequentially is often faster than using an index.

📝 Syntax

advanced

Identify the error in this EXPLAIN query

Which option shows the correct way to get the query plan including whether a sequential or index scan is used for SELECT * FROM products WHERE price < 100;?

A EXPLAIN SELECT * FROM products WHERE price < 100;

B EXPLAIN ANALYZE SELECT * FROM products WHERE price < 100;

C EXPLAIN PLAN FOR SELECT * FROM products WHERE price < 100;

D EXPLAIN QUERY SELECT * FROM products WHERE price < 100;

💡 Hint

One option runs the query and shows actual timing and scan type.

EXPLAIN ANALYZE runs the query and shows the actual execution plan including scan types and timing. Other options are invalid syntax or incomplete.

❓ optimization

advanced

Optimize query to use index scan instead of sequential scan

Given a large table orders with an index on customer_id, this query uses a sequential scan:

SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'USA');

Which change is most likely to make PostgreSQL use an index scan on customer_id?

A Rewrite the query to use a JOIN instead of a subquery

B Add an index on the <code>country</code> column in <code>customers</code>

C Increase work_mem setting in PostgreSQL

D Use a sequential scan hint in the query

💡 Hint

Sometimes rewriting queries helps the planner choose better plans.

Rewriting the query with a JOIN can help PostgreSQL better use indexes on customer_id by flattening the query and making the filter clearer.

🔧 Debug

expert

Diagnose why index scan is not used

A table sales has an index on sale_date. The query below always uses sequential scan:

SELECT * FROM sales WHERE sale_date > '2023-01-01';

What is the most likely reason PostgreSQL does not use the index?

A The statistics for the table are outdated and need to be refreshed

B The sale_date column is not indexed

C The query uses an unsupported operator for the index

D The table is empty

💡 Hint

PostgreSQL relies on statistics to decide query plans.

If statistics are outdated, PostgreSQL may wrongly estimate that a sequential scan is cheaper. Running ANALYZE updates statistics and can fix this.