Sequential vs Index scans: when each one wins

Capsule description

The most common pedagogical mistake about PostgreSQL is the phrase "Sequential Scan = bad, Index Scan = good". It's what basic courses repeat, and it's what almost every backend dev believes before reading plans seriously.

The truth is more interesting. PostgreSQL has four main scan types, and each one has cases where it's the right choice. Sometimes a Sequential Scan beats an Index Scan by an order of magnitude — and understanding why is what separates the dev who "throws indexes at everything" from the one who knows when the index helps and when it's counterproductive.

In this capsule you'll understand:

• Sequential Scan: when it wins, why it's sometimes optimal.
• Index Scan: when the planner chooses it and when it discards it.
• Index-Only Scan: the ideal case where the table isn't touched — covering indexes.
• Bitmap Heap Scan + Bitmap Index Scan: the combined plan for medium selectivity or multiple indexes.

By the end, when you see any of these four in a plan, you'll know why the planner chose it, whether it's the right choice, and what you'd change if it isn't. This is 70% of diagnosing "slow queries".

Mental model: the planner is a shopkeeper comparing routes

Imagine a shopkeeper who has a warehouse and needs to assemble orders. Each order requests specific items. To assemble the order, they can:

• Sequential Scan: walk through all the aisles in order, item by item, and grab whatever matches the order. It works well if the order requests most of the warehouse's items — walking through everything gets you to most of the products anyway.
• Index Scan: go to the catalog, look up the exact location of each requested item, go straight to that location and grab it. It works well if the order is small — few items, the lookup is worth it.
• Index-Only Scan: if the order only needs information that's in the catalog (e.g.: the price of each item), you don't need to go to the warehouse — you read everything from the catalog. As fast as possible.
• Bitmap Heap Scan + Bitmap Index Scan: the intermediate case. The catalog gives you all the locations at once (instead of one by one), you sort them by physical aisle order, and you walk the aisles in order, grabbing everything in one pass. It combines the advantages of the two extremes for medium selectivity.

                Filter selectivity (rows / total table)

   0%               1%               10%               100%
   │                │                │                  │
   ▼                ▼                ▼                  ▼
   Index Scan      Index Scan       Bitmap Heap        Sequential Scan
   (very few)      (few)            (medium)           (most)

   Index-Only Scan: wins at any level IF the index covers all the requested columns

That's the intuition. Now let's get into the detail of each one.

Sequential Scan: when it's the right choice

Seq Scan on books  (cost=0.00..1620.00 rows=100000 width=22) (actual time=0.012..7.812 rows=100000 loops=1)
  Filter: (published_year > 1900)
  Rows Removed by Filter: 50

What it does: reads the table page by page, sequentially, and applies the filter (if any) to each row.

Why it sometimes wins:

1. Sequential reading is cheap compared to random access. Especially on HDDs, but also on SSDs (read-ahead, page prefetching). A sequential page costs seq_page_cost = 1.0; a random one costs random_page_cost = 4.0 (HDD default) or 1.1 (SSD).
2. No index overhead. It doesn't have to traverse the B-tree or do a heap fetch for each match. It goes straight to the table.
3. Implicit vectorization. PostgreSQL optimizes Sequential Scans with special CPU instructions.
4. Easy parallelization. Parallel Sequential Scans (Parallel Seq Scan) scale linearly with cores.

When the planner chooses it:

• Very small table. If the table fits in a couple of pages, it's not even worth consulting the index.
• Low-selectivity filter. If you're going to read >5-30% of the table (depends on hardware and random_page_cost), it's cheaper to read everything sequentially than to do 30,000 random fetches.
• No filter. SELECT * FROM books is always a Seq Scan (there's no filter to use an index).
• The planner overestimated the selectivity and decided the filter brings back too much.

When it's a symptom of a problem:

• A large table (millions of rows) with a selective filter (<1% of rows) and yet the planner chooses a Seq Scan. Typical causes: a missing index on the filter's column, or an index that exists but isn't used (the WHERE has an implicit cast, a non-immutable function, etc.).

Example: when a Seq Scan is optimal

-- A filter that matches almost the whole table
EXPLAIN ANALYZE SELECT * FROM books WHERE published_year > 1900;

Seq Scan on books  (cost=0.00..2120.00 rows=99950 width=22) (actual time=0.012..7.812 rows=99950 loops=1)
  Filter: (published_year > 1900)
  Rows Removed by Filter: 50
Execution Time: 9.234 ms

Analysis:

• 99,950 of 100,000 rows match. Selectivity: 99.95%.
• If you forced an Index Scan: it would have to read 99,950 index entries + 99,950 random heap fetches = much more expensive.
• A Seq Scan is the optimal choice. Even if an index exists on published_year, the planner correctly ignores it.

Example: when a Seq Scan is a symptom

-- A selective filter on a large table
EXPLAIN ANALYZE SELECT * FROM books WHERE author_id = 42;

If books(author_id) has no index, the output is:

Seq Scan on books  (cost=0.00..1870.00 rows=20 width=22) (actual time=0.024..7.812 rows=22 loops=1)
  Filter: (author_id = 42)
  Rows Removed by Filter: 99978
Execution Time: 7.834 ms

Analysis:

• 22 of 100,000 rows match. Selectivity: 0.022%.
• It read 100k rows to return 22. 99.978% waste.
• A clear symptom: a missing index on books(author_id). Solution (module 3):

  CREATE INDEX idx_books_author_id ON books(author_id);

  After that CREATE INDEX and an ANALYZE, the plan changes to an Index Scan or a Bitmap Heap Scan.

Index Scan: the "few rows, I fetch them directly" case

Index Scan using idx_books_year on books  (cost=0.42..28.45 rows=8 width=22) (actual time=0.025..0.082 rows=8 loops=1)
  Index Cond: (published_year = 1987)

What it does: traverses the index's B-tree to find the entries that match the Index Cond, and for each entry does a heap fetch (reads the table page where the full row is).

How it works internally (simplified):

1. The planner searches idx_books_year (B-tree).
2. It traverses the tree: root → branch → leaf. ~3-4 levels for medium tables.
3. In the leaf it finds pointers to the table's rows (CTID = (page, position)).
4. For each CTID:
   a. Read the table page (8KB).
   b. Find the row.
   c. Return the row to the executor.
5. Repeat until the Index Cond no longer matches.

Theoretical cost: O(log N) to find the first entry + O(K) heap fetches for K matched rows.

When it wins:

• Very selective filters (<1% of the table). Few heap fetches.
• Filters on the PK (PK lookups are always Index Scan).
• Queries that return few rows and only need a few columns.

When it loses:

• Low-selectivity filters. Many random heap fetches outweigh the cost of a Seq Scan.
• When the column's correlation is low (logical order ≠ physical order). Each heap fetch is really random.
• Queries that return most of the table.

Index Cond vs Filter (critical reminder)

Index Scan using idx_books_year on books
  Index Cond: (published_year = 1987)         ← resolved by the index
  Filter: (title LIKE 'A%')                    ← applied after reading the row
  Rows Removed by Filter: 7                    ← discarded 7 of the 8 the index brought back

Reading: the index brought back 8 rows (those from the year 1987). Of those 8, the Filter (which the index does not cover) discarded 7. Only 1 survived the filter.

Implication: the index was partially useful — it limited the rows to read from the heap, but it didn't resolve the whole query. If you had a composite index (published_year, title), both predicates would be Index Cond, no Filter — more efficient.

Index-Only Scan: the ideal plan

Index Only Scan using idx_books_year_title on books  (cost=0.42..8.44 rows=8 width=18) (actual time=0.025..0.080 rows=8 loops=1)
  Index Cond: (published_year = 1987)
  Heap Fetches: 0

What it does: reads only from the index — it doesn't touch the table. Only possible if the index contains all the columns the query needs.

When it appears:

-- If you have this index
CREATE INDEX idx_books_year_title ON books(published_year, title);

-- And this query
SELECT title FROM books WHERE published_year = 1987;

The index contains published_year + title. The SELECT only requests title. There's no need to read the table. The planner uses an Index-Only Scan.

Why it's the fastest:

• No heap fetches (Heap Fetches: 0). You save massive IO.
• The index is usually much smaller than the table (8KB pages with many entries, vs full rows).
• Purely sequential reading within the index.

Caveat: the "visibility map" and heap fetches

PostgreSQL doesn't guarantee that the rows in the index are "visible" to the current transaction (MVCC). To confirm visibility, it looks at the visibility map (a bitmap per table). If a table page is marked "all-visible", the Index-Only Scan doesn't need to go to the heap. If it isn't marked, it does a fetch.

Index Only Scan using idx_books_year_title on books
  Index Cond: (published_year = 1987)
  Heap Fetches: 5

Heap Fetches: 5 means: it read 8 index entries, but 5 required going to the heap to verify visibility. It's still more efficient than a normal Index Scan (which always does a heap fetch), but less than a pure Index-Only Scan.

How to maximize Index-Only Scans:

• Make sure VACUUM runs regularly (autovacuum keeps the visibility map up to date).

• Use covering indexes with the INCLUDE clause (PostgreSQL 11+):

  -- Only published_year in the index tree; title as payload
  CREATE INDEX idx_books_year_inc_title
  ON books(published_year) INCLUDE (title);

  The title column isn't indexed (you can't filter by it using this index), but it is stored in the leaf — available for Index-Only Scans. More efficient than a composite index when you only want to cover a SELECT.

(Full detail on covering indexes and INCLUDE in module 3.)

Bitmap Heap Scan + Bitmap Index Scan: the combined plan

Bitmap Heap Scan on books  (cost=15.20..345.50 rows=1500 width=22) (actual time=0.245..2.812 rows=1485 loops=1)
  Recheck Cond: (published_year BETWEEN 1980 AND 1990)
  Heap Blocks: exact=180
  Buffers: shared hit=192
  ->  Bitmap Index Scan on idx_books_year  (cost=0.00..14.83 rows=1500 width=0) (actual time=0.225..0.226 rows=1485 loops=1)
        Index Cond: (published_year >= 1980 AND published_year <= 1990)

What it does: two steps.

1. Bitmap Index Scan: traverses the index and builds a bitmap (a bit map) that marks which table pages contain matched rows. It doesn't do heap fetches yet.
2. Bitmap Heap Scan: sorts the marked pages by physical order, and reads them in that order — sequentially when there are adjacent pages. For each row read, it rechecks the condition (because the bitmap can have false positives at the page level).

Why it exists:

• For medium selectivities (1-30% of the table), an Index Scan would do thousands of random heap fetches. Costly.
• A Seq Scan would read pages empty of matches. Also costly.
• A Bitmap Heap Scan is the middle ground: it uses the index to avoid the pages with no matches, but reads the pages with matches in sequential order.

When it appears:

• Filters with ranges (BETWEEN, >, <).
• Filters with OR that combine several indexes (BitmapAnd, BitmapOr).
• When the planner estimates that the number of matched rows is "medium" — neither so few as for an Index Scan nor so many as for a Seq Scan.

Combining several indexes

-- You have two separate indexes
CREATE INDEX idx_books_year ON books(published_year);
CREATE INDEX idx_books_author ON books(author_id);

-- A query with OR that leverages both
EXPLAIN ANALYZE
SELECT * FROM books
WHERE published_year = 1987 OR author_id = 42;

Bitmap Heap Scan on books  (cost=20.42..420.50 rows=30 width=22) (actual time=0.412..2.823 rows=30 loops=1)
  Recheck Cond: ((published_year = 1987) OR (author_id = 42))
  Heap Blocks: exact=29
  ->  BitmapOr  (cost=20.42..20.42 rows=30 width=0) (actual time=0.298..0.298 rows=0 loops=1)
        ->  Bitmap Index Scan on idx_books_year  (cost=0.00..4.43 rows=8 width=0)
              Index Cond: (published_year = 1987)
        ->  Bitmap Index Scan on idx_books_author  (cost=0.00..15.99 rows=22 width=0)
              Index Cond: (author_id = 42)

Reading:

• There are two Bitmap Index Scans (one per index).
• Their bitmaps are combined with BitmapOr (union).
• Then, a single Bitmap Heap Scan reads the rows in physical order.

