Seq Scan in PostgreSQL EXPLAIN

What it does

Seq Scan (sequential scan) reads a table from its first page to its last and checks every row against the query’s conditions, shown in the plan as Filter. Rows that pass go up to the next node; the rest are counted in Rows Removed by Filter. No index is involved.

Each 8 kB page is read once, in the order it sits on disk. That makes a sequential scan the cheapest way to read a large part of a table: the operating system can read ahead, and there’s no index to walk first. It’s also always possible, so the planner builds a sequential scan plan for every table and keeps it unless something cheaper beats it.

When the planner picks it

• No index matches the condition. That includes a column wrapped in a function or cast: lower(email) = '…' can’t use a plain index on email.
• The condition matches a large share of the rows. Fetching 5% of a table through an index means jumping between pages all over it; reading the whole table in order is often cheaper. There’s no fixed cut-off: it depends on the table, how the rows are laid out, and settings like random_page_cost.
• The table is small. A few pages are cheaper to read whole than through an index.
• The query needs every row anyway, such as an export or an aggregate over the whole table.

On tables bigger than min_parallel_table_scan_size (8 MB by default), you’ll often see a Parallel Seq Scan under a Gather instead.

Reading its numbers

Seq Scan on customers  (cost=0.00..993.00 rows=2488 width=29) (actual time=0.016..5.996 rows=2500.00 loops=1)
  Filter: (country = 'NZ'::text)
  Rows Removed by Filter: 47500
  Buffers: shared hit=368

• Filter: the conditions checked against each row.
• Rows Removed by Filter: rows read and rejected. Here 50,000 rows were read to return 2,500. When loops is more than 1, this is an average per loop, like rows. See Rows Removed by Filter.
• rows=2488 against rows=2500.00: the planner’s estimate and what really came out. Close, so the statistics are fine. PostgreSQL 18 prints actual rows with two decimals; 17 and earlier print whole numbers.
• Buffers: shared hit=368: all 368 of the table’s pages were already in PostgreSQL’s shared buffers. read= would count pages fetched from the operating system or disk. See Buffers: shared hit and read. On 18, EXPLAIN ANALYZE includes buffers without being asked; on 14–17 add BUFFERS.
• cost=0.00..993.00: a startup cost of zero (the first row can come out at once) and a total that grows with the number of pages and rows. See cost and rows estimates.

“Disabled” sequential scans

If you switch sequential scans off to test something (SET enable_seqscan = off) and the table has no other way in, PostgreSQL still uses one. Version 18 says so plainly:

Seq Scan on products  (cost=0.00..1976.00 rows=1 width=23)
  Disabled: true
  Filter: (name = 'Product 7'::text)

PostgreSQL 14–17 add 10,000,000,000 to the cost instead. On 17 here, that inflated cost was enough to switch on JIT compilation for a one-row lookup:

Seq Scan on products  (cost=10000000000.00..10000001976.00 rows=1 width=23)
  Filter: (name = 'Product 7'::text)
JIT:
  Functions: 2
  Options: Inlining true, Optimization true, Expressions true, Deforming true

When it’s a problem

Look for three things together: a big table, few rows returned, and Rows Removed by Filter close to the table’s size. Then:

1. Add an index on the filtered column. In the example below, an index turns a 3.4 ms scan of 50,000 rows into a 0.03 ms lookup. On a production table use CREATE INDEX CONCURRENTLY so writes aren’t blocked while it builds: see CREATE INDEX CONCURRENTLY. The trade-off: every index slows inserts and updates a little and takes disk space, so index the queries you actually run, not every column.
2. Index the expression the query uses. For WHERE lower(email) = $1, create CREATE INDEX ON users (lower(email)), or change the query to compare the plain column.
3. Refresh statistics. If the estimated rows are far from the actual rows, run ANALYZE <table>. After a bulk load, autovacuum may not have analysed the table yet, and a bad estimate can make an index look more expensive than it is.
4. Ask for fewer rows. If the query really needs most of the table, the sequential scan is already the fastest way to get it. The fix is a narrower WHERE, pagination, or a summary table.

Don’t leave enable_seqscan = off set to force indexes: it’s a test switch for the whole session, not a hint for one query.

Example

PostgreSQL 18.6 with default settings, in a scratch schema:

CREATE SCHEMA seo_explain;
SET search_path = seo_explain;

CREATE TABLE customers (
  id         int PRIMARY KEY,
  name       text NOT NULL,
  country    text NOT NULL,
  created_at timestamptz NOT NULL
);
INSERT INTO customers
SELECT i,
       'Customer ' || i,
       (ARRAY['GB','US','DE','FR','NL','IE','ES','IT','SE','NO',
              'DK','FI','PL','PT','BE','AT','CH','CA','AU','NZ'])[1 + (i * 7) % 20],
       timestamptz '2023-01-01' + i * interval '20 minutes'
FROM generate_series(1, 50000) AS i;
VACUUM ANALYZE customers;

Customers in New Zealand are 5% of the table. A sequential scan is the right plan, and the estimate is close:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM customers WHERE country = 'NZ';

Seq Scan on customers  (cost=0.00..993.00 rows=2488 width=29) (actual time=0.016..5.996 rows=2500.00 loops=1)
  Filter: (country = 'NZ'::text)
  Rows Removed by Filter: 47500
  Buffers: shared hit=368
Planning Time: 0.073 ms
Execution Time: 6.156 ms

Looking up one customer by name, with no index on name, reads the same 368 pages to keep one row:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM customers WHERE name = 'Customer 4242';

Seq Scan on customers  (cost=0.00..993.00 rows=1 width=29) (actual time=0.457..3.350 rows=1.00 loops=1)
  Filter: (name = 'Customer 4242'::text)
  Rows Removed by Filter: 49999
  Buffers: shared read=368
Planning Time: 0.055 ms
Execution Time: 3.369 ms

With an index, it reads three pages:

CREATE INDEX customers_name_idx ON customers (name);
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM customers WHERE name = 'Customer 4242';

Index Scan using customers_name_idx on customers  (cost=0.29..8.31 rows=1 width=29) (actual time=0.018..0.019 rows=1.00 loops=1)
  Index Cond: (name = 'Customer 4242'::text)
  Index Searches: 1
  Buffers: shared hit=3
Planning Time: 0.053 ms
Execution Time: 0.032 ms

On a table of 50,000 rows the difference is a few milliseconds. On a table of 50 million it’s seconds per query, which is when a Seq Scan in a plan deserves a second look.

In Inlet

Inlet draws EXPLAIN and EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts, so a scan that dominates the query is easy to spot. You can also paste a plan into the free plan visualizer.