What is an Index Scan?

by

An index scan is a method databases use to retrieve data by reading through an index from start to finish. The database reads every entry in the index sequentially, checking each one to see if it matches your query conditions.

This is different from an index seek, where the database jumps directly to specific values in the index. Index scans happen when the database determines it needs to examine a large portion of the index, or when it can’t use the index’s sorted structure to go directly to the data you need.

When Do Index Scans Happen?

The query optimizer chooses an index scan in several situations. If your query needs to return a large percentage of rows from a table, scanning the index might be more efficient than multiple seeks. Similarly, when you’re searching for values that don’t match the index’s leading columns, the database can’t use the index’s sort order effectively, so it scans instead.

Queries with certain operators like NOT IN, <>, or OR often trigger index scans because the optimizer can’t narrow down to specific index locations. Range queries on non-indexed columns or calculations within WHERE clauses also typically result in scans.

Index Scan vs Table Scan

An index scan is generally faster than a full table scan because indexes are smaller than the actual table data. The index contains only specific columns plus a pointer to the full row, so reading through it requires less I/O. However, if the query needs columns that aren’t in the index, the database has to perform additional lookups to get that data from the table itself, which can slow things down.

For queries where you need most or all of the table’s rows anyway, a table scan might actually be more efficient since it avoids the overhead of reading the index first and then looking up the data.

For very small tables, the optimizer often skips indexes entirely and just reads the table. The overhead of using an index isn’t worth it when you can read all the data in one or two disk pages.

Example of Index Scan

Here’s a simple example that shows a couple of index scans in action. We’ll first create and populate a table. Then we’ll create two indexes. We’ll then use PostgreSQL’s EXPLAIN command to view the query execution plan for a couple of queries.

Here’s the script for the data set and indexes:

CREATE TABLE concert_tickets (
    ticket_id INT PRIMARY KEY,
    venue_name VARCHAR(100),
    artist VARCHAR(100),
    show_date DATE,
    price DECIMAL(10,2),
    section VARCHAR(20)
);

CREATE INDEX idx_artist ON concert_tickets(artist);
CREATE INDEX idx_show_date ON concert_tickets(show_date);

-- Insert enough data to make indexes worthwhile
INSERT INTO concert_tickets 
SELECT 
    n,
    CASE (n % 5)
        WHEN 0 THEN 'Red Rocks'
        WHEN 1 THEN 'Greek Theatre'
        WHEN 2 THEN 'Hollywood Bowl'
        WHEN 3 THEN 'Madison Square Garden'
        ELSE 'The Fillmore'
    END,
    CASE (n % 8)
        WHEN 0 THEN 'The Midnight'
        WHEN 1 THEN 'Khruangbin'
        WHEN 2 THEN 'Heilung'
        WHEN 3 THEN 'Tool'
        WHEN 4 THEN 'Gojira'
        WHEN 5 THEN 'Explosions in the Sky'
        WHEN 6 THEN 'Sigur Rós'
        ELSE 'Godspeed You! Black Emperor'
    END,
    DATE '2026-01-01' + (n % 365),
    50.00 + (n % 150),
    CASE (n % 4)
        WHEN 0 THEN 'GA'
        WHEN 1 THEN 'Orchestra'
        WHEN 2 THEN 'Balcony'
        ELSE 'Floor'
    END
FROM generate_series(1, 10000) AS n;

This creates a table called concert_tickets and inserts 10,000 rows so that there’s enough data to trigger the index scan. The script also creates two indexes:

  • idx_artists
  • idx_show_date

We’ll be using these indexes in the following queries.

Now let’s run our first query that produces an index scan. We’ll prefix the query with PostgreSQL’s EXPLAIN command, which will result in the query plan being returned instead of the query results:

EXPLAIN SELECT artist, show_date, venue_name
FROM concert_tickets
WHERE show_date BETWEEN '2026-06-01' AND '2026-08-31';

Output:

                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Bitmap Heap Scan on concert_tickets  (cost=4.35..26.87 rows=7 width=440)
   Recheck Cond: ((show_date >= '2026-06-01'::date) AND (show_date <= '2026-08-31'::date))
   ->  Bitmap Index Scan on idx_show_date  (cost=0.00..4.35 rows=7 width=0)
         Index Cond: ((show_date >= '2026-06-01'::date) AND (show_date <= '2026-08-31'::date))
(4 rows)

The query execution plan shows a “Bitmap Index Scan” on the idx_show_date index. This is the index we created on the show_date column of the concert_tickets table (we used CREATE INDEX idx_artist ON concert_tickets(artist) to do this).