This is what makes Bitmap scans powerful: they combine information from multiple indexes into a single heap scan. Without bitmap scans, PostgreSQL would have to choose a single index.

The planner's decision: side-by-side comparison

Let's see how the plan changes based on selectivity. Setup (we assume idx_books_year is already created):

CREATE INDEX IF NOT EXISTS idx_books_year ON books(published_year);
ANALYZE books;

Very selective filter (8 rows / 100k = 0.008%)

EXPLAIN ANALYZE SELECT * FROM books WHERE published_year = 1987;

Index Scan using idx_books_year on books  (cost=0.42..28.45 rows=8 width=22) (actual time=0.025..0.082 rows=8 loops=1)
  Index Cond: (published_year = 1987)

→ Index Scan. Few rows, worth going straight there.

Medium filter (~1500 rows / 100k = 1.5%)

EXPLAIN ANALYZE SELECT * FROM books WHERE published_year BETWEEN 1980 AND 1990;

Bitmap Heap Scan on books  (cost=15.20..345.50 rows=1500 width=22) (actual time=0.245..2.812 rows=1485 loops=1)
  Recheck Cond: ((published_year >= 1980) AND (published_year <= 1990))
  Heap Blocks: exact=180
  ->  Bitmap Index Scan on idx_books_year  (cost=0.00..14.83 rows=1500 width=0)
        Index Cond: ((published_year >= 1980) AND (published_year <= 1990))

→ Bitmap Heap Scan. A medium amount; it combines the Index to avoid empty pages + sequential reading where there are matches.

Low-selectivity filter (~99,950 rows / 100k = 99.95%)

EXPLAIN ANALYZE SELECT * FROM books WHERE published_year > 1900;

Seq Scan on books  (cost=0.00..2120.00 rows=99950 width=22) (actual time=0.012..7.812 rows=99950 loops=1)
  Filter: (published_year > 1900)
  Rows Removed by Filter: 50

→ Seq Scan. Almost all the rows match; an index would be counterproductive.

This is the correct behavior. The planner is choosing well in each case. The problems come when the planner overestimates/underestimates rows and chooses the wrong plan — we saw that case in capsule 04.

Special case: Index Scan Backward

Index Scan Backward using idx_books_year on books  (cost=0.42..50.00 rows=20 width=22)
  Index Cond: (published_year IS NOT NULL)

What happens: it traverses the index in reverse order. Useful for queries with ORDER BY ... DESC LIMIT N. Without it, the planner would do a Sort after the Scan.

Example:

SELECT * FROM books WHERE published_year IS NOT NULL ORDER BY published_year DESC LIMIT 50;

→ Index Scan Backward + Limit. No sort. Efficient.

If you don't have the appropriate index, the plan becomes Seq Scan + Sort + Limit — massively slower.

Why does this matter in real work?

1. 70% of diagnosing slow queries is reading scan types.

When you see a plan, the first thing after the root node is: which scan was used? is it the right one for the selectivity? If the answer is no, you already have a root-cause hypothesis.

2. Indexing decisions are made based on the scan types you see.

When module 3 teaches you to design indexes, you'll justify each index with a plan that shows the current scan (typically a Seq Scan) and the expectation of the resulting scan (Index Scan or Index-Only Scan). Without understanding scan types, indexing decisions are guesswork.

3. Detecting "the index exists but isn't used".

It's a classic headache. The answer is almost always visible in the plan: the WHERE filter has a transformation that invalidates the use of the index (implicit cast, function, expression). Without reading scan types carefully, you don't detect this.

4. Optimizing ranking / pagination queries.

ORDER BY x DESC LIMIT N is ubiquitous in APIs (top N items, recent posts, etc.). Index Scan Backward + Limit is the difference between 5ms and 5000ms. If you don't know it exists, you don't leverage it.

Traps and common mistakes

Mistake 1 (conceptual): "Sequential Scan = bad, always"

Symptom: "I see a Seq Scan in the plan, it has to be fixed."

Why it's sometimes wrong: for low-selectivity filters (>5-30% of the table, depending on hardware), a Seq Scan is optimal. Forcing an index with SET enable_seqscan=off can make the query 10x worse.

