SQL Academy · Lesson

Sequential Scans vs Index Scans

Know when a sequential scan is fine, when an index scan is required, and how the planner decides.

Sequential Scans vs Index Scans is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Two Ways to Find Rows

The database has two basic strategies for reading rows:

Sequential Scan — read every page of the table
Index Scan — walk an index, fetch matching rows

When Sequential Scan Is Right

If you need most of the table anyway, scanning is cheaper than reading the index AND fetching each matching row. Roughly: over ~10–20% of rows → seq scan wins.

When Index Scan Wins

For selective queries (small fraction of rows), the index pays off:

EXPLAIN SELECT * FROM users WHERE id = 42;
-- Index Scan using users_pkey  (cost=0.43..8.45 rows=1)

EXPLAIN SELECT * FROM users WHERE active;
-- Seq Scan on users  (cost=0.00..15000.00 rows=950000)
-- (because most users are active)

Index Scan vs Index-Only Scan

Sometimes the index alone has all the columns you need — no table fetch needed. This is Index-Only Scan:

CREATE INDEX users_email_id_idx ON users(id) INCLUDE (email);

EXPLAIN SELECT email FROM users WHERE id = 42;
-- Index Only Scan using users_email_id_idx

Bitmap Index Scan

For medium selectivity, PostgreSQL may build a bitmap of matching rows, then fetch them in physical order — faster than random I/O:

EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- Bitmap Heap Scan on orders
--   Recheck Cond: (status = 'pending')
--   -> Bitmap Index Scan on orders_status_idx

Why the Planner Picks Seq Scan

Common reasons:

No index on the filtered column
The index can't be used (function on the column, OR clauses, type mismatches)
The expected row count is too high to make an index worthwhile
Statistics are stale and the planner is misjudging selectivity

Forcing Index Use (Carefully)

You cannot directly hint PostgreSQL. Instead:

Run ANALYZE to refresh stats
Add the right index
Set session knobs: SET enable_seqscan = off; for diagnosis (not for production)

Indexable Predicates

For an index to help, the WHERE must be "sargable" — comparing the indexed column directly:

-- GOOD:
WHERE created_at >= '2024-01-01'

-- BAD (function on the column):
WHERE date_trunc('day', created_at) = '2024-01-01'

-- BAD (cast):
WHERE created_at::DATE = '2024-01-01'

-- FIX: add a functional index, or rewrite with range.

Composite Index Order

An index on (a, b) helps queries on a alone and on a AND b, but not on b alone.

Index Size Matters

A narrow B-tree index with hot keys may stay entirely in memory; a wide index may not. Smaller indexes are faster.

Verify the Plan

After adding an index, run EXPLAIN ANALYZE to confirm the planner is actually using it. If not, dig in.

Recap

Sequential vs index scan is selectivity-driven.

Selective filter → index scan
Most-of-the-table → seq scan
Bitmap scan for the middle
Watch sargability

Quick Check

Why might PostgreSQL choose a sequential scan over an existing index?

Frequently asked questions

Is the “Sequential Scans vs Index Scans” lesson free?

Yes — the full text of “Sequential Scans vs Index Scans” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Sequential Scans vs Index Scans”?

Know when a sequential scan is fine, when an index scan is required, and how the planner decides. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Sequential Scans vs Index Scans” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy