Sequential scan (Seq Scan)

What a sequential scan is

When PostgreSQL performs a sequential scan, it reads every 8 KB page of the table's heap storage in order, from the first page to the last. For each page, it examines every tuple (row), evaluates the WHERE clause, and returns matching rows to the next stage of the plan. Rows that do not match are discarded — read, considered, and set aside without ceremony.

No index is consulted. The scan does not know which pages contain matching rows in advance — it visits all of them. Allow me to be fair here: this is not inherently slow. Sequential I/O is the fastest access pattern that storage hardware supports. Reading 1 GB of data sequentially from an SSD takes roughly one second. The cost comes from processing rows that will ultimately be thrown away — and that, depending on the circumstances, ranges from perfectly reasonable to quietly alarming.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending';

-- Seq Scan on orders  (cost=0.00..25432.00 rows=45000 width=96)
--                     (actual time=0.012..187.345 rows=45000 loops=1)
--   Filter: (status = 'pending')
--   Rows Removed by Filter: 955000
--   Planning Time: 0.08 ms
--   Execution Time: 201.42 ms

In this plan, PostgreSQL read all one million rows in the orders table to find 45,000 matching ones. The Rows Removed by Filter: 955000 line is the diagnostic signal — 955,000 rows examined and discarded. That is 95% of the work, gone. Whether this is acceptable depends on the table size, the query frequency, and whether a better access path exists. I have my suspicions.

When sequential scans are appropriate

I should be clear about this: sequential scans are not mistakes. A well-run household does not treat every shortcut as an offence. There are several situations where a sequential scan is the correct choice and an index scan would be the slower path.

Small tables

A table with a few dozen rows fits in one or two 8 KB pages. Reading two pages sequentially is faster than traversing a B-tree index (which itself may be two or three pages) and then fetching the heap row. The planner knows this and will choose a sequential scan for small tables regardless of available indexes. Nine rows in a config table? An index would be overdressing for the occasion.

EXPLAIN ANALYZE
SELECT * FROM config_settings WHERE key = 'timezone';

-- Seq Scan on config_settings  (cost=0.00..1.12 rows=1 width=64)
--                               (actual time=0.008..0.009 rows=1 loops=1)
--   Filter: (key = 'timezone')
--   Rows Removed by Filter: 8
--   Planning Time: 0.05 ms
--   Execution Time: 0.02 ms

-- Nine rows total. An index would not help here.

Low selectivity queries

If a query returns most of the rows in a table — say 60% or more — an index scan is counterproductive. Each index entry requires a random I/O to fetch the corresponding heap row. When you need most of the rows anyway, reading them all sequentially is cheaper than bouncing randomly across the heap. The planner compares the estimated cost of each path and picks the cheaper one.

No suitable index exists

If there is no index on the filtered column, a sequential scan is the only option. This is not a planner failure — it is a schema gap. The planner cannot use an index that does not exist, and one does not blame the staff for a task they were never given the tools to perform.

Maintenance operations

VACUUM, ANALYZE, and bulk operations like CREATE INDEX inherently perform sequential scans. They need to visit every row. This is expected and unavoidable.

When sequential scans are a problem

Now we arrive at the matter that prompted your visit. A sequential scan becomes a performance problem when all three of these are true: the table is large, the query is selective (returns a small fraction of rows), and the query runs frequently.

Large tables with selective WHERE clauses

A table with 10 million rows where the query matches 200 of them. PostgreSQL reads every page of the table, examines every row, and throws away 99.998% of what it reads. I'm afraid there is no polite way to describe this — it is performing rather industrially. With an index, it would read the 200 matching rows directly. The difference is often two to three orders of magnitude in execution time.

Missing indexes

The most common cause of unwanted sequential scans. If you see a Seq Scan on a large table in an EXPLAIN plan with a high Rows Removed by Filter count, the first thing to check is whether an index exists on the filtered column.

-- The key diagnostic: Rows Removed by Filter
EXPLAIN ANALYZE
SELECT * FROM events WHERE user_id = 7842;

-- Seq Scan on events  (cost=0.00..384210.00 rows=312 width=128)
--                     (actual time=12.045..2841.678 rows=312 loops=1)
--   Filter: (user_id = 7842)
--   Rows Removed by Filter: 9,999,688
--   Execution Time: 2843.12 ms

-- 312 rows returned, 9.99 million discarded.
-- This table needs an index on user_id.

312 rows returned. 9.99 million discarded. If I may speak plainly, this is the diagnostic equivalent of an alarm bell. An index on user_id would transform this query from seconds to milliseconds.

High Rows Removed by Filter

This EXPLAIN ANALYZE metric is the single most useful diagnostic for sequential scan problems. It tells you exactly how many rows PostgreSQL read and discarded. A high ratio of removed rows to returned rows means the scan is doing far more work than necessary — and this is the number I watch for. When you see it, the table almost certainly needs an index on the filtered column or columns.

Fixing unwanted sequential scans

Three approaches, in order of likelihood. If you'll permit me, I will attend to each in turn.

Create an index

The most common fix, and frequently the most satisfying. If the query filters on a column that has no index, create one. The planner will use it automatically if it reduces the estimated cost.

-- Before: sequential scan filtering 955,000 rows to find 45,000
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';
-- Seq Scan on orders  (cost=0.00..25432.00 rows=45000 width=96)
--   Filter: (status = 'pending')
--   Rows Removed by Filter: 955000
--   Execution Time: 201.42 ms

-- Create a targeted index
CREATE INDEX idx_orders_status ON orders (status);

-- After: index scan reads only the matching rows
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';
-- Bitmap Index Scan on idx_orders_status  (cost=0.00..512.50 rows=45000 width=96)
--   Index Cond: (status = 'pending')
--   Execution Time: 18.73 ms

After creating the index, run EXPLAIN ANALYZE again to confirm the planner switched to an index or bitmap scan. From 201ms to 18ms — the sort of improvement that makes the effort worthwhile. If the Seq Scan persists, read on.

Update statistics with ANALYZE

If an index exists but the planner still chooses a sequential scan, the planner may be working with stale statistics. When PostgreSQL underestimates how selective a WHERE clause is — thinking it will return 80% of rows when it actually returns 1% — it calculates that a sequential scan is cheaper than an index scan. Running ANALYZE gives the planner current data distributions.

-- Check if table statistics are current
SELECT
  relname,
  last_analyze,
  last_autoanalyze,
  n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- If n_mod_since_analyze is high, the planner may be
-- using stale row estimates. Run ANALYZE to update.
ANALYZE orders;

Check that the planner can use existing indexes

An index exists, statistics are current, and yet the planner still chooses a sequential scan. This requires a closer look. Several things can prevent index use:

• Type mismatch — filtering a varchar column with an integer value, or using the wrong collation, prevents the index from being used
• Function wrapping — WHERE LOWER(email) = 'user@example.com' cannot use a plain B-tree index on email. Create an expression index: CREATE INDEX ON users (LOWER(email))
• Negation — WHERE status != 'archived' is often not selective enough for an index scan, even with an index on status
• OR conditions — WHERE a = 1 OR b = 2 may not use individual indexes. The planner may choose bitmap scans on each index and combine them, or fall back to a sequential scan if neither condition is selective

Sequential scan vs index scan cost model

The planner does not flip a coin. It calculates the estimated cost of each access method and picks the cheapest — and it is, on the whole, quite good at its job. Understanding the cost model explains why the planner sometimes chooses a sequential scan even when an index is available, and why that decision is usually correct.

-- View the planner's cost parameters
SHOW seq_page_cost;          -- default: 1.0
SHOW random_page_cost;       -- default: 4.0

-- Sequential scan cost for a table with 10,000 pages and 1M rows:
--   I/O cost:  10,000 pages * 1.0 (seq_page_cost)  = 10,000
--   CPU cost:  1,000,000 rows * 0.01 (cpu_tuple_cost) = 10,000
--   Total:     20,000

-- Index scan cost for 100 matching rows from a 3-level B-tree:
--   I/O cost:  3 index pages * 4.0 (random_page_cost) = 12
--            + 100 heap pages * 4.0 (random_page_cost) = 400
--   CPU cost:  100 index entries * 0.005 = 0.5
--            + 100 rows * 0.01 = 1.0
--   Total:     413.5

-- The planner picks the cheaper option.
-- As selectivity drops (more rows returned), the index scan
-- cost rises while the sequential scan cost stays the same.

The key insight is the ratio between seq_page_cost (default 1.0) and random_page_cost (default 4.0). A sequential page read costs 1 unit; a random page read costs 4 units. An index scan performs random reads — jumping to different heap pages for each matching row — so its per-page cost is four times higher than a sequential scan's.

This means that as a query becomes less selective (returns a larger fraction of the table), the index scan cost rises because it reads more heap pages at the random I/O rate, while the sequential scan cost stays constant because it always reads the entire table at the sequential rate. At some crossover point, the sequential scan becomes cheaper. For the default cost parameters, this crossover happens at roughly 5-10% of the table, depending on table and index size.

-- On SSDs, random reads are nearly as fast as sequential reads.
-- Lowering random_page_cost makes the planner favor index scans.
SET random_page_cost = 1.1;  -- typical SSD setting

-- Check if an existing index is being ignored
-- by forcing the planner's hand (diagnostic only):
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';
-- If the index scan is faster, the real fix is to adjust
-- random_page_cost or run ANALYZE — not to leave enable_seqscan off.
RESET enable_seqscan;

On SSDs, the gap between random and sequential reads is much smaller than the default 4:1 ratio assumes. Lowering random_page_cost to 1.1 or 1.5 shifts the crossover point, making the planner more willing to use index scans. I should note that this is one of the most impactful single-parameter changes you can make on SSD-backed PostgreSQL. One setting. Measurable improvement across the board.

Monitoring sequential scans

PostgreSQL tracks how many sequential scans and index scans each table receives in pg_stat_user_tables. This is useful for identifying tables that are being scanned sequentially far more often than you might expect — or, in some cases, far more often than is proper.

-- Check sequential scan frequency per table
SELECT
  relname,
  seq_scan,
  seq_tup_read,
  idx_scan,
  idx_tup_fetch,
  n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;

-- Tables with high seq_tup_read and high n_live_tup
-- are candidates for index investigation.

A large table with a high seq_scan count and a low idx_scan count is worth investigating. It may indicate missing indexes, or it may reflect legitimate bulk operations — the numbers alone do not tell the full story, but they tell you where to look. The seq_tup_read column tells you how many rows were read via sequential scans in total, which is a more direct measure of the work involved.

How Gold Lapel relates

This is the pattern Gold Lapel was built to see. It sits between the application and PostgreSQL, observing every query's execution plan. When it detects a sequential scan on a large table where the filter removes most of the rows — that telltale disproportion between Rows Removed by Filter and rows returned — it flags the query and recommends a specific index to resolve it.

Critically, Gold Lapel distinguishes between sequential scans that are appropriate and those that indicate a missing index. It tracks selectivity ratios over time and only recommends indexes when the evidence is consistent: the query runs frequently, the table is large enough that the scan is expensive, and the WHERE clause is selective enough that an index would meaningfully reduce the work. An index on every column is not a strategy. It is an admission that no one has looked at the query plans. Gold Lapel has.