How to fix it: look at the selectivity before judging. If a Seq Scan brings back 100k rows to return 99k, it's fine. If it brings back 100k to return 22, there's a problem (missing index).

Mistake 2 (conceptual): "Index Scan = good, always"

Symptom: "The plan uses an Index Scan, it's fine."

Why it's sometimes wrong: an Index Scan with loops=10000 is 10,000 lookups (potentially catastrophic, especially if it's N+1). An Index Scan with Rows Removed by Filter: 50000 means the index didn't cover the full filter. An Index Scan on a column with low correlation does expensive random heap fetches.

How to fix it: look at loops, Rows Removed by Filter, Buffers on each Index Scan. The type doesn't guarantee it's good.

Mistake 3 (operational): thinking CREATE INDEX always helps

Symptom: "I threw indexes at all the columns that appear in WHERE, my database is super slow."

Why it happens:

• Each extra index makes INSERT/UPDATE/DELETE slower (all the indexes have to be updated).
• Indexes take up disk and RAM.
• The planner takes longer at planning with many indexes to evaluate.

How to fix it: indexing is a trade-off. Only indexes with real usage proven by plans. More in module 3 (including how to detect unused indexes with pg_stat_user_indexes).

Mistake 4 (reading): ignoring Heap Fetches in an Index-Only Scan

Symptom: "I see an Index Only Scan, it's perfect."

Why it's sometimes wrong: if Heap Fetches: 5000, the "Index Only" wasn't so "only" — it had to go to the heap 5000 times to verify MVCC visibility. A typical symptom: lagging VACUUM, an outdated visibility map.

How to fix it: Heap Fetches should be close to 0 in well-functioning Index-Only Scans. If not, run VACUUM ANALYZE table and check whether it goes down. (More in module 7.)

Mistake 5 (subtle): an implicit cast that invalidates the index

Symptom: "I have an index on users(email) but the plan says Seq Scan with Filter: ((email)::text = 'foo'::text)."

Why it happens: the email column is of type citext (case-insensitive text), but the literal 'foo' is interpreted as text. PostgreSQL does a cast (email)::text to compare — and the index on citext isn't usable after the cast.

How to fix it:

-- Instead of this (invalidates the index):
SELECT * FROM users WHERE email = 'foo@example.com';

-- Do this (preserves the index):
SELECT * FROM users WHERE email = 'foo@example.com'::citext;

Similar cases: WHERE date_col = '2026-01-01' when date_col is timestamp, WHERE numeric_col = 100 when numeric_col is text. Always check types.

Mistake 6 (decision): a partial index where there was no selectivity

Symptom: "I created a partial index WHERE active = true, it didn't improve anything."

Why it sometimes doesn't help: if 95% of the rows have active = true, the partial index is almost as large as the full index. The gain is marginal. Partial indexes shine when the condition filters out a minority.

How to fix it: consider the cardinality before a partial index. The informal rule: a partial index wins when the condition selects <30% of the table. More in module 3.

Exercises

Exercise 1: force the 4 scan types

On the books table, write 4 queries, each designed so the planner chooses a different scan type: Seq Scan, Index Scan, Index-Only Scan, Bitmap Heap Scan. Verify with EXPLAIN.

CREATE INDEX IF NOT EXISTS idx_books_year ON books(published_year);
CREATE INDEX IF NOT EXISTS idx_books_year_title ON books(published_year, title);
ANALYZE books;

EXPLAIN ANALYZE SELECT * FROM books WHERE published_year > 1900;
-- Seq Scan on books  (cost=0.00..2120.00 rows=99950 width=22) ...

EXPLAIN ANALYZE SELECT * FROM books WHERE published_year = 1987;
-- Index Scan using idx_books_year on books  (cost=0.42..28.45 rows=8 width=22) ...

EXPLAIN ANALYZE SELECT title FROM books WHERE published_year = 1987;
-- Index Only Scan using idx_books_year_title on books  (cost=0.42..8.44 rows=8 width=18)
--   Heap Fetches: 0

EXPLAIN ANALYZE SELECT * FROM books WHERE published_year BETWEEN 1980 AND 1990;
-- Bitmap Heap Scan on books  (cost=15.20..345.50 rows=1500 width=22)
--   ->  Bitmap Index Scan on idx_books_year ...

