Postgres Scan Types in EXPLAIN Plans | Crunchy Data Blog
Elizabeth Christensen
Dec 4, 2025·9 min read
More by this author
Latest Articles<br>Postgres Serials Should be BIGINT (and How to Migrate)<br>Postgres 18 New Default for Data Checksums and How to Deal with Upgrades<br>PostGIS Performance: Simplification<br>Postgres Scan Types in EXPLAIN Plans<br>PostGIS Performance: Data Sampling
Production Postgres<br>Postgres Scan Types in EXPLAIN Plans
Elizabeth Christensen
Dec 4, 2025·9 min read·More by this author
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<br>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<br>Seq Scan on accounts (cost=0.00..22.70 rows=1270 width=36)<br>(1 row)
Index Scan<br>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<br>Index Scan using accounts_pkey on accounts (cost=0.15..2.37 rows=1 width=36)<br>Index Cond: (id = 5)<br>(2 rows)<br>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.However, index scans are not always faster. In many situations, Postgres’ query planner will correctly choose a sequential scan. This is typically for cases when the table being scanned is small or the percentage of rows returned outweighs using an index. If a query returns ~10%, a sequential scan is probably faster.
Bitmap Index Scan<br>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.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.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<br>FROM customer_records<br>WHERE gender = 'F'<br>AND state_code = 'KS';<br>QUERY PLAN<br>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)<br>Recheck Cond: (state_code = 'NY'::bpchar)<br>Filter: (gender = 'F'::bpchar)<br>Rows Removed by Filter: 49682<br>Heap Blocks: exact=6370<br>Buffers: shared hit=6370 read=87<br>-> 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)<br>Index Cond: (state_code = 'NY'::bpchar)<br>Index Searches: 1<br>Buffers: shared read=87<br>Planning:<br>Buffers: shared hit=27 read=2<br>Planning Time: 0.774 ms<br>Execution Time: 40.572 ms<br>(14 rows)
Parallel Sequential Scan<br>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...