A sequential scan, also called a full table scan or table scan, visits every row and tests it against the query condition. Its linear O(N) work becomes increasingly expensive as a table grows. section 4.1 section 4.4
Use EXPLAIN to recognize when Postgres chooses this access method and why:
- A plan node named Seq Scan means Postgres checks each row, while Filter shows the condition applied to those rows. section 4.4
- Rows Removed by Filter reveals how many scanned rows failed the condition; a score range retained 970 rows and rejected 9,030. section 4.5.1
- Postgres may prefer a sequential scan when a condition matches many rows, because using an index can cost more than scanning the table. section 4.6.1
- Large time-series queries can use parallel sequential scans, dividing table or partition ranges among cooperating processes. section 9.7
Inspecting a score-range scan
Run EXPLAIN (analyze, costs off) SELECT count(*) FROM game.player_stats WHERE score BETWEEN 5000 AND 6000; The plan reports Seq Scan on player_stats, retains 970 rows, removes 9,030, and completes in 1.753 ms. Creating a B-tree index on score changes this workload to an Index Only Scan and reduces the reported execution time to 0.350 ms. section 4.5.1
QUERY PLAN
-----------------------------------------------------------------------
Aggregate (cost=282.18..282.19 rows=1 width=8)
-> Seq Scan on player_stats (cost=0.00..262.00 rows=8070 width=0)
Filter: (win_count > 100)
(3 rows)
Inspect the complete EXPLAIN output for a count query whose Seq Scan filters player_stats by win_count. section 4.4
