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.

Practice

1. What does a sequential scan do in PostgreSQL?

easy

A. Reads only the first 10 rows of a table

B. Uses an index to find specific rows quickly

C. Deletes rows based on a condition

D. Reads every row in the table one by one

Solution

1. Step 1: Understand sequential scan behavior

   A sequential scan reads all rows in the table from start to end without using any index.

2. Step 2: Compare with other options

   Using an index is an index scan, reading only first 10 rows or deleting rows are unrelated actions.

3. Final Answer:

   Reads every row in the table one by one -> Option D

4. Quick Check:

   Sequential scan = full table read [OK]

Hint: Sequential scan reads all rows, no index used [OK]

Common Mistakes:

• Confusing sequential scan with index scan
• Thinking sequential scan reads only some rows
• Assuming sequential scan deletes rows

2. Which of the following is the correct way to see if PostgreSQL uses an index scan or sequential scan?

easy

A. SELECT * FROM table WHERE id = 1;

B. EXPLAIN SELECT * FROM table WHERE id = 1;

C. CREATE INDEX ON table(id);

D. DROP INDEX index_name;

Solution

1. Step 1: Identify command to check query plan

   The EXPLAIN command shows how PostgreSQL executes a query, including scan type.

2. Step 2: Eliminate other options

   SELECT runs query but doesn't show plan; CREATE and DROP INDEX modify indexes, not show plans.

3. Final Answer:

   EXPLAIN SELECT * FROM table WHERE id = 1; -> Option B

4. Quick Check:

   EXPLAIN shows scan type [OK]

Hint: Use EXPLAIN before query to see scan type [OK]

Common Mistakes:

• Running SELECT without EXPLAIN to check scan
• Confusing index creation with scan checking
• Using DROP INDEX to check scans

3. Given a table users(id SERIAL PRIMARY KEY, name TEXT) with 1 million rows, which scan is PostgreSQL likely to use for this query?
SELECT * FROM users WHERE id = 500000;

medium

A. Index scan using the primary key index

B. Sequential scan scanning all 1 million rows

C. Bitmap heap scan reading random rows

D. No scan, query will fail

Solution

1. Step 1: Analyze query condition and table size

   The query filters by primary key id, which has an index, and the table is large (1 million rows).

2. Step 2: Determine efficient scan type

   PostgreSQL uses an index scan to quickly find the single matching row instead of scanning all rows.

3. Final Answer:

   Index scan using the primary key index -> Option A

4. Quick Check:

   Selective query on indexed column = index scan [OK]

Hint: Selective query on indexed column uses index scan [OK]

Common Mistakes:

• Assuming sequential scan for large tables with indexed filter
• Confusing bitmap heap scan with index scan
• Thinking query fails without reason

4. You wrote this query:
SELECT * FROM orders WHERE customer_id = 123;
But EXPLAIN shows a sequential scan instead of an index scan. What could be the reason?

medium

A. There is no index on customer_id

B. The table is empty

C. The query syntax is incorrect

D. PostgreSQL always uses sequential scan

Solution

1. Step 1: Check index presence on filter column

   If no index exists on customer_id, PostgreSQL must scan all rows sequentially.

2. Step 2: Evaluate other options

   Empty table would still show scan but no rows; syntax is correct; PostgreSQL chooses scan type based on indexes.

3. Final Answer:

   There is no index on customer_id -> Option A

4. Quick Check:

   No index on filter column = sequential scan [OK]

Hint: No index on filter column causes sequential scan [OK]

Common Mistakes:

• Assuming syntax error causes scan type
• Thinking PostgreSQL always uses sequential scan
• Ignoring missing index as cause

5. You have a large table products with millions of rows and an index on category_id. You run:
SELECT * FROM products WHERE category_id IN (1, 2, 3, 4, 5);
PostgreSQL chooses a sequential scan instead of an index scan. Why might this happen?

hard

A. The index on category_id is corrupted

B. Sequential scan is always faster for any query

C. The query is not selective enough; many rows match

D. The IN clause is invalid syntax

Solution

1. Step 1: Understand query selectivity

   The query matches many rows because it filters on multiple categories, reducing selectivity.

2. Step 2: Explain PostgreSQL scan choice

   When many rows match, PostgreSQL prefers sequential scan as it can be faster than many index lookups.

3. Final Answer:

   The query is not selective enough; many rows match -> Option C

4. Quick Check:

   Low selectivity = sequential scan preferred [OK]

Hint: Low selectivity queries often use sequential scan [OK]

Common Mistakes:

• Assuming index corruption without evidence
• Thinking sequential scan is always slower
• Believing IN clause is invalid syntax