Exercise 2: the effect of selectivity on the chosen plan

Run the same structural query, varying the filter: =, a small BETWEEN range, a medium BETWEEN range, a large > range. Note which scan type the planner chooses in each case. Does it match what you'd expect from selectivity?

-- ~8 rows (selectivity 0.008%)
EXPLAIN SELECT * FROM books WHERE published_year = 1987;
-- → Index Scan

-- ~80 rows (selectivity 0.08%)
EXPLAIN SELECT * FROM books WHERE published_year BETWEEN 1985 AND 1989;
-- → Index Scan or Bitmap Heap Scan (depends on hardware)

-- ~8000 rows (selectivity 8%)
EXPLAIN SELECT * FROM books WHERE published_year BETWEEN 1980 AND 1990;
-- → Bitmap Heap Scan

-- ~80,000 rows (selectivity 80%)
EXPLAIN SELECT * FROM books WHERE published_year BETWEEN 1925 AND 2025;
-- → Seq Scan

-- ~99,950 rows (selectivity 99.95%)
EXPLAIN SELECT * FROM books WHERE published_year > 1900;
-- → Seq Scan

Exercise 3: turn an Index Scan into an Index-Only Scan

You have this query and this plan:

SELECT title FROM books WHERE published_year = 1987;

Index Scan using idx_books_year on books  (cost=0.42..28.45 rows=8 width=22)
  Index Cond: (published_year = 1987)

What change would you make to the index to turn it into an Index-Only Scan? Apply it and verify.

CREATE INDEX idx_books_year_title ON books(published_year, title);
ANALYZE books;

EXPLAIN ANALYZE SELECT title FROM books WHERE published_year = 1987;
-- Index Only Scan using idx_books_year_title on books
--    (cost=0.42..8.44 rows=8 width=18)
--    Heap Fetches: 0  ← perfect

DROP INDEX idx_books_year_title;
CREATE INDEX idx_books_year_inc_title ON books(published_year) INCLUDE (title);
ANALYZE books;

EXPLAIN ANALYZE SELECT title FROM books WHERE published_year = 1987;
-- Index Only Scan using idx_books_year_inc_title on books
--    (cost=0.42..8.44 rows=8 width=18)
--    Heap Fetches: 0

-- After the CREATE, verify that the plan says "Index Only Scan"
-- and "Heap Fetches: 0" or close to 0.

Exercise 4: why the index isn't used (implicit cast)

You have:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL,
  signup_date TIMESTAMP NOT NULL
);

CREATE INDEX idx_customers_signup ON customers(signup_date);

INSERT INTO customers (email, signup_date)
SELECT 'user_' || g || '@example.com', '2024-01-01'::timestamp + (g || ' minutes')::interval
FROM generate_series(1, 100000) g;

ANALYZE customers;

This query doesn't use the index — diagnose why:

EXPLAIN ANALYZE SELECT * FROM customers WHERE signup_date::date = '2025-01-01';

Seq Scan on customers  (cost=0.00..2370.00 rows=500 width=80) (actual time=0.024..12.234 rows=1440 loops=1)
  Filter: ((signup_date)::date = '2025-01-01'::date)
  Rows Removed by Filter: 98560

Exercise 5: Bitmap Heap Scan combining indexes

On books, create two separate indexes (published_year and author_id) and run a query with OR that uses both. Which plan does it choose? Identify the Bitmap Index Scans and the BitmapOr.

CREATE INDEX IF NOT EXISTS idx_books_year ON books(published_year);
CREATE INDEX IF NOT EXISTS idx_books_author ON books(author_id);
ANALYZE books;

EXPLAIN ANALYZE
SELECT * FROM books
WHERE published_year = 1987 OR author_id = 42;