Anyway, for this query PostgreSQL scans through the index to find all entries where the show date falls between June 1st and August 31st, 2026. It builds a bitmap of matching rows, then uses that bitmap to fetch the actual data from the table in a “Bitmap Heap Scan.” This two-step approach helps reduce random I/O when fetching multiple rows. The index scan reads through the relevant portion of the index rather than seeking to individual values, which makes sense for a range query that returns multiple rows.

Here’s another example that can trigger an index scan. This one queries for multiple specific values:

EXPLAIN SELECT artist, show_date, price
FROM concert_tickets
WHERE artist IN ('The Midnight', 'Khruangbin', 'Heilung', 'Tool');

Output:

                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on concert_tickets  (cost=17.33..80.06 rows=28 width=238)
   Recheck Cond: ((artist)::text = ANY ('{"The Midnight",Khruangbin,Heilung,Tool}'::text[]))
   ->  Bitmap Index Scan on idx_artist  (cost=0.00..17.32 rows=28 width=0)
         Index Cond: ((artist)::text = ANY ('{"The Midnight",Khruangbin,Heilung,Tool}'::text[]))
(4 rows)

This one also uses a “Bitmap Index Scan”, but this time it’s on the idx_artist index. The database scans through the index looking for any entries that match the four artists specified in the IN clause. Since these artist names are scattered throughout the index (they’re not consecutive values), PostgreSQL scans the index to locate all matching entries, builds a bitmap of their positions, and then retrieves the actual rows.

Query that Does a Table Scan

Let’s see what a table scan looks like:

EXPLAIN SELECT artist, show_date, price
FROM concert_tickets
WHERE artist NOT IN ('The Midnight', 'Khruangbin', 'Heilung', 'Tool');

Output:

                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Seq Scan on concert_tickets  (cost=0.00..119.79 rows=1358 width=238)
   Filter: ((artist)::text <> ALL ('{"The Midnight",Khruangbin,Heilung,Tool}'::text[]))
(2 rows)

All I did was change IN to NOT IN. The rest of the query remains the same. This produces a very different execution plan. Instead of using the index, PostgreSQL performs a sequential scan (table scan) of the entire concert_tickets table. The plan shows it’s checking each row to ensure the artist is not one of the four specified values.

Why the change? PostgreSQL’s statistics estimate this will return about 1,358 rows (roughly 14% of the table), although the actual result is around 5,000 rows, so the estimate seems to be way off in this case. But even with the underestimate, PostgreSQL chose a table scan because NOT IN queries typically need to examine most or all of the table anyway. The index can’t efficiently tell you which rows don’t match certain values, so scanning the table directly avoids the overhead of using an index that won’t help narrow down the results.

Index Scans vs Index Seeks

When it comes to using indexes, databases have two main strategies: scanning and seeking.

  • An index seek (sometimes called an index search) is generally faster than an index scan. Instead of reading through the index sequentially, the database jumps directly to specific values using the index’s sorted structure. It’s a bit like looking for “Smith” in the phone book where you flip straight to the “S” section rather than starting at “A” and working your way through. Index seeks work well for queries with equality conditions on indexed columns. For example, WHERE artist = 'Tool' allows the database to seek directly to “Tool” in the index. The lookup is fast because the index is sorted, and the database knows exactly where to find that value.
  • Index scans, on the other hand, read through portions (or all) of an index sequentially. This happens when the database can’t jump to a specific location. This can happen with the likes of range queries, multiple value lookups, or conditions that don’t match the index’s structure. The query WHERE artist IN ('Tool', 'Heilung', 'Gojira') might scan the index because these values are scattered throughout, making sequential reading more practical than multiple seeks.

The performance gap between seeks and scans grows with table size. On a small table, the difference is negligible. On a table with millions of rows, seeks can be orders of magnitude faster, but only when they’re applicable to your query pattern.

Should You Worry About Index Scans?

Given index seeks are typically faster than index scans, you may be wondering if you should be concerned when your query plan shows index scans instead of index seeks. But you shouldn’t worry too much about this. Index scans are a legitimate and often optimal way to retrieve data. The optimizer chooses them when they make sense for your query.

If a query is performing poorly and you see index scans in the execution plan, the question shouldn’t be how to eliminate the scan, but rather whether there’s a way to restructure it so that a seek is possible. Sometimes the answer is yes. Adding a composite index or rewriting the query conditions can enable seeks. But often the answer is no, and the index scan is simply the best approach for what you’re asking the database to do.

For queries that genuinely need to examine ranges, multiple values, or a significant portion of your data, index scans are working as intended. They’re faster than table scans and appropriate for the task. Focus your optimization efforts on queries where there’s actually room for improvement, not on eliminating index scans just because they’re not seeks.