Index Scan vs Sequential Scan

• 28 Feb, 2026
• 0 Comments
• 3 Mins Read

Index Scan vs Sequential Scan

 Understanding the Core Difference in Database Query Performance

When working with relational databases like PostgreSQL, MySQL, or Oracle Database, one of the most important concepts for performance tuning is understanding how data is read from tables.

Two common access methods used by database query planners are:

• Index Scan

• Sequential Scan (Full Table Scan)

If you’re preparing for DBA interviews or optimizing production queries, this topic is extremely important.

Let’s understand both in detail.

What is a Sequential Scan?

A Sequential Scan (also called a Full Table Scan) means the database reads every row in the table, one by one, to find matching records.

How It Works:

1. Database starts from the first data block.

2. Reads every block in order.

3. Checks each row against the WHERE condition.

4. Returns matching rows.

Example:

SELECT * FROM employees WHERE department = 'HR';

If there is no index on the department column, the database will perform a Sequential Scan.

When Does Sequential Scan Happen?

• No index exists on the filtered column

• Large percentage of rows are required

• Table is very small

• Index is not selective

• Planner estimates full scan is cheaper

Advantages of Sequential Scan

Simple and predictable
Efficient for small tables
Good when fetching most of the table data
Fewer random disk reads

Disadvantages

Slow for large tables
Reads unnecessary rows
Higher I/O for selective queries

What is an Index Scan?

An Index Scan uses an index to locate specific rows without scanning the entire table.

Indexes are typically built using B-Tree structures in databases like PostgreSQL and MySQL.

How It Works:

1. Database searches the index.

2. Finds matching row pointers (row IDs).

3. Fetches corresponding rows from the table.

Example:

CREATE INDEX idx_department ON employees(department);

SELECT * FROM employees WHERE department = 'HR';

Now the database will use an Index Scan instead of a Sequential Scan.

Types of Index Scans (Especially in PostgreSQL)

In PostgreSQL, you may see:

• Index Scan

• Index Only Scan

• Bitmap Index Scan

• Bitmap Heap Scan

1. Index Scan

Fetches index entries and then accesses table rows.

2. Index Only Scan

Fetches data directly from index (if all required columns are in index).

3. Bitmap Index Scan

Used when multiple rows match. Efficient for medium selectivity queries.

Performance Comparison

When is Sequential Scan Actually Better?

Many beginners think index is always faster – this is wrong.

Sequential Scan is better when:

• Table has 1,000 rows only

• Query returns 60–80% of rows

• Index has poor selectivity

• Table fits in memory

Example:

SELECT * FROM orders WHERE status IN ('shipped', 'delivered');

If 75% of rows match, full scan is often cheaper.

How Database Decides?

Databases use a Query Optimizer and Cost-Based Planner.

In PostgreSQL, you can check execution plan using:

EXPLAIN ANALYZE SELECT * FROM employees WHERE department='HR';

The planner estimates:

• Table size

• Number of matching rows

• Index selectivity

• Disk I/O cost

• CPU cost

Then it chooses the cheapest path.

Real-World Production Scenario

Let’s say:

• Table has 10 million rows

• Only 10 rows match condition

• Index exists

Index Scan is ideal.

But if:

• Table has 10 million rows

• 8 million rows match condition

Sequential Scan is faster.

Common Mistakes by Developers

1. Creating too many indexes

2. Forcing index usage unnecessarily

3. Not analyzing table statistics

4. Ignoring execution plans

DBA Interview Question Angle

You might be asked:

• Why is PostgreSQL using Sequential Scan even when index exists?

• What is selectivity?

• Difference between Index Scan and Bitmap Scan?

• How to force index usage?

Make sure you understand cost estimation.

Practical Tips for Performance Tuning

• Run ANALYZE to update statistics
• Use selective indexes
• Avoid indexing low-cardinality columns
• Check EXPLAIN ANALYZE regularly
• Monitor slow queries

Final Thoughts

Understanding Index Scan vs Sequential Scan is fundamental for:

• Database Administrators

• Backend Developers

• Performance Engineers

• Interview Preparation

Indexes are powerful – but not magic. The database optimizer decides based on cost, not emotion.

If you’re learning database performance tuning at Learnomate Technologies, make sure you practice with real execution plans and production-like datasets.

Mastering this concept will significantly improve your SQL optimization skills and confidence in DBA interviews.