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.