Bitmap Heap Scan on books  (cost=20.42..420.50 rows=30 width=22) (actual time=0.412..2.823 rows=30 loops=1)
  Recheck Cond: ((published_year = 1987) OR (author_id = 42))
  Heap Blocks: exact=29
  Buffers: shared hit=35
  ->  BitmapOr  (cost=20.42..20.42 rows=30 width=0) (actual time=0.298..0.298 rows=0 loops=1)
        ->  Bitmap Index Scan on idx_books_year  (cost=0.00..4.43 rows=8 width=0)
              Index Cond: (published_year = 1987)
        ->  Bitmap Index Scan on idx_books_author  (cost=0.00..15.99 rows=22 width=0)
              Index Cond: (author_id = 42)

Exercise 6: reading a plan with Index Scan Backward

Run this query and read the plan. Why does the plan have no Sort? What would happen if the index didn't exist?

-- Assuming idx_books_year exists
EXPLAIN ANALYZE
SELECT id, published_year FROM books
ORDER BY published_year DESC LIMIT 10;

Limit  (cost=0.42..0.85 rows=10 width=8) (actual time=0.025..0.045 rows=10 loops=1)
  ->  Index Scan Backward using idx_books_year on books  (cost=0.42..4250.42 rows=100000 width=8)
        (actual time=0.024..0.043 rows=10 loops=1)
Planning Time: 0.082 ms
Execution Time: 0.080 ms

Limit  (cost=2120.45..2120.47 rows=10 width=8) (actual time=18.512..18.515 rows=10 loops=1)
  ->  Sort  (cost=2120.45..2370.45 rows=100000 width=8)
            (actual time=18.510..18.512 rows=10 loops=1)
        Sort Key: published_year DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Seq Scan on books  (cost=0.00..1620.00 rows=100000 width=8)
              (actual time=0.012..7.823 rows=100000 loops=1)
Execution Time: 18.612 ms

Summary and next step

In this capsule you learned:

• Sequential Scan isn't "always bad": it wins when the filter matches >30% of the table, or when the table is small.
• Index Scan shines with very selective filters (<1% of rows). Look at loops and Rows Removed by Filter to validate that it's efficient.
• Index-Only Scan is the ideal plan — it doesn't touch the table, only the index. It requires the index to cover all the requested columns (composite or INCLUDE).
• Bitmap Heap Scan + Bitmap Index Scan is the combined plan for medium selectivities (1-30%) or multiple indexes with OR. It reads pages in physical order (fast).
• The planner adapts the scan type based on estimated selectivity. If the estimates are good (capsule 04), it chooses well.
• Implicit casts in filters invalidate the use of indexes — the #1 cause of "the index isn't used".
• ORDER BY ... DESC LIMIT N benefits from Index Scan Backward — without it, each request pays for a full Sort.

Before moving on you should be able to:

• Distinguish the 4 scan types in any plan
• Say why the planner chose each one (selectivity, index coverage)
• Detect Heap Fetches > 0 in an Index-Only Scan and know what it means
• Recognize when a Seq Scan is optimal vs symptomatic

Next capsule — Buffers and JIT. You now know the scan types. But the same Index Scan can be 5ms or 500ms depending on whether the data is in the RAM cache or on disk. The Buffers: section tells you. And on large queries, JIT compilation comes into play — sometimes it helps, sometimes it gets in the way. You'll learn to read shared hit/read/dirtied, compute the cache hit ratio, identify JIT in the plan, and decide when to disable it. It's the last technical capsule before the visual tools (capsule 07) and the project (capsule 08).

Resources

1. PostgreSQL Documentation — Index Scanning Methods — the official reference for index types and how they're used in each scan.
2. PostgreSQL Documentation — Index Only Scans and Covering Indexes — everything about Index-Only Scans and INCLUDE.
3. Hubert "depesz" Lubaczewski — "Explaining the unexplainable, part 5: Bitmap scans" — the last part of the classic series, covers Bitmap scans in detail.
4. Markus Winand — "Use The Index, Luke!" — Operations — a visual explanation of all the scan types, focused on when each one wins.
5. Bruce Momjian — "Explaining the Postgres Query Optimizer" — a section on how the planner chooses scans.
6. Citus — "Index Only Scan in Postgres" — a practical primer on Index-Only Scans.
7. Tomas Vondra — "On the impact of full-page writes" (PostgreSQL conf talks) — covers the visibility map, relevant for Index-Only Scans with Heap Fetches > 0.

Module 2 — Database Performance & Query Tuning Guide