A sequential scan (shown in PostgreSQL plans as `Seq Scan`) reads through a table and filters rows afterward, rather than using an index to locate a small matching set. It can return correct results while doing far more work than the query’s selectivity suggests. As tables grow, this access pattern becomes linearly slower, so a scan that seems harmless in milliseconds can materially reduce throughput when repeated often. section 1.5.2 section 6.13
Treat a `Seq Scan` in `EXPLAIN` or `EXPLAIN ANALYZE` as a diagnosis prompt, not an automatic instruction to add any index. Check the `WHERE` clause, the table access, filters, and rows processed; then compare plans before and after a targeted change. A foreign key on a referenced primary key does not automatically create an index on the referencing column, so joins or filters on that column may still scan the referencing table. section 6.10 section 1.5.2
Counting the few open tickets
A support table contains 500,000 tickets, but only 250 have `status = 10`. `SELECT count(*) FROM support.tickets WHERE status = 10` initially produces a `Seq Scan` with a filter and takes about 110 ms. A regular index on `status` changes the plan to an `Index Only Scan` and reduces the time to about 3.7 ms, but indexes every status value. A partial index restricted to `WHERE status = 10` is much smaller—16 kB rather than 3408 kB—and retains an index-only plan at about 0.8 ms. The useful fix is therefore an index aligned with the narrow, recurring predicate, not merely an index on the column. section 1.5.2
EXPLAIN SELECT count(*)
FROM support.tickets
WHERE status = 10;
QUERY PLAN
----------------------------------------------------------------
Aggregate (cost=9927.28..9927.29 rows=1 width=8)
-> Seq Scan on tickets (cost=0.00..9927.28 rows=1 width=0)
Filter: (status = 10)
(3 rows)
Inspect this plan to see the aggregate consuming rows from a `Seq Scan` and applying `status = 10` as a filter afterward. section 1.5.2
An existing index can be bypassed when a predicate transforms the indexed column. A direct timestamp comparison uses an `Index Scan`, while `date_trunc('s', tstamp) = ...` produces a full table scan because the transformed values no longer match the ordinary timestamp index. Prefer a range predicate that leaves the indexed column untransformed, or create an expression index when the same expression and casts are consistently required. Expression indexes are lossy for queries that do not use their defining functions and casts. section 2.7
When a plan shows a sequential scan on a slow query:
- Run `EXPLAIN` or `EXPLAIN ANALYZE` and identify the scanned relation, filters, and access method before changing schema. section 1.5.2 section 6.10
- Match an index to the actual selective `WHERE` or join predicate; consider a partial index when only a small, stable subset is queried. section 1.5.2 section 6.10
- Avoid indexing every column reflexively: unneeded indexes consume disk and slow `INSERT` and `UPDATE` work. section 1.5.2 section 6.13
- Rewrite predicates that apply functions to indexed columns as ranges where possible, or use a matching expression index for established query patterns. section 2.7
