The secret to unlocking performance gains often lies not just in what you ask in a query, but in how Postgres finds the answer. The Postgres EXPLAIN system is great for understanding how data is being queried. One of secretes to reading EXPLAIN plans is understanding the type of scan done to retrieve the data. The scan type can be the difference between a lightning-fast response or a slow query.

Today I’ll break down the most common scan types, how they work, and when you’ll see them in your queries.

Sequential scan

This type of data scan reads the entire table, row by row checking to see what matches the query conditions. If you have a WHERE or FILTER, Postgres just scans each row looking for matches.

Sequence scans are kind of the foundation of how scans are done and for many searches, this is what Postgres will use. For very large data sets, or those queried often, sequential scans are not ideal and an index scan may be faster. For that reason - knowing how to spot a seq scan vs index scan when reading an EXPLAIN plan is one the most important parts of reading a scan type in a query plan.

EXPLAIN select * from accounts;

QUERY PLAN
-------------------------------------------------------------
Seq Scan on accounts  (cost=0.00..22.70 rows=1270 width=36)
(1 row)

Index Scan

When you create an index in Postgres, you’re creating a column or multi-column reference that is stored on disk. Postgres is able to use this index as a map to the data stored in the table. A basic index scan uses a B-tree to quickly find the exact location of the data using a a two-step process: first Postgres finds the entry in the index, uses the reference, and then it fetches the rest of the row data from the table.

EXPLAIN select * from accounts where id = '5';

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using accounts_pkey on accounts  (cost=0.15..2.37 rows=1 width=36)
   Index Cond: (id = 5)
(2 rows)

Note that primary keys are automatically indexed with a b-tree index, so queries that involve a primary key may use an index scan.

An index scan is typically faster than a sequential scan in Postgres when a query needs to retrieve only a very small fraction of rows from a large table. Using the index is faster than scanning the whole table.

Bitmap Index Scan

If an index scan or a seq scan aren’t the perfect option, Postgres can use the the bitmap index scan as a kind of hybrid approach. It is typically chosen when a query matches too many rows for an regular index scan, but not so many that a sequential scan would be the best option.

This shows up in an EXPLAIN plan as a two-phased approach.

1. Bitmap Index Scan: First, Postgres scans one or more indexes to create an in-memory "bitmap", a simple map of all the table pages that might contain rows you need.
2. Bitmap Heap Scan: The bitmap is used to visit the main table. The key here is that it reads the required pages from the disk sequentially, which can be much faster than the random jumping of a standard index scan.

Bitmap index scans are common when a query has multiple filter conditions that each have a separate index. The bitmap scan allows the database to use separate indexes on different columns simultaneously. You’ll see this scan come up with WHERE conditions joined by AND or OR operators.

EXPLAIN SELECT customer_id, registration_date
FROM customer_records
WHERE gender = 'F'
  AND state_code = 'KS';
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on customer_records  (cost=835.78..8669.29 rows=49226 width=12) (actual time=5.717..38.642 rows=50184.00 loops=1)
   Recheck Cond: (state_code = 'NY'::bpchar)
   Filter: (gender = 'F'::bpchar)
   Rows Removed by Filter: 49682
   Heap Blocks: exact=6370
   Buffers: shared hit=6370 read=87
   ->  Bitmap Index Scan on idx_customer_state  (cost=0.00..823.48 rows=97567 width=0) (actual time=4.377..4.378 rows=99866.00 loops=1)
         Index Cond: (state_code = 'NY'::bpchar)
         Index Searches: 1
         Buffers: shared read=87
 Planning:
   Buffers: shared hit=27 read=2
 Planning Time: 0.774 ms
 Execution Time: 40.572 ms
(14 rows)

Parallel Sequential Scan

You will see a parallel sequential scan when Postgres uses multiple background workers to perform more than one sequential scan on a single large table at the same time. The table is broken into chunks, and each worker gets a chunk to scan, and the results are combined at the end in a gather process. Depending on your query - you may also have an aggregate or sort after the parallel queries and before the final gather. This is part of Postgres’ parallel query function.

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT id, data_value
FROM parallel_test
WHERE data_value < 100000
ORDER BY data_value DESC
LIMIT 1000;

                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=161310.11..161431.04 rows=1000 width=16) (actual time=130.300..140.555 rows=1000.00 loops=1)
   Output: id, data_value
   Buffers: shared hit=142685
   ->  Gather Merge  (cost=161310.11..220311.14 rows=487915 width=16) (actual time=130.299..140.468 rows=1000.00 loops=1)
         Output: id, data_value
         Workers Planned: 5
         Workers Launched: 5
         Buffers: shared hit=142685
         ->  Sort  (cost=160310.04..160553.99 rows=97583 width=16) (actual time=112.942..112.973 rows=861.17 loops=6)
               Output: id, data_value
               Sort Key: parallel_test.data_value DESC
               Sort Method: top-N heapsort  Memory: 163kB
               Buffers: shared hit=142685
               Worker 0:  actual time=112.535..112.571 rows=1000.00 loops=1
                 Sort Method: top-N heapsort  Memory: 164kB
                 Buffers: shared hit=21729
               Worker 1:  actual time=112.271..112.308 rows=1000.00 loops=1
                 Sort Method: top-N heapsort  Memory: 164kB
                 Buffers: shared hit=21573
               Worker 2:  actual time=112.465..112.500 rows=1000.00 loops=1
                 Sort Method: top-N heapsort  Memory: 164kB
                 Buffers: shared hit=20549
               Worker 3:  actual time=99.099..99.133 rows=1000.00 loops=1
                 Sort Method: top-N heapsort  Memory: 163kB
                 Buffers: shared hit=17033
               Worker 4:  actual time=112.333..112.368 rows=1000.00 loops=1
                 Sort Method: top-N heapsort  Memory: 163kB
                 Buffers: shared hit=19964
               ->  Parallel Seq Scan on public.parallel_test  (cost=0.00..154959.67 rows=97583 width=16) (actual time=19.238..99.868 rows=83250.83 loops=6)
                     Output: id, data_value
                     Filter: (parallel_test.data_value < '100000'::numeric)
                     Rows Removed by Filter: 750082
                     Buffers: shared hit=142500
                     Worker 0:  actual time=18.837..99.169 rows=83026.00 loops=1
                       Buffers: shared hit=21692
                     Worker 1:  actual time=18.594..99.301 rows=84378.00 loops=1
                       Buffers: shared hit=21536
                     Worker 2:  actual time=18.706..99.551 rows=79196.00 loops=1
                       Buffers: shared hit=20512
                     Worker 3:  actual time=5.308..86.023 rows=81187.00 loops=1
                       Buffers: shared hit=16996
                     Worker 4:  actual time=18.694..99.497 rows=83574.00 loops=1
                       Buffers: shared hit=19927
 Planning:
   Buffers: shared hit=15
 Planning Time: 0.315 ms
 Execution Time: 140.635 ms
