A sequential scan is not always a problem — PostgreSQL's planner often chooses one correctly for small tables or queries that return most of a table's rows. The dangerous variant is the sequential scan with no filter predicate: the plan reads every row in the table and returns all of them, with zero selectivity applied at the scan level. This pattern means the query has no WHERE clause narrowing the result, or the join/filter column has no index backing it. On any large table in a transactional workload it produces unnecessary I/O, longer runtimes, and contention under load. This post shows how to spot it and what to do about it.
1. Capture the execution plan
Run EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) against the slow query. The BUFFERS option exposes how many shared-buffer pages were read from disk versus cache, making the real I/O cost visible alongside the plan structure.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM your_table; -- The warning sign in the output: -- Seq Scan on your_table (cost=0.00..XXXXX rows=NNNNN width=NN) -- (actual time=X.XXX..X.XXX rows=NNNNN loops=1) -- Buffers: shared read=XXXXX -- (No "Filter:" line beneath the Seq Scan node = no predicate pushing down)
2. Visualize and confirm the issue in PlanTrace
Copy the full plan text and paste it into PlanTrace, my free browser-based EXPLAIN visualizer for PostgreSQL and Amazon Redshift. It will render the execution plan as an interactive node graph, flags the costliest nodes with a color-coded cost semaphore, shows buffer usage per node, and surfaces tuning insights automatically, and like this posts, will include the absence of a filter predicate on a sequential scan.
In the node graph, a Seq Scan node with no child filter annotation and a high shared read buffer count is the fingerprint of this issue. The cost semaphore on that node will typically appear in red or orange on a large table, making it easy to prioritize against other nodes in a complex plan.
3. Confirm the table is large enough to decide your next action
Not every sequential scan needs fixing. If the table has only a few thousand rows, the planner's choice is almost always correct — a sequential scan on a small table is faster than the overhead of an index lookup. Query pg_class to check the row estimate before proceeding.
SELECT relname AS table_name, reltuples::bigint AS estimated_rows, pg_size_pretty(pg_total_relation_size(oid)) AS total_size FROM pg_class WHERE relname = 'your_table' AND relkind = 'r'; -- If estimated_rows is under ~1,000, the sequential scan is likely correct. Stop here.
4. Identify the missing filter or join predicate
The root cause is almost always one of two things: the query has no WHERE clause at all, or it has one but the filtered column has no supporting index. Check scan counters in pg_stat_user_tables to understand how often the table is being hit with sequential versus index scans, and verify which indexes currently exist.
-- Scan counter ratio SELECT relname AS table_name, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname = 'your_table'; -- Existing indexes SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'your_table';
5. Add a WHERE clause and create the right index
The remediation follows directly from the diagnosis. If the query has no filter, add one — most production queries have a natural narrow condition such as a date range, a status value, or a foreign key. If the filter column exists but has no index, create one. Use CONCURRENTLY on production systems to avoid blocking writes during the build, as described in the PostgreSQL CREATE INDEX documentation. For frequently queried subsets, a partial index covers only the qualifying rows, keeping the structure compact. For queries that project only a few columns, an index with INCLUDE enables an index-only scan and removes the need for a heap access entirely.
-- Option A: Standard B-tree on the filter or join key CREATE INDEX CONCURRENTLY idx_your_table_col ON your_table (filter_column); -- Option B: Partial index — only index the rows you actually query CREATE INDEX CONCURRENTLY idx_your_table_partial ON your_table (filter_column) WHERE status = 'active'; -- Option C: Covering index — avoid heap access for narrow SELECT lists CREATE INDEX CONCURRENTLY idx_your_table_covering ON your_table (filter_column) INCLUDE (col_a, col_b);
6. Keep statistics current so the planner uses the index
An index alone is not enough. PostgreSQL's cost-based planner relies on table statistics to decide whether an index scan is cheaper than a sequential scan. If statistics are stale — because the table grew rapidly or autovacuum thresholds are too loose — the planner's row-count estimates will be wrong and it may still choose a sequential scan even with a valid index in place. Run ANALYZE manually after bulk loads, and tighten per-table autovacuum settings on high-write tables.
-- Refresh statistics after a bulk load or after creating the index ANALYZE your_table; -- Tighten autovacuum thresholds on a high-write table ALTER TABLE your_table SET ( autovacuum_analyze_scale_factor = 0.01, autovacuum_analyze_threshold = 500 );
7. Re-run the plan and verify the fix in PlanTrace
After creating the index and running ANALYZE, re-execute EXPLAIN (ANALYZE, BUFFERS) on the same query and paste the new plan into PlanTrace. The insight should be gone (or show new ones) — confirming that the optimization took effect.
Wrapping Up
A sequential scan with no filter predicate is a full-table read with no early exit: every page is touched, every row is returned, and nothing is filtered at the scan level. The pattern applies to any table in any schema — the fix is always the same combination: add a selective WHERE clause, back it with the right index type (standard, partial, or covering), and run ANALYZE so the planner has accurate statistics to make the right choice.
PlanTrace makes both steps easier — paste the plan before and after the change and the node graph shows immediately whether the sequential scan has been replaced and how much the buffer cost dropped.
