Sequential scan vs index scan in PostgreSQL - Visual Side-by-Side Comparison

Concept Flow - Sequential scan vs index scan

Start Query

↓

Check if index exists

↓

Index Scan

↓

Read matching rows

↓

Return results

↓

End

The database decides if it can use an index to find rows quickly. If yes, it uses an index scan; if not, it reads all rows one by one with a sequential scan.

Execution Sample

PostgreSQL

EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 5;

This query shows whether PostgreSQL uses a sequential scan or an index scan to find employees in department 5.

Execution Table

Step	Action	Scan Type	Rows Examined	Rows Returned	Notes
1	Start query execution	N/A	0	0	Query begins
2	Check for index on department_id	N/A	0	0	Index exists
3	Use index to find matching rows	Index Scan	10	10	Only rows with department_id=5 scanned
4	Return matching rows	Index Scan	10	10	Results sent to client
5	End query execution	N/A	10	10	Query finished

💡 Query ends after returning all matching rows found by index scan

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Rows Examined	0	0	10	10	10
Rows Returned	0	0	10	10	10
Scan Type	N/A	N/A	Index Scan	Index Scan	Index Scan

Key Moments - 2 Insights

Why does the database choose an index scan instead of a sequential scan here?

Because an index exists on department_id, the database can quickly find only matching rows without reading the whole table, as shown in execution_table step 3.

What happens if there is no index on the searched column?

The database will perform a sequential scan, reading every row one by one, which is slower but guaranteed to find all matches.

Visual Quiz - 3 Questions

Look at the execution_table, how many rows does the index scan examine?

A 0

B All rows in the table

C 10

D 5

💡 Hint

Check the 'Rows Examined' column at step 3 in the execution_table.

At which step does the database confirm the index exists?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Action' column in the execution_table for index checking.

If the index did not exist, what scan type would replace 'Index Scan' in step 3?

A Sequential Scan

B Bitmap Scan

C Hash Scan

D No Scan

💡 Hint

Recall the concept_flow where absence of index leads to sequential scan.

Concept Snapshot

Sequential scan reads every row in the table one by one.
Index scan uses an index to quickly find matching rows.
PostgreSQL chooses index scan if an index exists and is efficient.
Sequential scan is slower but always works.
Use EXPLAIN ANALYZE to see which scan is used.

Full Transcript

When PostgreSQL runs a query with a WHERE condition, it first checks if there is an index on the searched column. If an index exists, it uses an index scan to quickly find only the rows that match the condition. This means it reads fewer rows and returns results faster. If no index exists, it performs a sequential scan, reading every row in the table to find matches. The execution table shows each step: starting the query, checking for an index, using the index to find rows, returning results, and finishing. Variables like rows examined and scan type change as the query runs. Understanding when PostgreSQL uses each scan helps write faster queries.

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