(47 rows)

Parallel index scan

A parallel index scan uses the same parallel workers to scan through an index concurrently. This uses the same methodology of the index scan - except that multiple workers are doing it simultaneously. Each process reads a different part of the index and returns results. Like the other parallel scans, this ends in a gather.

You will see a parallel index scan done when the indexes and tables involved are very large - and the overall operation to split things up and gather them at the end is faster than handing the job to a single worker.

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT data_id, filler_text
FROM parallel_index_test
WHERE data_id BETWEEN 1000000 AND 2000000;

                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=0.43..34560.34 rows=995971 width=109) (actual time=1.014..145.796 rows=1000001.00 loops=1)
   Output: data_id, filler_text
   Workers Planned: 4
   Workers Launched: 4
   Buffers: shared hit=23385
   ->  Parallel Index Scan using idx_data_id on public.parallel_index_test  (cost=0.43..33564.37 rows=248993 width=109) (actual time=0.941..38.211 rows=200000.20 loops=5)
         Output: data_id, filler_text
         Index Cond: ((parallel_index_test.data_id >= 1000000) AND (parallel_index_test.data_id <= 2000000))
         Index Searches: 1
         Buffers: shared hit=23385
         Worker 0:  actual time=2.104..45.540 rows=240638.00 loops=1
           Buffers: shared hit=5640
         Worker 1:  actual time=2.174..45.169 rows=240096.00 loops=1
           Buffers: shared hit=5638
         Worker 2:  actual time=0.067..45.380 rows=242658.00 loops=1
           Buffers: shared hit=5693
         Worker 3:  actual time=0.306..45.122 rows=242292.00 loops=1
           Buffers: shared hit=5686
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.526 ms
 Execution Time: 180.660 ms
(22 rows)

Index-Only Scan

An Index-Only Scan is the superstar of scans and answers the entire query using only the information stored within the index itself. Index only scans are also called “covering indexes” meaning the index itself covers all the data. It never even has to touch the main table. Index only scans are a huge performance win because they’re very fast - no information needs to be retrieved from the heap table. They also typically use less i/o resources because indexes are very cache friendly and often in shared buffers - meaning no data needs to be read for the underlying disk.

Queries benefit from a covering index in these situations:

• The query is very frequently executed.
• The current query is performing a standard index scan followed by many slow disk reads (heap fetches) and using i/o.
• The query only requires a small subset of the table's columns, for example you select only three columns from a table of twenty.
• The columns have a low write frequency. Any column that is indexed must be written to disk and the index, so if you start adding covering indexes for all your columns - you’re essentially creating write amplification.
• The new index, which must cover all needed columns, won't be excessively large. Indexes are stored on disk so you don’t want to cause storage issues.

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT code, status
FROM index_only_test
WHERE code > 'CODE_050000'
ORDER BY code
LIMIT 100;
                                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..2.60 rows=100 width=13) (actual time=0.346..0.362 rows=100.00 loops=1)
   Output: code, status
   Buffers: shared hit=1 read=3
   ->  Index Only Scan using idx_code_status on public.index_only_test  (cost=0.42..1068.02 rows=49000 width=13) (actual time=0.345..0.352 rows=100.00 loops=1)
         Output: code, status
         Index Cond: (index_only_test.code > 'CODE_050000'::text)
         Heap Fetches: 0
         Index Searches: 1
         Buffers: shared hit=1 read=3
 Planning:
   Buffers: shared hit=19
 Planning Time: 1.838 ms
 Execution Time: 0.385 ms
(13 rows)

Summary

We’ve covered all the major scan types so now reading your EXPLAIN plans will be a little easier.

• Seq scan - Postgres looks through the whole table in sequential order to find the query data
• Index scan - Postgres first looks at the index and then fetches the row data the index pointed to
• Bitmap index scan - Postgres first read the index and created a bitmap list matching rows. Second, Postgres read the data heap using the bitmap in a more efficient method than a sequential scan.
• Parallel scan - Postgres used multiple parallel workers to scan the table and data was gathered at the end
• Parallel index scan - Postgres used multiple workers to do an index scan and data was gathered at the end
• Index only scan- All data for the query was in the index

And here’s everything all in one graphic: